AnalyticDB:Fungsi segmentasi pengguna (Roaring Bitmap)

Skenario 1: Filter dan kelompokkan

Hitung jumlah pengguna dengan tag1 IN ('x', 'y'), dikelompokkan berdasarkan tag2.

Semua kueri mengikuti pola yang sama: gabungkan tabel tag bitmap berdasarkan user_group, terapkan operasi himpunan per kelompok, lalu agregasi di seluruh kelompok.

1. Periksa hasil antara per kelompok.

   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. Jumlahkan hitungan per kelompok untuk mendapatkan total akhir.

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

Skenario 2: Irisan antara dua tabel tag bitmap

Temukan pengguna dengan (tag1 = 'x' OR tag1 = 'y') DAN tag2 = 'b'.

Kedua input berasal dari tabel tag bitmap. Pertama, agregasi OR dalam setiap tabel tag, lalu AND-kan hasilnya di antara keduanya.

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

Skenario 3: Irisan antara tabel tag bitmap dan tabel sumber

Temukan pengguna dengan (tag1 = 'x' OR tag1 = 'y') DAN tag2 = 'b', dengan kondisi kedua difilter dari tabel users mentah, bukan dari tabel tag bitmap yang telah dibuat sebelumnya.

Gunakan RB_BUILD_AGG untuk membangun bitmap secara langsung dari tabel users, lalu AND-kan dengan bitmap yang telah dibuat sebelumnya dari 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 |
+------------+--------------------+

Skenario 4: Ekspor hasil bitmap ke OSS

Simpan hasil bitmap dari Skenario 2 ke tabel eksternal OSS untuk penggunaan downstream.

1. Buat tabel output.

   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. Tulis hasil Skenario 2 ke 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;

   Setelah kueri selesai, hasilnya disimpan dalam format Parquet di oss://testBucketName/roaringbitmap/tag_tbl_3/.

Skenario 5: Percepat kueri dengan tabel cache internal (untuk tabel eksternal)

Impor data bitmap dari tabel eksternal OSS ke tabel internal untuk mempercepat kueri berulang. Karena tabel internal dengan versi sebelum V3.2.1.0 tidak mendukung tipe ROARINGBITMAP secara native, simpan bitmap sebagai VARBINARY dan konversi saat kueri.

1. Buat tabel cache internal dengan kolom VARBINARY untuk bitmap.

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

2. Impor dari tabel OSS, serialisasi setiap bitmap ke VARBINARY.

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

3. Kueri tabel cache, deserialisasi VARBINARY kembali ke bitmap saat kueri.

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