Bitmap aggregate functions enable you to aggregate numeric data into a bitmap format. OceanBase AI 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 columns into bitmap data. The syntax is as follows:
rb_build_agg(values)
The input parameter values (UINT64) is a numeric column used to build the aggregated bitmap data. The valid value range supported by OceanBase AI Database is [0, UINT64_MAX]. However, for compatibility with PostgreSQL, it also supports input of negative integers within the range [INT32_MIN, 0). When outputting, negative integers are converted to UINT32 values; for example, input -1 is output as 4294967295.
This function currently only supports input of integer types; non-integer types will cause an error. If the input is a string, it can be converted to an integer before being entered.
An example is as follows:
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 an OR operation on multiple rows of bitmap data and aggregates the results into a bitmap. The syntax is as follows:
rb_or_agg(rb_in)
The input parameter rb_in is the bitmap column for which aggregation operations are to be performed.
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 an AND operation on multiple rows of a bitmap column and aggregates the results into a bitmap. The syntax is as follows:
rb_and_agg(rb_in)
The input parameter rb_in is the bitmap column for which aggregation operations are to be performed.
An example is as follows:
SELECT rb_to_string(rb_and_agg(rb)) FROM t2;
+------------------------------+
| rb_to_string(rb_and_agg(rb)) |
+------------------------------+
| 2,3 |
+------------------------------+
1 row in set