Roaring Bitmap スカラー関数と集計関数 - AnalyticDB

Roaring Bitmap は、さまざまなプログラミング言語やビッグデータプラットフォームで広く使用されている効率的なビットマップ圧縮アルゴリズムです。一般的な用途には、重複排除、タグフィルタリング、時系列計算などがあります。このトピックでは、Roaring Bitmap 関数の使用方法について説明します。

バージョン要件

バージョン 3.1.6.4 以降では、OSS 外部テーブルで ROARING BITMAP 型と関連関数がサポートされます。
バージョン 3.2.1.0 以降では、内部テーブルで ROARING BITMAP 型と関連関数がサポートされます。

説明

マイナーバージョンを表示および更新するには、AnalyticDB for MySQL コンソールの クラスター情報 ページの 構成情報 セクションに移動します。

制限事項

AnalyticDB for MySQL の Roaring Bitmap 関数には、以下の制限事項が適用されます：

ROARING BITMAP 型の列を直接選択することはできません。ROARING BITMAP フィールド内の要素を表示するには、Unnest 操作を使用します。例：
```
SELECT * FROM unnest(RB_BUILD(ARRAY[1,2,3]));
```
バージョン 3.2.1.0 より前では、ROARING BITMAP 型は OSS 外部テーブルでのみネイティブにサポートされます。ROARING BITMAP 列を含むテーブルを直接作成することはできません。AnalyticDB for MySQL の内部テーブルで Roaring Bitmap 関数を使用するには、rb_build_varbinary 関数を使用して、データを VARBINARY 型から ROARING BITMAP 型に変換する必要があります。例：
```
// 内部テーブルを定義します。
CREATE TABLE test_rb_cstore (id INT, rb VARBINARY);

// Roaring Bitmap 関数を使用して計算を実行します。
SELECT RB_CARDINALITY(RB_BUILD_VARBINARY(rb)) FROM test_rb_cstore;
```

関数リスト

Roaring Bitmap 関数には、スカラー関数と集計関数があります。

スカラー関数

