BITMAP_AGG

描述

聚合某列的值（不包括任何空值）返回一行 bitmap 值，即多行转一行。

语法

BITMAP_AGG(<expr>)

参数

参数	说明
<expr>	待合并数值的列或表达式，expr 的类型需要为 TINYINT,SMALLINT,INT,LARGEINT 和 BIGINT 类型，也支持可以转化为以上类型的 VARCHAR。

返回值

返回 BITMAP 类型的值。特殊情况：

• 如果某个值小于 0 或者大于 18446744073709551615，该值会被忽略，不会合并到 Bitmap 中

举例

create table test_bitmap_agg(
    id int,
    k0 int,
    k1 varchar(32),
    k2 int,
    k3 int,
    k4 bigint,
    k5 varchar(32)
) properties('replication_num' = '1');
insert into test_bitmap_agg values
  (1, 10, '110', 11, 300, 10000000000, '0'),
  (2, 20, '120', 21, 400, 20000000000, '200000000000000'),
  (3, 30, '130', 31, 350, 30000000000, '300000000000000'),
  (4, 40, '140', 41, 500, 40000000000, '18446744073709551616'),
  (5, 50, '150', 51, 250, 50000000000, '18446744073709551615'),
  (6, 60, '160', 61, 600, 60000000000, '-1'),
  (7, 60, '160', 120, 600, 60000000000, NULL);

select * from test_bitmap_agg;

+------+------+------+------+------+-------------+----------------------+
| id   | k0   | k1   | k2   | k3   | k4          | k5                   |
+------+------+------+------+------+-------------+----------------------+
|    1 |   10 | 110  |   11 |  300 | 10000000000 | 0                    |
|    2 |   20 | 120  |   21 |  400 | 20000000000 | 200000000000000      |
|    3 |   30 | 130  |   31 |  350 | 30000000000 | 300000000000000      |
|    4 |   40 | 140  |   41 |  500 | 40000000000 | 18446744073709551616 |
|    5 |   50 | 150  |   51 |  250 | 50000000000 | 18446744073709551615 |
|    6 |   60 | 160  |   61 |  600 | 60000000000 | -1                   |
|    7 |   60 | 160  |  120 |  600 | 60000000000 | NULL                 |
+------+------+------+------+------+-------------+----------------------+

select bitmap_to_string(bitmap_agg(k0)) from test_bitmap_agg;

+----------------------------------+
| bitmap_to_string(bitmap_agg(k0)) |
+----------------------------------+
| 10,20,30,40,50,60                |
+----------------------------------+

select bitmap_to_string(bitmap_agg(k5)) from test_bitmap_agg;

+--------------------------------------------------------+
| bitmap_to_string(bitmap_agg(cast(k5 as BIGINT)))       |
+--------------------------------------------------------+
| 0,200000000000000,300000000000000,18446744073709551615 |
+--------------------------------------------------------+