The bitmap cardinality calculation functions calculate the cardinality of the input bitmap data. OceanBase Database supports the following bitmap cardinality calculation 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. Syntax:
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 by performing an AND operation on two bitmap data sets. Syntax:
rb_and_cardinality(rb1, rb2)
The order of the rb1 and rb2 parameters does not affect the result.
Here is an example:
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 is the same as the rb_and_cardinality() function, except that it treats empty input 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 by performing an OR operation on two bitmap data sets. Syntax:
rb_or_cardinality(rb1, rb2)
The order of the rb1 and rb2 parameters does not affect the result.
Here is an example:
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 is the same as the rb_or_cardinality() function, except that it treats empty input as empty bitmap data.
rb_xor_cardinality
The rb_xor_cardinality() function returns the cardinality of the new bitmap data obtained by performing an XOR operation on two bitmap data sets. Syntax:
rb_xor_cardinality(rb1, rb2)
The order of the rb1 and rb2 parameters 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 by performing an AND NOT operation on two bitmap data sets. Syntax:
rb_andnot_cardinality(rb1, rb2)
The function calculates the difference between the rb1 and rb2 parameters, that is, it returns the cardinality of the bitmap data rb1 - rb2.
Here is an example:
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 is the same as the rb_andnot_cardinality() function, except that it treats empty input as empty bitmap data.
rb_or_cardinality_agg
The rb_or_cardinality_agg() function calculates the cardinality of the union of a bitmap column. Syntax:
rb_or_cardinality_agg(rb)
The function takes a bitmap column as input.
It 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 calculates the cardinality of the intersection of a bitmap column. Syntax:
rb_and_cardinality_agg(rb)
The function takes a bitmap column as input.
It returns the cardinality of the intersection of the 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