AnalyticDB:ユーザーセグメンテーション関数 (Roaring Bitmap)

シナリオ 1：フィルタリングとグループ化

tag1 IN ('x', 'y') に該当するユーザーを tag2 でグループ化してカウントします。

すべてのクエリは同じパターンに従います。user_group でビットマップタグテーブルを結合し、グループごとに集合演算を適用してから、グループをまたいで集約します。

1. グループごとの中間結果を確認します。

   SELECT
     t2.tag2,
     t1.user_group,
     RB_CARDINALITY(RB_AND(t1.rb1, t2.rb)) AS rb
   FROM tag_tbl_2 AS t2
   JOIN (
     SELECT user_group, RB_OR_AGG(rb) AS rb1
     FROM tag_tbl_1
     WHERE tag1 IN ('x', 'y')
     GROUP BY user_group
   ) 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. グループごとのカウントを合計して最終的な結果を取得します。

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

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

シナリオ 2：2 つのビットマップタグテーブル間の積集合

(tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b' に該当するユーザーを検索します。

両方の入力はビットマップタグテーブルから取得されます。まず各タグテーブル内で OR 集約を行い、次に 2 つの結果に対して AND 演算を実行します。

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 |
+------------+--------------------+

シナリオ 3：ビットマップタグテーブルとソーステーブル間の積集合

(tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b' に該当するユーザーを検索します。ただし、2 番目の条件は事前に構築されたビットマップタグテーブルではなく、元の users テーブルからフィルタリングされます。

RB_BUILD_AGG を使用して users テーブルから即座にビットマップを構築し、tag_tbl_1 の事前構築されたビットマップと AND 演算を実行します。

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 |
+------------+--------------------+

シナリオ 4：ビットマップ結果の OSS へのエクスポート

シナリオ 2 のビットマップ結果を OSS 外部テーブルに保存して、下流で使用できるようにします。

1. 出力テーブルを作成します。

   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. シナリオ 2 の結果を 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;

   クエリが完了すると、結果は oss://testBucketName/roaringbitmap/tag_tbl_3/ に Parquet 形式で保存されます。

シナリオ 5：内部キャッシュテーブルによるクエリの高速化 (外部テーブルの場合)

OSS 外部テーブルからビットマップデータを内部テーブルにインポートして、繰り返しクエリを高速化します。V3.2.1.0 より前のバージョンでは、内部テーブルは ROARINGBITMAP 型をネイティブにサポートしていないため、ビットマップを VARBINARY として保存し、クエリ時に変換します。

1. ビットマップを格納するための VARBINARY 型の列を持つ内部キャッシュテーブルを作成します。

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

2. OSS テーブルからインポートし、各ビットマップを VARBINARY にシリアル化します。

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

3. キャッシュテーブルに対してクエリを実行し、クエリ時に VARBINARY をビットマップに逆シリアル化します。

   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 |
   +------+------------+---------------------------------------------------+