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

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

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

すべてのクエリは user_group でビットマップタグテーブルを結合し、グループごとにセット操作を適用してから、グループ全体で集計するという同じパターンに従います。

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

   SELECT
     t2.tag2,
     t2.user_group,
     RB_CARDINALITY(RB_AND(t2.rb, t1.rb1)) 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
     t2.tag2,
     SUM(RB_CARDINALITY(RB_AND(t2.rb, t1.rb1))) 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 t2.tag2;

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

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

(tag1 = 'x' OR tag1 = 'y') かつ 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') かつ 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;

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

シナリオ 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 |
   +------+------------+---------------------------------------------------+