関数名	入力データの型	出力データの型	説明	例
RB_BUILD	INT	ROARING BITMAP	整数の配列から Roaring Bitmap を生成します。	`RB_BUILD(ARRAY[1,2,3])`
RB_BUILD_RANGE	INT,INT	ROARING BITMAP	整数の範囲から Roaring Bitmap を生成します。開始値は含まれ、終了値は含まれません。	`RB_BUILD_RANGE(0, 10000)`
RB_BUILD_VARBINARY	VARBINARY	ROARING BITMAP	バイナリ型から Roaring Bitmap を生成します。	`RB_BUILD_VARBINARY(RB_TO_VARBINARY (RB_BUILD(ARRAY[1,2,3])))`
RB_CARDINALITY	ROARING BITMAP	BIGINT	Roaring Bitmap のカーディナリティを計算します。	`RB_CARDINALITY(RB_BUILD(ARRAY[1,2,3]))`
RB_CONTAINS	ROARING BITMAP, INT	BOOLEAN	Roaring Bitmap に整数が含まれているかどうかを確認します。	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]), 3)`
RB_AND	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	2 つの Roaring Bitmap の積集合を計算します。	`RB_AND(RB_BUILD(ARRAY[1,2,3]), RB_BUILD(ARRAY[2,3,4]))`
RB_OR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	2 つの Roaring Bitmap の和集合を計算します。	`RB_OR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_XOR	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	2 つの Roaring Bitmap の排他的論理和 (XOR) を計算します。	`RB_XOR(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[2,3,4]))`
RB_AND_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	AND 演算を実行します。入力パラメーターの 1 つが null の場合、もう一方のパラメーターが返されます。入力パラメーターの 1 つが `{}` の場合、出力は `{}` です。	`RB_AND_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	OR 演算を実行します。入力が null の場合、Roaring Bitmap は空 ({}) として扱われます。	`RB_OR_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY	ROARING BITMAP, ROARING BITMAP	ROARING BITMAP	AND NOT 演算を実行します。入力が null の場合、Roaring Bitmap は空 ({}) として扱われます。	`RB_ANDNOT_NULL2EMPTY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	AND 演算を実行し、カーディナリティを返します。	`RB_AND_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_AND_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	AND 演算を実行し、カーディナリティを返します。入力が null の場合、Roaring Bitmap は空 ({}) として扱われます。	`RB_AND_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	OR 演算を実行し、カーディナリティを返します。	`RB_OR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_OR_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	OR 演算を実行し、カーディナリティを返します。入力が null の場合、Roaring Bitmap は空 ({}) として扱われます。	`RB_OR_NULL2EMPTY_CARDINALITY(RB_BUILD(null),RB_BUILD(ARRAY[3,4,5]))`
RB_XOR_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	XOR 演算を実行し、カーディナリティを返します。	`RB_XOR_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	AND NOT 演算を実行し、カーディナリティを返します。	`RB_ANDNOT_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_ANDNOT_NULL2EMPTY_CARDINALITY	ROARING BITMAP, ROARING BITMAP	INTEGER	AND NOT 演算を実行し、カーディナリティを返します。入力が null の場合、Roaring Bitmap は空 ({}) として扱われます。	`RB_ANDNOT_NULL2EMPTY_CARDINALITY(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3,4,5]))`
RB_IS_EMPTY	ROARING BITMAP	BOOLEAN	Roaring Bitmap が空かどうかを確認します。	`RB_IS_EMPTY(RB_BUILD(ARRAY[]))`
RB_CLEAR	ROARING BITMAP,BIGINT,BIGINT	ROARING BITMAP	指定された範囲をクリアします。range_end の値は含まれません。	`RB_CLEAR(RB_BUILD('{1,2,3}'), 2, 3)`
RB_CONTAINS	ROARING BITMAP, ROARING BITMAP	BOOLEAN	最初の Roaring Bitmap が 2 番目の Roaring Bitmap を含むかどうかを確認します。	`RB_CONTAINS(RB_BUILD(ARRAY[1,2,3]),RB_BUILD(ARRAY[3]))`
RB_FLIP	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	Roaring Bitmap 内の特定のオフセットセグメントを反転させます。	`RB_FLIP(RB_BUILD(ARRAY[1,2,3,4,5]), 2, 5)`
RB_MINIMUM	ROARING BITMAP	INTEGER	Roaring Bitmap 内の最小オフセットを返します。Roaring Bitmap が空の場合、エラーが返されます。	`RB_MINIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_MAXIMUM	ROARING BITMAP	INTEGER	Roaring Bitmap 内の最大オフセットを返します。Roaring Bitmap が空の場合、エラーが返されます。	`RB_MAXIMUM(RB_BUILD(ARRAY[1,2,3]))`
RB_RANK	ROARING BITMAP,INTEGER	INTEGER	Roaring Bitmap 内で指定されたオフセット以下の要素のカーディナリティを返します。	`RB_RANK(RB_BUILD(ARRAY[1,2,3]),2)`
RB_TO_ARRAY	ROARING BITMAP	INTEGER	Roaring Bitmap に対応する整数配列を返します。	`RB_TO_ARRAY(RB_BUILD(ARRAY[1,2,3]))`
RB_TO_VARBINAR	ROARING BITMAP	VARBINARY	Roaring Bitmap に対応する VARBINARY 型を返します。	`RB_TO_VARBINARY(RB_BUILD(ARRAY[1,2,3]))`
RB_RANGE_CARDINALITY	ROARING BITMAP, INTEGER, INTEGER	INTEGER	開始位置 (含む) から終了位置 (含まない) までの範囲のカーディナリティを返します。位置は 1 から始まります。重要この関数は、バージョン 3.1.10.0 以降でのみサポートされます。	`RB_RANGE_CARDINALITY(RB_BUILD(ARRAY [1,2,3]),2,3)`
RB_SELECT	ROARING BITMAP, INTEGER, INTEGER	ROARING BITMAP	開始位置 (含む) から終了位置 (含まない) までの範囲内のビットマップのオフセットを返します。重要この関数は、バージョン 3.1.10.0 以降でのみサポートされます。	`RB_SELECT(RB_BUILD(ARRAY [1,3,4,5,7,9]),2, 3)`

集計関数

関数名	入力データの型	出力データの型	説明	例
RB_BUILD_AGG	INTEGER	ROARING BITMAP	オフセットをビットマップに集約します。	`RB_CARDINALITY(RB_BUILD_AGG(1))`
RB_OR_AGG	ROARING BITMAP	ROARING BITMAP	OR 集約を実行します。	`RB_CARDINALITY(RB_OR_AGG(RB_BUILD(array[1,2,3])))`
RB_AND_AGG	ROARING BITMAP	ROARING BITMAP	AND 集約を実行します。	`RB_CARDINALITY(RB_AND_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_XOR_AGG	ROARING BITMAP	ROARING BITMAP	XOR 集約を実行します。	`RB_CARDINALITY(RB_XOR_AGG(RB_BUILD(ARRAY[1,2,3])))`
RB_OR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	OR 集約を実行し、そのカーディナリティを返します。	`RB_OR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_AND_CARDINALITY_AGG	ROARING BITMAP	INTEGER	AND 集約を実行し、そのカーディナリティを返します。	`RB_AND_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`
RB_XOR_CARDINALITY_AGG	ROARING BITMAP	INTEGER	XOR 集約を実行し、そのカーディナリティを返します。	`RB_XOR_CARDINALITY_AGG(RB_BUILD(ARRAY[1,2,3]))`

例

以下のセクションでは、Roaring Bitmap 関数の使用方法に関する完全な例を示します。

内部テーブル

ROARINGBITMAP 列を含む内部テーブルを作成します。
```
CREATE TABLE `test_rb` (
  `id` INT,
  `rb` ROARINGBITMAP
);
```

テーブルにデータを書き込みます。

INSERT INTO test_rb VALUES (1, '[1, 2, 3]');
INSERT INTO test_rb VALUES (2, '[2, 3, 4, 5, 6]');

Roaring Bitmap スカラー関数を使用してカーディナリティを計算します。

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

次の結果が返されます：

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  5 |
|    1 |                  3 |
+------+--------------------+

Roaring Bitmap 集計関数を使用して集約を実行します。

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

次の結果が返されます：

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         6 |
+---------------------------+

外観

ROARINGBITMAP 列を含む外部テーブルを作成します。

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

説明

外部テーブルのパラメーターの詳細については、「OSS 非パーティション外部テーブル」をご参照ください。

テーブルにデータを書き込みます。

重要
`INSERT INTO` を使用したデータ書き込みは非効率です。大規模なデータセットの場合は、抽出・変換・書き出し (ETL) ツールを使用して Parquet データファイルを生成します。その後、外部テーブルを作成する前に、対応する OSS パスにファイルをアップロードします。
```
INSERT INTO test_rb SELECT 1, rb_build(ARRAY[1,2,3]);
INSERT INTO test_rb SELECT 2, rb_build(ARRAY[2,3,4,5]);
```

Roaring Bitmap スカラー関数を使用してカーディナリティを計算します。

SELECT id, RB_CARDINALITY(rb) FROM test_rb;

次の結果が返されます：

+------+--------------------+
| id   | rb_cardinality(rb) |
+------+--------------------+
|    2 |                  4 |
|    1 |                  3 |
+------+--------------------+

Roaring Bitmap 集計関数を使用して集約を実行します。

SELECT RB_OR_CARDINALITY_AGG(rb) FROM test_rb;

次の結果が返されます：

+---------------------------+
| rb_or_cardinality_agg(rb) |
+---------------------------+
|                         5 |
+---------------------------+

ユーザーセグメンテーションシナリオの実践

このシナリオでは、元のタグテーブルを Roaring Bitmap タグテーブルに変換し、Roaring Bitmap 計算を実行します。プロセスを次の図に示します：

ステップ 1：元のタグテーブルの準備

`users_base` という名前のテスト用の元のタグテーブルを作成します。

CREATE TABLE users_base(
   uid INT,
   tag1 STRING, // tag1 の値の範囲は x、y、z です。
   tag2 STRING, // tag2 の値の範囲は a と b です。
   tag3 INT // tag3 の値の範囲は 1 から 10 です。
);

1 億行のランダムデータを生成します。生成されたデータがユーザーのタグデータを表すと仮定します。

SUBMIT JOB
INSERT OVERWRITE users_base
SELECT CAST(ROW_NUMBER() OVER (ORDER BY c1) AS INT) AS uid, SUBSTRING('xyz', FLOOR(RAND() * 3) + 1, 1) AS tag1, SUBSTRING('ab', FLOOR(RAND() * 2) + 1, 1) AS tag2, CAST(FLOOR(RAND() * 10) + 1 AS INT) as tag3 FROM  
(
SELECT A.c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS A(c1)
  JOIN
  (SELECT c1 FROM
UNNEST(RB_BUILD_RANGE(0, 10000)) AS B(c1)
));

元のタグテーブル `users_base` から 10 行のデータをクエリします。

SELECT * FROM users_base LIMIT 10;

次の結果が返されます：

+--------+------+------+------+
| uid    | tag1 | tag2 | tag3 |
+--------+------+------+------+
|  74526 | y    | b    |    3 |
|  75611 | z    | b    |   10 |
|  80850 | x    | b    |    5 |
|  81656 | z    | b    |    7 |
| 163845 | x    | b    |    2 |
| 167007 | y    | b    |    4 |
| 170541 | y    | b    |    9 |
| 213108 | x    | a    |   10 |
|  66056 | y    | b    |    4 |
|  67761 | z    | a    |    2 |
+--------+------+------+------+

ステップ 2：元のタグテーブルのグループ化

Roaring Bitmap タグテーブルを設計する際、分散コンピューティングエンジンの同時実行機能を活用できます。この例の `user_group` のように、並列計算のために UID をグループ化するグループ化フィールドを追加することを推奨します。グループサイズは、ご利用のクラスター内の ACU の総数とビジネス要件に基づいて決定できます。次の原則に従ってください：

一般的に、グループが多いほど計算能力は高くなります。ただし、グループが多すぎると、各 Roaring Bitmap フィールドの要素が少なくなりすぎる可能性があります。これにより、ビットマップの計算属性を十分に活用できなくなります。
ベストプラクティスとして、各グループの Roaring Bitmap 空間に 1 億行未満が含まれるようにすることを推奨します。たとえば、元の UID 空間に 100 億レコードがある場合、100 グループを作成でき、各グループの空間には 1 億レコードが含まれます。

この例では 16 グループを使用します。レコードを uid % 16 でグループ化し、結果を `user_group` フィールドに格納します。uid / 16 を使用してオフセットを計算し、結果を `offset` フィールドに格納します。数式は uid = 16 * offset + user_group です。その後、オフセットを使用して Roaring Bitmap を計算します。

このグループ化メソッドは一例です。ビジネス要件に合ったグループ化関数を設計する必要があります。

グループ化フィールドを追加して `users` タグテーブルを作成します。

CREATE TABLE users(
   uid INT,
   tag1 STRING,
   tag2 STRING,
   tag3 INT,
   user_group INT, // グループ化フィールド
   offset INT // オフセットフィールド
);

`users_base` テーブルから `users` テーブルにデータを書き込みます。

SUBMIT JOB INSERT OVERWRITE users SELECT uid, tag1, tag2, tag3, CAST(uid%16 AS INT), CAST(FLOOR(uid/16) AS INT) FROM users_base;

`users` テーブルから 10 行のデータをクエリします。

SELECT * FROM users LIMIT 10;

次の結果が返されます：

+---------+------+------+------+------------+--------+
| uid     | tag1 | tag2 | tag3 | user_group | offset |
+---------+------+------+------+------------+--------+
|  377194 | z    | b    |   10 |         10 |  23574 |
|  309440 | x    | a    |    1 |          0 |  19340 |
|  601745 | z    | a    |    7 |          1 |  37609 |
|  753751 | z    | b    |    3 |          7 |  47109 |
|  988186 | y    | a    |   10 |         10 |  61761 |
|  883822 | x    | a    |    9 |         14 |  55238 |
|  325065 | x    | b    |    6 |          9 |  20316 |
| 1042875 | z    | a    |   10 |         11 |  65179 |
|  928606 | y    | b    |    5 |         14 |  58037 |
|  990858 | z    | a    |    8 |         10 |  61928 |
+---------+------+------+------+------------+--------+

ステップ 3：Roaring Bitmap タグテーブルの構築

内部テーブル

`tag1` 用の Roaring Bitmap タグテーブル `tag_tbl_1` を作成します。

CREATE TABLE `tag_tbl_1` (
  `tag1` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

`users` テーブルから `tag_tbl_1` テーブルにデータを書き込みます。

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

`tag_tbl_1` タグテーブルからデータをクエリします。

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

次の結果が返されます：

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| y    |         13 |             563654 |
| x    |         11 |             565013 |
| z    |          2 |             564428 |
| x    |          4 |             564377 |
...                                 
| z    |          5 |             564333 |
| x    |          8 |             564808 |
| x    |          0 |             564228 |
| y    |          3 |             563325 |
+------+------------+--------------------+

`tag2` 用の Roaring Bitmap タグテーブル `tag_tbl_2` を作成します。

CREATE TABLE `tag_tbl_2` (
  `tag2` STRING,
  `rb` ROARINGBITMAP,
  `user_group` INT
);

`users` テーブルから `tag_tbl_2` テーブルにデータを書き込みます。

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

`tag_tbl_2` タグテーブルからデータをクエリします。

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

次の結果が返されます：

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| a    |          9 |            3123039 |
| a    |          5 |            3123973 |
| a    |         12 |            3122414 |
| a    |          7 |            3127218 |
| a    |         15 |            3125403 |
...                                  
| a    |         10 |            3122698 |
| b    |          4 |            3126091 |
| b    |          3 |            3124626 |
| b    |          9 |            3126961 |
| b    |         14 |            3125351 |
+------+------------+--------------------+

外観

`tag1` 用の Roaring Bitmap タグテーブル `tag_tbl_1` を作成します。

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

`users` テーブルから `tag_tbl_1` テーブルにデータを書き込みます。

INSERT OVERWRITE tag_tbl_1 SELECT tag1, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag1, user_group;

`tag_tbl_1` タグテーブルからデータをクエリします。

SELECT tag1, user_group, RB_CARDINALITY(rb) FROM tag_tbl_1;

次の結果が返されます：

+------+------------+--------------------+
| tag1 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| z    |          7 |            2082608 |
| x    |         10 |            2082953 |
| y    |          7 |            2084730 |
| x    |         14 |            2084856 |
...                                  
| z    |         15 |            2084535 |
| z    |          5 |            2083204 |
| x    |         11 |            2085239 |
| z    |          1 |            2084879 |
+------+------------+--------------------+

`tag2` 用の Roaring Bitmap タグテーブル `tag_tbl_2` を作成します。

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

`users` テーブルから `tag_tbl_2` テーブルにデータを書き込みます。

INSERT OVERWRITE tag_tbl_2 SELECT tag2, RB_BUILD_AGG(offset), user_group FROM users GROUP BY tag2, user_group;

`tag_tbl_2` タグテーブルからデータをクエリします。

SELECT tag2, user_group, RB_CARDINALITY(rb) FROM tag_tbl_2;

次の結果が返されます：

+------+------------+--------------------+
| tag2 | user_group | rb_cardinality(rb) |
+------+------------+--------------------+
| b    |         11 |            3121361 |
| a    |          6 |            3124750 |
| a    |          1 |            3125433 |
...                                    
| b    |          2 |            3126523 |
| b    |         12 |            3123452 |
| a    |          4 |            3126111 |
| a    |         13 |            3123316 |
| a    |          2 |            3123477 |
+------+------------+--------------------+

ステップ 4：Roaring Bitmap タグテーブルを使用したデータ分析

シナリオ 1：フィルタリングと分析

このシナリオでは、条件 tag1 IN ('x', 'y') を満たすユーザーの分布を `tag2` ディメンションで分析し、結果を縦棒グラフで表示します。

理解を深めるために、まず条件 tag1 IN ('x', 'y') を満たすユーザーをクエリします。

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

条件 tag1 IN ('x', 'y') を満たすユーザーを `tag2` ディメンションで示す縦棒グラフのデータをクエリします。

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

シナリオ 2：Roaring Bitmap タグテーブルの積集合、和集合、差集合の計算

Roaring Bitmap タグテーブル `tag_tbl_1` から条件 tag1 = 'x' OR tag1 = 'y' を満たすデータを取得します。Roaring Bitmap タグテーブル `tag_tbl_2` から条件 tag2 = 'b' を満たすデータを取得します。2 つのタグテーブルのデータに対して積集合計算を実行し、tag1 = 'x' OR tag1 = 'y' と tag2 = 'b' の両方の条件を満たすユーザーを特定します。

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：元のタグテーブルと Roaring Bitmap タグテーブル間の積集合、和集合、差集合の計算

Roaring Bitmap タグテーブル `tag_tbl_1` から条件 tag1 = 'x' OR tag1 = 'y' を満たすデータを取得します。元のタグテーブル `users` から条件 tag2 = 'b' を満たすデータを取得します。2 つのテーブルのデータに対して積集合計算を実行し、tag1 = 'x' OR tag1 = 'y' と tag2 = 'b' の両方の条件を満たすデータを取得します。

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：シナリオ 2 の Roaring Bitmap を OSS にエクスポート (外部テーブル用)

計算結果をエクスポートするために `tag_tbl_3` タグテーブルを作成します。

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 の計算結果を `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;

説明

SQL 文が実行されると、ファイルは Parquet フォーマットで oss://testBucketName/roaringbitmap/tag_tbl_3/ パスに格納されます。

シナリオ 5：クエリの高速化 (外部テーブル用)

クエリを高速化するために、`tag1` タグテーブルから内部テーブルにデータをインポートします。

`tag_tbl_1_cstore` という名前の内部テーブルを作成し、`rb` フィールドを VARBINARY 型として定義します。
```
CREATE TABLE `tag_tbl_1_cstore` (
 `tag1` VARCHAR,
 `rb` VARBINARY,
 `user_group` INT
);
```
OSS の `tag1` タグテーブルから `tag_tbl_1_cstore` 内部テーブルにデータをインポートします。
```
INSERT INTO tag_tbl_1_cstore SELECT tag1, RB_TO_VARBINARY(rb), user_group FROM tag_tbl_1;
```

`tag_tbl_1_cstore` テーブルからデータをクエリします。

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