github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_bitmap.result (about) 1 CREATE TABLE my_table ( 2 d_1 int, 3 d_2 int, 4 v_1 int 5 ); 6 insert into my_table values (0, 0, 0), (0, 0, 1), (0, 0, 2), (0, 0, 32768), (0, 0, 65537); 7 insert into my_table values (0, 1, 0), (0, 1, 32769); 8 insert into my_table values (1, 0, 3), (1, 0, 65540); 9 CREATE TABLE precompute AS 10 SELECT 11 d_1, 12 d_2, 13 BITMAP_BUCKET_NUMBER(v_1) bucket, 14 BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(v_1)) bmp 15 FROM my_table 16 GROUP BY d_1, d_2, bucket; 17 SELECT 18 d_1, 19 d_2, 20 SUM(BITMAP_COUNT(bmp)) sum_cnt 21 FROM precompute 22 GROUP BY d_1, d_2; 23 d_1 d_2 sum_cnt 24 0 0 5 25 0 1 2 26 1 0 2 27 SELECT d_1, SUM(cnt) FROM ( 28 SELECT 29 d_1, 30 BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt 31 FROM precompute 32 GROUP BY d_1, bucket 33 ) 34 GROUP BY d_1; 35 d_1 sum(cnt) 36 0 6 37 1 2 38 drop table my_table; 39 drop table precompute; 40 drop table if exists bitmap01; 41 create table bitmap01(col1 bigint); 42 insert into bitmap01 values (1844674407370955161); 43 insert into bitmap01 values (9223372036854775807); 44 insert into bitmap01 values (0); 45 select bitmap_bit_position(col1) from bitmap01; 46 bitmap_bit_position(col1) 47 6553 48 32767 49 0 50 select bitmap_bucket_number(col1) from bitmap01; 51 bitmap_bucket_number(col1) 52 56294995342131 53 281474976710655 54 0 55 select bitmap_bucket_number(col1) bucket, 56 bitmap_construct_agg(bitmap_bit_position(col1)) bmp from bitmap01 group by col1; 57 bucket bmp 58 56294995342131 :0 59 281474976710655 :0 ÿ 60 0 :0 61 select bitmap_count(bitmap_construct_agg(bitmap_bit_position(col1))) from bitmap01 group by col1; 62 bitmap_count(bitmap_construct_agg(bitmap_bit_position(col1))) 63 1 64 1 65 1 66 drop table bitmap01; 67 drop table if exists bitmap02; 68 create table bitmap02 (val int); 69 insert into bitmap02 values (1), (32769); 70 select bitmap_bucket_number(val) as bitmap_id, 71 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 72 from bitmap02 73 group by bitmap_id; 74 bitmap_id bitmap 75 0 :0 76 1 :0 77 insert into bitmap02 values (32769), (32769), (1); 78 select bitmap_bucket_number(val) as bitmap_id, 79 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 80 from bitmap02 81 group by bitmap_id; 82 bitmap_id bitmap 83 0 :0 84 1 :0 85 insert into bitmap02 values (2), (3), (4); 86 select bitmap_bucket_number(val) as bitmap_id, 87 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 88 from bitmap02 89 group by bitmap_id; 90 bitmap_id bitmap 91 0 :0 92 1 :0 93 select bitmap_bucket_number(val) as bitmap_id, 94 bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values 95 from bitmap02 96 group by bitmap_id; 97 bitmap_id distinct_values 98 0 4 99 1 1 100 select sum(distinct_values) from ( 101 select bitmap_bucket_number(val) as bitmap_id, 102 bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values 103 from bitmap02 104 group by bitmap_id 105 ); 106 sum(distinct_values) 107 5 108 drop table bitmap02; 109 drop table if exists bitmap03; 110 create table bitmap03 (val tinyint unsigned); 111 insert into bitmap03 values (1), (254), (127); 112 select bitmap_bucket_number(val) as bitmap_id, 113 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 114 from bitmap03 115 group by bitmap_id; 116 bitmap_id bitmap 117 0 :0 þ 118 insert into bitmap03 values (10), (100), (1); 119 select bitmap_bucket_number(val) as bitmap_id, 120 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 121 from bitmap03 122 group by bitmap_id; 123 bitmap_id bitmap 124 0 :0 \n d þ 125 insert into bitmap03 values (2), (3), (4); 126 select bitmap_bucket_number(val) as bitmap_id, 127 bitmap_construct_agg(bitmap_bit_position(val)) as bitmap 128 from bitmap03 129 group by bitmap_id; 130 bitmap_id bitmap 131 0 :0 \n d þ 132 select bitmap_bucket_number(val) as bitmap_id, 133 bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values 134 from bitmap03 135 group by bitmap_id; 136 bitmap_id distinct_values 137 0 8 138 select sum(distinct_values) from ( 139 select bitmap_bucket_number(val) as bitmap_id, 140 bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values 141 from bitmap03 142 group by bitmap_id 143 ); 144 sum(distinct_values) 145 8 146 drop table bitmap03;