Bitmap aggregate functions

Bitmap aggregate functions are used to aggregate numeric values into a bitmap. OceanBase Database supports the following bitmap aggregate functions: rb_build_agg(), rb_or_agg(), and rb_and_agg().

rb_build_agg

The rb_build_agg() function aggregates numeric values into a bitmap. Syntax:

rb_build_agg(values)

The values parameter is a numeric column that contains the values to be aggregated. The valid value range of the values parameter is [0, UINT64_MAX]. However, to ensure compatibility with PostgreSQL, the function also supports the input of negative integers in the range [INT32_MIN, 0). When negative integers are input, they are converted to UINT32 values during output. For example, if -1 is input, the output is 4294967295.

The function only supports integer types as input. If a non-integer type is used, an error will be returned. If the input is a string, it can be converted to an integer before being input.

Here is an example:

CREATE TABLE t1(val bigint);
INSERT INTO t1 VALUES(1),(2),(3);
SELECT rb_to_string(rb_build_agg(val)) FROM t1;
+---------------------------------+
| rb_to_string(rb_build_agg(val)) |
+---------------------------------+
| 1,2,3                           |
+---------------------------------+
1 row in set

rb_or_agg

The rb_or_agg() function performs a bitwise OR operation on multiple rows of a bitmap column and aggregates the result into a bitmap. Syntax:

rb_or_agg(rb_in)

The rb_in parameter is the bitmap column to be aggregated.

Here is an example:

CREATE TABLE t2(rb roaringbitmap);
INSERT INTO t2 VALUES (rb_from_string('1,2,3')),(rb_from_string('2,3,4'));
SELECT rb_to_string(rb_or_agg(rb)) FROM t2;
+-----------------------------+
| rb_to_string(rb_or_agg(rb)) |
+-----------------------------+
| 1,2,3,4                     |
+-----------------------------+
1 row in set

rb_and_agg

The rb_and_agg() function performs a bitwise AND operation on multiple rows of a bitmap column and aggregates the result into a bitmap. Syntax:

rb_and_agg(rb_in)

The rb_in parameter is the bitmap column to be aggregated.

Here is an example:

SELECT rb_to_string(rb_and_agg(rb)) FROM t2;
+------------------------------+
| rb_to_string(rb_and_agg(rb)) |
+------------------------------+
| 2,3                          |
+------------------------------+
1 row in set