AnalyticDB:User segmentation functions (Roaring Bitmap)

Scénario 1 : Filtrer et regrouper

Comptez les utilisateurs où tag1 IN ('x', 'y'), regroupés par tag2.

Toutes les requêtes suivent le même schéma : joignez les tables de tags bitmap sur user_group, appliquez les opérations ensemblistes par groupe, puis agrégez les résultats sur tous les groupes.

1. Inspectez les résultats intermédiaires par groupe.

   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. Additionnez les comptes par groupe pour obtenir les totaux finaux.

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

Scénario 2 : Intersection entre deux tables de tags bitmap

Recherchez les utilisateurs où (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b'.

Les deux entrées proviennent de tables de tags bitmap. Effectuez d'abord une agrégation OR au sein de chaque table de tags, puis appliquez l'opération AND sur les résultats des deux tables.

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

Scénario 3 : Intersection entre une table de tags bitmap et la table source

Recherchez les utilisateurs où (tag1 = 'x' OR tag1 = 'y') AND tag2 = 'b', la seconde condition étant filtrée à partir de la table brute users plutôt que d'une table de tags bitmap préconstruite.

Utilisez RB_BUILD_AGG pour construire un bitmap à la volée à partir de la table users, puis appliquez l'opération AND avec le bitmap préconstruit de 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 |
+------------+--------------------+

Scénario 4 : Exporter les résultats bitmap vers OSS

Enregistrez les résultats bitmap du scénario 2 dans une table externe OSS pour une utilisation en aval.

1. Créez la table de sortie.

   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. Écrivez les résultats du scénario 2 dans 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;

   Une fois la requête terminée, le résultat est stocké au format Parquet à l'emplacement oss://testBucketName/roaringbitmap/tag_tbl_3/.

Scénario 5 : Accélérer les requêtes avec une table de cache interne (pour les tables externes)

Importez les données bitmap d'une table externe OSS dans une table interne pour accélérer les requêtes répétées. Étant donné que les tables internes antérieures à la version V3.2.1.0 ne prennent pas en charge nativement le type ROARINGBITMAP, stockez les bitmaps au format VARBINARY et convertissez-les lors de l'exécution de la requête.

1. Créez la table de cache interne avec une colonne VARBINARY pour le bitmap.

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

2. Importez depuis la table OSS en sérialisant chaque bitmap au format VARBINARY.

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

3. Interrogez la table de cache en désérialisant le format VARBINARY vers des bitmaps lors de l'exécution de la requête.

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