Bitmap output functions are used to specify the output format of bitmap data, such as outputting each element of the bitmap data as a string and separating them with commas. OceanBase Database currently supports the following bitmap output functions: rb_to_varbinary(), rb_to_string(), and rb_to_array().
rb_to_varbinary
The rb_to_varbinary() function outputs bitmap data in the varbinary format. Syntax:
rb_to_varbinary(rb, [format])
The format parameter is optional. Currently, only roaring is supported. After configuring the format parameter, the output binary data type will only be compatible with BITMAP_32 and BITMAP_64. If you do not configure the format parameter, the default output format is the varbinary format of OceanBase Database, which includes seven subtypes: BITMAP_32, BITMAP_64, and five others.
Example:
SELECT rb_to_varbinary((1,2,3));
+------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3')) |
+------------------------------------------------------------------------------------+
| 0x010303010000000200000003000000 |
+------------------------------------------------------------------------------------+
1 row in set
SELECT rb_to_varbinary((1,2,3),'roaring');
+----------------------------------------------------------------------------------------------------------+
| rb_to_varbinary(rb_from_string('1,2,3'), 'roaring') |
+----------------------------------------------------------------------------------------------------------+
| 0x01053A300000010000000000020010000000010002000300 |
+----------------------------------------------------------------------------------------------------------+
1 row in set
rb_to_string
The rb_to_string() function outputs each element of the bitmap data as a string and separates them with commas. The output format of each element is UINT64, and the maximum number of elements that can be output is 1,000,000. Syntax:
rb_to_string(rb)
Example:
SELECT rb_to_string(rb_from_string('1,2,3'));
+---------------------------------------+
| rb_to_string(rb_from_string('1,2,3')) |
+---------------------------------------+
| 1,2,3 |
+---------------------------------------+
1 row in set
SELECT rb_to_string(x'010101000000');
+-------------------------------+
| rb_to_string(x'010101000000') |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
rb_iterate
The rb_iterate() function expands the values of the bitmap data into multiple rows in a single column. This function can be used after SELECT or FROM:
When used after
SELECT, if multiplerb_iterate()functions are used, multiple columns will be returned, with the default column name beingrb_iterate. The number of rows returned is the maximum number of rows, and any additional rows will be filled withNULLvalues.When used after
FROM, if multiplerb_iterate()functions are used, it is equivalent to performing aJOINoperation on the returned tables in sequence, resulting in a Cartesian product. Syntax:
table = rb_iterate(rb) [AS] [alias]
The parameters are described as follows:
| Parameter | Data type | Default value | Description | Required |
|---|---|---|---|---|
| rb | RoaringBitmap | The bitmap data to be output. | Yes | |
| alia | String | rb_iterate | The alias. You can use the AS keyword or not. | No |
The return value is a table with multiple rows in a single column of the uint64 type.
Example:
SELECT rb_iterate(rb_from_string('1,2,3'));
+------------+
| rb_iterate |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
3 rows in set
Example of using multiple rb_iterate() functions after FROM:
SELECT * FROM rb_iterate(rb_from_string('1,2,3')) AS t1, rb_iterate(rb_from_string('11,22')) AS t2;
+------+------+
| t1 | t2 |
+------+------+
| 1 | 11 |
| 1 | 22 |
| 2 | 11 |
| 2 | 22 |
| 3 | 11 |
| 3 | 22 |
+------+------+
6 rows in set
Example of using multiple rb_iterate() functions after SELECT:
SELECT rb_iterate(rb_from_string('1,2,3')), rb_iterate(rb_from_string('11,22'));
+------------+------------+
| rb_iterate | rb_iterate |
+------------+------------+
| 1 | 11 |
| 2 | 22 |
| 3 | NULL |
+------------+------------+
3 rows in set
CREATE TABLE t1 (id int, rb roaringbitmap, rb2 roaringbitmap);
INSERT INTO t1 VALUES (1, rb_from_string('1,2,3'), rb_from_string('33,44')),(2, rb_from_string('3,4'), rb_from_string('44,55,66'));
SELECT rb_iterate(rb) FROM t1;
+------------+
| rb_iterate |
+------------+
| 1 |
| 2 |
| 3 |
| 3 |
| 4 |
+------------+
5 rows in set
rb_to_array
The rb_to_array() function outputs the bitmap data in an array format. Syntax:
rb_to_array(rb)
The input parameter of this function is a bitmap data.
The return value is an ordered array.
Example:
SELECT rb_to_array(rb_from_string('0,1,2,3,4,5,6,7,8,9,123789,68719476736'));
+-----------------------------------------------------------------------+
| rb_to_array(rb_from_string('0,1,2,3,4,5,6,7,8,9,123789,68719476736')) |
+-----------------------------------------------------------------------+
| [0,1,2,3,4,5,6,7,8,9,123789,68719476736] |
+-----------------------------------------------------------------------+
1 row in set