Roaring bitmap output functions specify the output format of Roaring bitmap data. For example, you can specify to output each element in a Roaring bitmap in order as a string, with the elements separated with commas (,). OceanBase Database supports the rb_to_varbinary(), rb_to_string(), and rb_to_array() roaring bitmap output functions.
rb_to_varbinary
rb_to_varbinary() outputs Roaring bitmap data in the varbinary format. The syntax is as follows:
rb_to_varbinary(rb, [format])
The format parameter is optional and can be set only to roaring now. After you specify this parameter, only the BITMAP_32 and BITMAP_64 formats are supported for the output binary data. If you do not specify this parameter, the default output format is varbinary, which comprises seven subtypes, including BITMAP_32 and BITMAP_6.
Here are some examples:
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
rb_to_string() outputs each element in a Roaring bitmap in order as a string, with the elements separated with commas (,). Elements are output in the UINT64 format, and a maximum of 1,000,000 elements can be output. The syntax is as follows:
rb_to_string(rb)
Here are some examples:
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 value of bitmap data into one column with multiple rows. You can use this function after the SELECT or FROM clause:
If you use this function after the
SELECTclause, multiplerb_iterate()functions will return multiple columns. The default column name isrb_iterate. If there are fewer rows than the maximum number of rows, the remaining rows are filled withNULLvalues.If you use this function after the
FROMclause, multiplerb_iterate()functions are equivalent to performingJOINoperations on the returned tables in sequence. The number of rows returned is the Cartesian product. The syntax is as follows:
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 add the AS keyword or not. | No |
The return value is a table with one column and multiple rows, where the data type of the column is uint64.
Here is an example:
SELECT rb_iterate(rb_from_string('1,2,3'));
+------------+
| rb_iterate |
+------------+
| 1 |
| 2 |
| 3 |
+------------+
3 rows in set
The following example shows how to use multiple rb_iterate() functions after the FROM clause:
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
The following example shows how to use multiple rb_iterate() functions after the SELECT clause:
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 is used to output bitmap data in the form of an array. The syntax is as follows:
rb_to_array(rb)
The input parameter is a bitmap dataset.
The return value is a sorted array.
Here is an 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