Bitmap cardinality functions calculate the cardinality of bitmap data. OceanBase Database supports the following bitmap cardinality functions: rb_cardinality(), rb_and_cardinality(), rb_or_cardinality(), rb_xor_cardinality(), rb_andnot_cardinality(), rb_and_null2empty_cardinality(), rb_or_null2empty_cardinality(), rb_andnot_null2empty_cardinality(), rb_or_cardinality_agg(), and rb_and_cardinality_agg().
rb_cardinality
The rb_cardinality() function returns the cardinality of the input bitmap data. The syntax is as follows:
rb_cardinality(rb)
Here is an example:
SELECT rb_cardinality(rb_from_string('1,2,3'));
+-----------------------------------------+
| rb_cardinality(rb_from_string('1,2,3')) |
+-----------------------------------------+
| 3 |
+-----------------------------------------+
1 row in set
rb_and_cardinality and rb_and_null2empty_cardinality
The rb_and_cardinality() function returns the cardinality of the new bitmap data obtained after performing an AND operation on two bitmap datasets. The syntax is as follows:
rb_and_cardinality(rb1, rb2)
The order of the parameters rb1 and rb2 does not affect the result.
Here are some examples:
SELECT rb_and_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4'));
+----------------------------------------------------------------------+
| rb_and_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4')) |
+----------------------------------------------------------------------+
| 2 |
+----------------------------------------------------------------------+
1 row in set
SELECT rb_and_cardinality(rb_from_string('1,2,3'), NULL);
+---------------------------------------------------+
| rb_and_cardinality(rb_from_string('1,2,3'), NULL) |
+---------------------------------------------------+
| NULL |
+---------------------------------------------------+
1 row in set
The rb_and_null2empty_cardinality() function operates logically the same as rb_and_cardinality(), but treats null inputs as empty bitmap data.
SELECT rb_and_null2empty_cardinality(rb_from_string('1,2,3'), NULL);
+--------------------------------------------------------------+
| rb_and_null2empty_cardinality(rb_from_string('1,2,3'), NULL) |
+--------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------+
1 row in set
rb_or_cardinality and rb_or_null2empty_cardinality
The rb_or_cardinality() function returns the cardinality of the new bitmap data obtained after performing an OR operation on two bitmap datasets. The syntax is as follows:
rb_or_cardinality(rb1, rb2)
The order of the parameters rb1 and rb2 does not affect the result.
Here are some examples:
SELECT rb_or_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4'));
+---------------------------------------------------------------------+
| rb_or_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4')) |
+---------------------------------------------------------------------+
| 4 |
+---------------------------------------------------------------------+
1 row in set
SELECT rb_or_null2empty_cardinality(rb_from_string('1,2,3'), NULL);
+-------------------------------------------------------------+
| rb_or_null2empty_cardinality(rb_from_string('1,2,3'), NULL) |
+-------------------------------------------------------------+
| 3 |
+-------------------------------------------------------------+
1 row in set
The rb_or_null2empty_cardinality() function operates logically the same as rb_or_cardinality(), but treats null inputs as empty bitmap data.
rb_xor_cardinality
The rb_xor_cardinality() function returns the cardinality of the new bitmap data obtained after performing an XOR operation on two bitmap datasets. The syntax is as follows:
rb_xor_cardinality(rb1, rb2)
The order of the parameters rb1 and rb2 does not affect the result.
Here is an example:
SELECT rb_xor_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4'));
+----------------------------------------------------------------------+
| rb_xor_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4')) |
+----------------------------------------------------------------------+
| 2 |
+----------------------------------------------------------------------+
1 row in set
rb_andnot_cardinality and rb_andnot_null2empty_cardinality
The rb_andnot_cardinality() function returns the cardinality of the new bitmap data obtained after performing an AND NOT operation on two bitmap datasets. The syntax is as follows:
rb_andnot_cardinality(rb1, rb2)
This function uses the parameter rb1 as the basis for calculation and computes its difference with rb2. It returns the cardinality of the bitmap data value rb1 - rb2.
Here are some examples:
SELECT rb_andnot_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4'));
+-------------------------------------------------------------------------+
| rb_andnot_cardinality(rb_from_string('1,2,3'), rb_from_string('2,3,4')) |
+-------------------------------------------------------------------------+
| 1 |
+-------------------------------------------------------------------------+
1 row in set
SELECT rb_andnot_null2empty_cardinality(rb_from_string('1,2,3'), NULL);
+-----------------------------------------------------------------+
| rb_andnot_null2empty_cardinality(rb_from_string('1,2,3'), NULL) |
+-----------------------------------------------------------------+
| 3 |
+-----------------------------------------------------------------+
1 row in set
The rb_andnot_null2empty_cardinality() function operates logically the same as rb_andnot_cardinality(), but treats null inputs as empty bitmap data.
rb_or_cardinality_agg
The rb_or_cardinality_agg() function is used to calculate the union cardinality of a bitmap column. The syntax is as follows:
rb_or_cardinality_agg(rb)
This function accepts one bitmap column as the input.
The function returns the cardinality of the union of the bitmap columns.
Here is an example:
CREATE TABLE t1(rb roaringbitmap);
INSERT INTO t1 VALUES (rb_from_string('1,2,3')),(rb_from_string('2,3,4'));
SELECT rb_or_cardinality_agg(rb) FROM t1;
+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
| 4 |
+---------------------------+
1 row in set
rb_and_cardinality_agg
The rb_and_cardinality_agg() function is used to calculate the intersection cardinality of a bitmap column. The syntax is as follows:
rb_and_cardinality_agg(rb)
The function takes a bitmap column as its input parameter.
It returns the cardinality of the intersection of these bitmap columns.
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_and_cardinality_agg(rb) FROM t2;
+----------------------------+
| rb_and_cardinality_agg(rb) |
+----------------------------+
| 2 |
+----------------------------+
1 row in set