AnalyticDB:User segmentation functions (Roaring Bitmap)

Scenario 1: Filter and group

Count users where tag1 IN ('x', 'y'), grouped by tag2.

All queries follow the same pattern: join bitmap tag tables on user_group, apply set operations per group, then aggregate across groups.

1. Inspect the per-group intermediate results.

   SELECT
     tag2,
     t1.user_group AS user_group,
     RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS rb
   FROM tag_tbl_2 AS t2
   JOIN (
     SELECT user_group, rb AS rb1
     FROM tag_tbl_1
     WHERE tag1 IN ('x', 'y')
   ) AS t1 ON t1.user_group = t2.user_group;

   +------+------------+---------+
   | tag2 | user_group | rb      |
   +------+------------+---------+
   | b    |          3 | 1041828 |
   | a    |         15 | 1039859 |
   | a    |          9 | 1039140 |
   | b    |          1 | 1041524 |
   | a    |          4 | 1041599 |
   | b    |          1 | 1041381 |
   | b    |         10 | 1041026 |
   | b    |          6 | 1042289 |
   +------+------------+---------+

2. Sum the per-group counts to get the final totals.

   SELECT tag2, SUM(cnt) FROM (
     SELECT
       tag2,
       t1.user_group AS user_group,
       RB_CARDINALITY(RB_AND(t2.rb, rb1)) AS cnt
     FROM tag_tbl_2 AS t2
     JOIN (
       SELECT user_group, rb AS rb1
       FROM tag_tbl_1
       WHERE tag1 IN ('x', 'y')
     ) AS t1 ON t1.user_group = t2.user_group
   ) GROUP BY tag2;

   +------+----------+
   | tag2 | sum(cnt) |
   +------+----------+
   | a    | 33327868 |
   | b    | 33335220 |
   +------+----------+

Scenario 2: Intersection between two bitmap tag tables

Find users where (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b'.

Both inputs come from bitmap tag tables. First OR-aggregate within each tag table, then AND the results across the two.

SELECT user_group, RB_CARDINALITY(rb) FROM (
  SELECT
    t1.user_group AS user_group,
    RB_AND(rb1, rb2) AS rb
  FROM (
    SELECT user_group, RB_OR_AGG(rb) AS rb1
    FROM tag_tbl_1
    WHERE tag1 = 'x' OR tag1 = 'y'
    GROUP BY user_group
  ) AS t1
  JOIN (
    SELECT user_group, RB_OR_AGG(rb) AS rb2
    FROM tag_tbl_2
    WHERE tag2 = 'b'
    GROUP BY user_group
  ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group
);

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|         10 |            2083679 |
|          3 |            2082370 |
|          9 |            2082847 |
|          2 |            2086511 |
...
|          1 |            2082291 |
|          4 |            2083290 |
|         14 |            2083581 |
|         15 |            2084110 |
+------------+--------------------+

Scenario 3: Intersection between a bitmap tag table and the source table

Find users where (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b', where the second condition is filtered from the raw users table rather than a pre-built bitmap tag table.

Use RB_BUILD_AGG to build a bitmap on the fly from the users table, then AND it with the pre-built bitmap from tag_tbl_1.

SELECT user_group, RB_CARDINALITY(rb) FROM (
  SELECT
    t1.user_group AS user_group,
    RB_AND(rb1, rb2) AS rb
  FROM (
    SELECT user_group, RB_OR_AGG(rb) AS rb1
    FROM tag_tbl_1
    WHERE tag1 = 'x' OR tag1 = 'y'
    GROUP BY user_group
  ) AS t1
  JOIN (
    SELECT user_group, RB_BUILD_AGG(offset) AS rb2
    FROM users
    WHERE tag2 = 'b'
    GROUP BY user_group
  ) AS t2 ON t1.user_group = t2.user_group
  GROUP BY user_group
);

+------------+--------------------+
| user_group | rb_cardinality(rb) |
+------------+--------------------+
|          3 |            2082370 |
|          1 |            2082291 |
|          0 |            2082383 |
|          4 |            2083290 |
|         11 |            2081662 |
|         13 |            2085280 |
...
|         14 |            2083581 |
|         15 |            2084110 |
|          9 |            2082847 |
|          8 |            2084860 |
|          5 |            2083056 |
|          7 |            2083275 |
+------------+--------------------+

Scenario 4: Export bitmap results to OSS

Save the bitmap results from Scenario 2 to an OSS external table for downstream use.

1. Create the output table.

   CREATE TABLE `tag_tbl_3` (
     `user_group` INT,
     `rb`         ROARINGBITMAP
   ) engine = 'oss'
   TABLE_PROPERTIES = '{
     "endpoint":  "oss-cn-zhangjiakou.aliyuncs.com",
     "accessid":  "************",
     "accesskey": "************",
     "url":       "oss://testBucketName/roaringbitmap/tag_tbl_3/",
     "format":    "parquet"
   }';

2. Write the Scenario 2 results to tag_tbl_3.

   INSERT OVERWRITE tag_tbl_3
   SELECT
     t1.user_group AS user_group,
     RB_AND(rb1, rb2) AS rb
   FROM (
     SELECT user_group, RB_OR_AGG(rb) AS rb1
     FROM tag_tbl_1
     WHERE tag1 = 'x' OR tag1 = 'y'
     GROUP BY user_group
   ) AS t1
   JOIN (
     SELECT user_group, RB_OR_AGG(rb) AS rb2
     FROM tag_tbl_2
     WHERE tag2 = 'b'
     GROUP BY user_group
   ) AS t2 ON t1.user_group = t2.user_group;

   After the query completes, the result is stored in Parquet format at oss://testBucketName/roaringbitmap/tag_tbl_3/.

Scenario 5: Accelerate queries with an internal cache table (for external tables)

Import bitmap data from an OSS external table into an internal table to speed up repeated queries. Because internal tables earlier than V3.2.1.0 do not support the ROARINGBITMAP type natively, store the bitmaps as VARBINARY and convert at query time.

1. Create the internal cache table with a VARBINARY column for the bitmap.

   CREATE TABLE `tag_tbl_1_cstore` (
     `tag1`       VARCHAR,
     `rb`         VARBINARY,
     `user_group` INT
   );

2. Import from the OSS table, serializing each bitmap to VARBINARY.

   INSERT INTO tag_tbl_1_cstore
   SELECT tag1, RB_TO_VARBINARY(rb), user_group
   FROM tag_tbl_1;

3. Query the cache table, deserializing VARBINARY back to bitmaps at query time.

   SELECT
     tag1,
     user_group,
     RB_CARDINALITY(RB_OR_AGG(RB_BUILD_VARBINARY(rb)))
   FROM tag_tbl_1_cstore
   GROUP BY tag1, user_group;

   +------+------------+---------------------------------------------------+
   | tag1 | user_group | rb_cardinality(rb_or_agg(rb_build_varbinary(rb))) |
   +------+------------+---------------------------------------------------+
   | y    |          3 |                                           2082919 |
   | x    |          9 |                                           2083085 |
   | x    |          3 |                                           2082140 |
   | y    |         11 |                                           2082268 |
   | z    |          4 |                                           2082451 |
   ...
   | z    |          2 |                                           2081560 |
   | y    |          6 |                                           2082194 |
   | z    |          7 |                                           2082608 |
   +------+------------+---------------------------------------------------+