ApsaraDB RDS:Use the roaringbitmap extension

Create a table

Create a table to store roaringbitmap data:

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

Build bitmaps from arrays

Use rb_build to create a bitmap from an integer array, then insert it into the table:

INSERT INTO t1 SELECT 1, RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);

As a result, a new row is inserted with id = 1 and a bitmap representing the integers {1, 2, 3, 4, 5, 6, 7, 8, 9, 200}.

Use rb_build_agg to aggregate a set of integers into a bitmap:

INSERT INTO t1 SELECT 2, RB_BUILD_AGG(e) FROM GENERATE_SERIES(1, 100) e;

As a result, a new row is inserted with id = 2 and a bitmap representing all integers from 1 to 100.

Bitwise operations

Run OR, AND, XOR, or ANDNOT operations on two bitmaps:

SELECT RB_OR(a.bitmap, b.bitmap)
FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,
     (SELECT bitmap FROM t1 WHERE id = 2) AS b;

As a result, a new bitmap is returned containing all integers present in either bitmap.

Aggregate operations

Run aggregate operations across all bitmaps in the table to produce a single result bitmap:

SELECT RB_OR_AGG(bitmap) FROM t1;
SELECT RB_AND_AGG(bitmap) FROM t1;
SELECT RB_XOR_AGG(bitmap) FROM t1;
SELECT RB_BUILD_AGG(e) FROM GENERATE_SERIES(1, 100) e;

Get cardinality

Cardinality is the count of bits set to 1 in a bitmap. Use rb_cardinality to calculate it:

SELECT RB_CARDINALITY(bitmap) FROM t1;

As a result, the number of set bits (elements) in each bitmap is returned.

Iterate over set bits

Use rb_iterate to get the list of bit positions set to 1:

SELECT RB_ITERATE(bitmap) FROM t1 WHERE id = 1;

As a result, each integer in the bitmap is returned as a separate row.

Bitmap calculation functions

Bitmap aggregate functions