github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_bitmap.sql (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  
    10  -- <0, 0, 0>: 0x07; <0, 0, 1>: 0x01; <0, 0, 2>: 0x02;
    11  -- <0, 1, 0>: 0x01; <0, 0, 1>: 0x02;
    12  -- <1, 0, 0>: 0x08; <1, 0, 2>: 0x10;
    13  CREATE TABLE precompute AS
    14  SELECT
    15      d_1,
    16      d_2,
    17      BITMAP_BUCKET_NUMBER(v_1) bucket,
    18      BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(v_1)) bmp
    19  FROM my_table
    20  GROUP BY d_1, d_2, bucket;
    21  
    22  -- <0, 0>: 5
    23  -- <0, 1>: 2
    24  -- <1, 0>: 2
    25  SELECT
    26      d_1,
    27      d_2,
    28      SUM(BITMAP_COUNT(bmp)) sum_cnt
    29  FROM precompute
    30  GROUP BY d_1, d_2;
    31  
    32  -- <0>: 6
    33  -- <1>: 2
    34  SELECT d_1, SUM(cnt) FROM (
    35      SELECT
    36          d_1,
    37          BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
    38      FROM precompute
    39      GROUP BY d_1, bucket
    40  )
    41  GROUP BY d_1;
    42  
    43  drop table my_table;
    44  drop table precompute;
    45  
    46  drop table if exists bitmap01;
    47  create table bitmap01(col1 bigint);
    48  insert into bitmap01 values (1844674407370955161);
    49  insert into bitmap01 values (9223372036854775807);
    50  insert into bitmap01 values (0);
    51  select bitmap_bit_position(col1) from bitmap01;
    52  select bitmap_bucket_number(col1) from bitmap01;
    53  select bitmap_bucket_number(col1) bucket,
    54         bitmap_construct_agg(bitmap_bit_position(col1)) bmp from bitmap01 group by col1;
    55  select bitmap_count(bitmap_construct_agg(bitmap_bit_position(col1))) from bitmap01 group by col1;
    56  drop table bitmap01;
    57  
    58  drop table if exists bitmap02;
    59  create table bitmap02 (val int);
    60  insert into bitmap02 values (1), (32769);
    61  select bitmap_bucket_number(val) as bitmap_id,
    62      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    63      from bitmap02
    64      group by bitmap_id;
    65  insert into bitmap02 values (32769), (32769), (1);
    66  select bitmap_bucket_number(val) as bitmap_id,
    67      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    68      from bitmap02
    69      group by bitmap_id;
    70  insert into bitmap02 values (2), (3), (4);
    71  select bitmap_bucket_number(val) as bitmap_id,
    72      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    73      from bitmap02
    74      group by bitmap_id;
    75  select bitmap_bucket_number(val) as bitmap_id,
    76      bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
    77      from bitmap02
    78      group by bitmap_id;
    79  select sum(distinct_values) from (
    80  select bitmap_bucket_number(val) as bitmap_id,
    81      bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
    82      from bitmap02
    83      group by bitmap_id
    84  );
    85  drop table bitmap02;
    86  
    87  drop table if exists bitmap03;
    88  create table bitmap03 (val tinyint unsigned);
    89  insert into bitmap03 values (1), (254), (127);
    90  select bitmap_bucket_number(val) as bitmap_id,
    91      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    92      from bitmap03
    93      group by bitmap_id;
    94  insert into bitmap03 values (10), (100), (1);
    95  select bitmap_bucket_number(val) as bitmap_id,
    96      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    97      from bitmap03
    98      group by bitmap_id;
    99  insert into bitmap03 values (2), (3), (4);
   100  select bitmap_bucket_number(val) as bitmap_id,
   101      bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
   102      from bitmap03
   103      group by bitmap_id;
   104  select bitmap_bucket_number(val) as bitmap_id,
   105      bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
   106      from bitmap03
   107      group by bitmap_id;
   108  select sum(distinct_values) from (
   109  select bitmap_bucket_number(val) as bitmap_id,
   110      bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
   111      from bitmap03
   112      group by bitmap_id
   113  );
   114  drop table bitmap03;