A Roaring bitmap constructor function constructs a Roaring bitmap object. OceanBase Database supports the following Roaring bitmap constructor functions: rb_build_empty(), rb_build_varbinary(), and rb_from_string().
Note
To return query results in the hexadecimal format, see Create Roaring bitmap columns.
rb_build_empty
The rb_build_empty() function constructs an empty Roaring bitmap object. The syntax is as follows:
rb_build_empty()
This function requires no input arguments.
Here are several examples:
SELECT rb_build_empty();
+------------------------------------+
| rb_build_empty() |
+------------------------------------+
| 0x0100 |
+------------------------------------+
1 row in set
SELECT rb_is_empty(rb_build_empty());
+-------------------------------+
| rb_is_empty(rb_build_empty()) |
+-------------------------------+
| 1 |
+-------------------------------+
1 row in set
rb_build_varbinary
The rb_build_varbinary() function constructs a Roaring bitmap object of the VARBINARY data type. VARBINARY is a proprietary format of OceanBase Database. A VARBINARY string consists of the version, type, and data. The syntax is as follows:
rb_build_varbinary(bin_str)
The input argument is a VARBINARY string.
Here are several examples:
SELECT rb_to_string(rb_build_varbinary(x'0100'));
+-------------------------------------------+
| rb_to_string(rb_build_varbinary(x'0100')) |
+-------------------------------------------+
| |
+-------------------------------------------+
1 row in set
SELECT rb_to_string(rb_build_varbinary(x'0103020100000002000000'));
+-------------------------------------------------------------+
| rb_to_string(rb_build_varbinary(x'0103020100000002000000')) |
+-------------------------------------------------------------+
| 1,2 |
+-------------------------------------------------------------+
1 row in set
rb_from_string
The rb_from_string() function constructs a Roaring bitmap object based on a string of a specific format. The string consists of elements required to construct the Roaring bitmap object. The elements are separated with commas (,), for example, 1,2,3,4. The syntax is as follows:
rb_from_string(str)
The input string can be of the INT32 or INT64 format. OceanBase Database supports the value range of [0, UINT64_MAX]. To ensure compatibility with PostgreSQL, OceanBase Database also supports negative integers within the range of [INT32_MIN,0). If a negative integer is input, the function converts the negative integer into a UINT32 value. For example, if -1 is input, the function returns 4294967295.
Here are several examples:
SELECT rb_from_string('1,2,3');
+--------------------------------------------------+
| rb_from_string('1,2,3') |
+--------------------------------------------------+
| 0x010303010000000200000003000000 |
+--------------------------------------------------+
1 row in set
SELECT rb_to_string(rb_from_string('-1'));
+------------------------------------+
| rb_to_string(rb_from_string('-1')) |
+------------------------------------+
| 4294967295 |
+------------------------------------+
1 row in set
rb_select
Bitmap data stores a set of non-negative integers. The rb_select() function selects a local range of values from bitmap data based on specified conditions, saves the selected values as new bitmap data, and returns the new bitmap data. The syntax is as follows:
res_rb = rb_select(rb, limit [, offset] [, reverse] [, range_start] [, range_end])
The parameters are described as follows:
| Parameter | Data type | Default value | Description | Required |
|---|---|---|---|---|
| rb | RoaringBitmap | The bitmap data to be input. | Yes | |
| limit | uint64 | The maximum number of elements to be output. | Yes | |
| offset | uint64 | 0 | The starting position offset. | No |
| reverse | bool | false | Whether to output the values in reverse order. | No |
| range_start | uint64 | 0 | The starting value of the output range. | No |
| range_end | uint64 | UINT64_MAX | The ending value of the output range. | No |
The calculation process is as follows:
If a range is specified, all elements within the range
[range_start,range_end)are filtered out.If
reverseisfalse, the values are selected in ascending order; iftrue, the values are selected in descending order. Therb_select()function calculates the starting position of the output based on the value ofreversefrom the filtered elements.The
offsetvalue is calculated.The
limitvalue is calculated. At mostlimitelements in the range are output. If the number of elements in the range is less thanlimit, all elements in the range are output.
The return value is of the bitmap data type.
Here are some examples:
Return the first 3 values of '10, 20, 30, 40, 50, 60, 70':
SELECT rb_to_string(rb_select(rb_from_string('10, 20, 30, 40, 50, 60, 70'), 3));
+--------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('10, 20, 30, 40, 50, 60, 70'), 3)) |
+--------------------------------------------------------------------------+
| 10,20,30 |
+--------------------------------------------------------------------------+
1 row in set
Return the first 2 values of '1,2,3,4,5,6,7' after an offset of 2 values:
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 2));
+----------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 2)) |
+----------------------------------------------------------------+
| 3,4 |
+----------------------------------------------------------------+
1 row in set
Return the first 2 values in ascending order from the range [2, 6):
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 0, false, 2, 6));
+-----------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 0, false, 2, 6)) |
+-----------------------------------------------------------------------------+
| 2,3 |
+-----------------------------------------------------------------------------+
1 row in set
Return the first 2 values in ascending order from the range [2, 6) after an offset of 1 value:
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 1, true, 2, 6));
+----------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 2, 1, true, 2, 6)) |
+----------------------------------------------------------------------------+
| 3,4 |
+----------------------------------------------------------------------------+
1 row in set
Return the first 10 values in ascending order from the range [2, 6) after an offset of 1 value (only 3 values are actually returned):
SELECT rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 10, 1, false, 2, 6));
+------------------------------------------------------------------------------+
| rb_to_string(rb_select(rb_from_string('1,2,3,4,5,6,7'), 10, 1, false, 2, 6)) |
+------------------------------------------------------------------------------+
| 3,4,5 |
+------------------------------------------------------------------------------+
1 row in set