Grouping Sets、Rollup、Cube 拡張の構文と使用例 - PolarDB - Alibaba Cloud ドキュメントセンター

PolarDB-X 1.0 は、GROUP BY 句を GROUPING SETS、ROLLUP、CUBE の 3 つの集約構成で拡張しています。これにより、複数の SELECT ... UNION ALL 文を記述する代わりに、単一のクエリで多次元の集計結果を取得できます。本トピックでは、これらの構文、使用例、および結果内の NULL 値を解釈するための GROUPING 関数と GROUPING_ID 関数について説明します。

前提条件

PolarDB-X 1.0 インスタンスのバージョンは 5.4.10 以降である必要があります。

注意事項

本トピックで説明する GROUP BY 拡張機能は、いずれも SQL クエリを LogicalView オペレーターにプッシュダウンすることを許可しません。詳細については、「SQL の書き換えとプッシュダウン」をご参照ください。

本トピックのすべての例では、同じ requests テーブルおよびデータセットを使用します。例を実行する前に、テーブルをセットアップしてください。テーブルを作成します。

CREATE TABLE requests (
  `id` int(10) UNSIGNED NOT NULL,
  `os` varchar(20) DEFAULT NULL,
  `device` varchar(20) DEFAULT NULL,
  `city` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8 dbpartition BY hash(`id`) tbpartition BY hash(`id`);

テストデータを挿入します。

INSERT INTO requests (id, os, device, city) VALUES
(1, 'windows', 'PC', '北京'),
(2, 'windows', 'PC', '石家荘'),
(3, 'linux', 'Phone', '北京'),
(4, 'windows', 'PC', '北京'),
(5, 'ios', 'Phone', '石家荘'),
(6, 'linux', 'PC', '北京'),
(7, 'windows', 'Phone', '石家荘');

結果内の NULL： 複数のグループ化セットがマージされると、特定のグループ化セットに関与していない列は NULL として表示されます。この NULL はプレースホルダーであり、データ値ではありません。プレースホルダーの NULL とデータ内の実際の NULL 値を区別するには、GROUPING 関数および GROUPING_ID 関数を使用してください。「GROUPING 関数および GROUPING_ID 関数」をご参照ください。

GROUPING SETS 拡張

GROUPING SETS は GROUP BY 句の拡張機能で、複数のグループ化操作の結果を単一の結果セットに連結します。その結果は UNION ALL と似ていますが、GROUPING SETS は重複行を削除しません。

構文

GROUPING SETS (
  { expr_1 | ( expr_1a [, expr_1b ] ...) |
    ROLLUP ( expr_list ) | CUBE ( expr_list )
  } [, ...] )

リスト内の各式は 1 つのグループ化セットを定義します。また、GROUPING SETS 内に ROLLUP または CUBE をネストして、さらに多くのグループ化の組み合わせを生成することもできます。

使用例

基本的なグループ化セット

(os, device)、city ごとにリクエストをグループ化し、総計を計算します。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS((os, device), (city), ());

これは次と同等です。

SELECT os, device, NULL, count(*) FROM requests GROUP BY os, device
UNION ALL
SELECT NULL, NULL, NULL, count(*) FROM requests
UNION ALL
SELECT NULL, NULL, city, count(*) FROM requests GROUP BY city;

結果：

+---------+--------+--------------+----------+
| OS      | デバイス | 都市         | カウント(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | Beijing      |        1 |
| windows | Phone  | Shijiazhuang |        1 |
| windows | PC     | Beijing      |        2 |
| ios     | Phone  | Shijiazhuang |        1 |
| windows | PC     | Shijiazhuang |        1 |
| linux   | PC     | Beijing      |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | NULL   | Beijing      |        2 |
| windows | NULL   | Shijiazhuang |        2 |
| windows | NULL   | Beijing      |        2 |
| ios     | NULL   | Shijiazhuang |        1 |
| linux   | NULL   | NULL         |        2 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| NULL    | Phone  | Beijing      |        1 |
| NULL    | Phone  | Shijiazhuang |        2 |
| NULL    | PC     | Beijing      |        3 |
| NULL    | PC     | Shijiazhuang |        1 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | PC     | NULL         |        4 |
| NULL    | NULL   | Beijing      |        4 |
| NULL    | NULL   | Shijiazhuang |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUPING SETS 内での ROLLUP のネスト

SELECT os, device, city, count(*) FROM requests
GROUP BY GROUPING SETS((city), ROLLUP(os, device));

これは次と同等です。

SELECT os, device, city, count(*) FROM requests
GROUP BY GROUPING SETS((city), (os), (os, device), ());

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | 石家荘       |        3 |
| NULL    | NULL   | 北京         |        4 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| linux   | NULL   | NULL         |        2 |
| ios     | NULL   | NULL         |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUPING SETS 内での CUBE のネスト

SELECT os, device, city, count(*) FROM requests
GROUP BY GROUPING SETS((city), CUBE(os, device));

これは次と同等です。

SELECT os, device, city, count(*) FROM requests
GROUP BY GROUPING SETS((city), (os), (os, device), (), (device));

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| NULL    | NULL   | 北京         |        4 |
| NULL    | NULL   | 石家荘       |        3 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | PC     | NULL         |        4 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUP BY、CUBE、および GROUPING SETS の組み合わせ

SELECT os, device, city, count(*)
FROM requests
GROUP BY os, cube(os, device), grouping sets(city);

これは次と同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS((os, device, city), (os, city));

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | 北京         |        1 |
| windows | Phone  | 石家荘       |        1 |
| windows | PC     | 石家荘       |        1 |
| linux   | PC     | 北京         |        1 |
| windows | PC     | 北京         |        2 |
| ios     | Phone  | 石家荘       |        1 |
| linux   | NULL   | 北京         |        2 |
| windows | NULL   | 石家荘       |        2 |
| windows | NULL   | 北京         |        2 |
| ios     | NULL   | 石家荘       |        1 |
+---------+--------+--------------+----------+

ROLLUP 拡張

ROLLUP は階層的なグループ化セットを生成し、各レベルで小計を、結果セット全体で総計を出力します。階層は式リストの左から右への順序に従い、最も左側の式が階層の最上位となり、右側の各式が 1 レベル下になります。

重要

列の順序が集約の階層を決定します。たとえば、os に device が含まれる場合は、ROLLUP(os, device) を使用します。順序を逆にすると、異なる（意図しない可能性のある）階層が生成されます。

構文

ROLLUP ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)

(expr_1a, expr_1b) のように括弧で囲まれた式は、階層内の単一レベルを定義します。
各グループ化レベルについて、PolarDB-X 1.0 は一意の値の組み合わせごとに小計を返します。そのレベルでロールアップされた列については、小計行に NULL が表示されます。
総計行（すべての列が NULL）は常に末尾に追加されます。

N 要素の ROLLUP 仕様は、N+1 個のグループ化セットを生成します。

使用例

(os, device, city) に対する ROLLUP

SELECT os, device, city, count(*)
FROM requests
GROUP BY ROLLUP(os, device, city);

これは次と同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS ((os, device, city), (os, device), (os), ());

また、MySQL スタイルの構文とも同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY os, device, city WITH ROLLUP;

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | 北京         |        2 |
| ios     | Phone  | 石家荘       |        1 |
| windows | PC     | 石家荘       |        1 |
| linux   | PC     | 北京         |        1 |
| linux   | Phone  | 北京         |        1 |
| windows | Phone  | 石家荘       |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| linux   | NULL   | NULL         |        2 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

(os, (os, device), city) に対する ROLLUP

(os, device) を括弧で囲むことで、この組み合わせを階層内の単一レベルとして扱います。

SELECT os, device, city, count(*)
FROM requests
GROUP BY ROLLUP(os, (os, device), city);

これは次と同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS ((os, device, city), (os, device), (os), ());

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| windows | PC     | 北京         |        2 |
| windows | PC     | 石家荘       |        1 |
| linux   | PC     | 北京         |        1 |
| linux   | Phone  | 北京         |        1 |
| windows | Phone  | 石家荘       |        1 |
| ios     | Phone  | 石家荘       |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| windows | NULL   | NULL         |        4 |
| linux   | NULL   | NULL         |        2 |
| ios     | NULL   | NULL         |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

CUBE 拡張

CUBE は、リスト内の式のすべての可能な組み合わせに対して小計を生成します。単一の階層に従うのではなく、N 個の式に対して 2^N 個のグループ化セットを生成するため、同じ式リストに対して ROLLUP よりも多くの行を返します。

ディメンションに自然な階層（年 → 四半期 → 月など）がある場合は ROLLUP を使用します。ディメンションが独立しており、すべてのクロスディメンションの組み合わせを分析したい場合は CUBE を使用します。

構文

CUBE ( { expr_1 | ( expr_1a [, expr_1b ] ...) }
  [, expr_2 | ( expr_2a [, expr_2b ] ...) ] ...)

括弧で囲まれた式は単一のグループを定義します。
PolarDB-X 1.0 は、すべての式にわたる各一意の値および各一意の値の組み合わせに対して小計を返します。集約対象の列については、小計行に NULL が表示されます。総計行は常に含まれます。

使用例

(os, device, city) に対する CUBE

SELECT os, device, city, count(*)
FROM requests
GROUP BY CUBE(os, device, city);

これは次と同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS (
  (os, device, city), (os, device), (os, city), (device, city),
  (os), (device), (city), ()
);

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | 北京         |        1 |
| windows | Phone  | 石家荘       |        1 |
| windows | PC     | 北京         |        2 |
| ios     | Phone  | 石家荘       |        1 |
| windows | PC     | 石家荘       |        1 |
| linux   | PC     | 北京         |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| ios     | Phone  | NULL         |        1 |
| linux   | PC     | NULL         |        1 |
| linux   | NULL   | 北京         |        2 |
| windows | NULL   | 石家荘       |        2 |
| windows | NULL   | 北京         |        2 |
| ios     | NULL   | 石家荘       |        1 |
| linux   | NULL   | NULL         |        2 |
| windows | NULL   | NULL         |        4 |
| ios     | NULL   | NULL         |        1 |
| NULL    | Phone  | 北京         |        1 |
| NULL    | Phone  | 石家荘       |        2 |
| NULL    | PC     | 北京         |        3 |
| NULL    | PC     | 石家荘       |        1 |
| NULL    | Phone  | NULL         |        3 |
| NULL    | PC     | NULL         |        4 |
| NULL    | NULL   | 北京         |        4 |
| NULL    | NULL   | 石家荘       |        3 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

((os, device), (device, city)) に対する CUBE

複数の列を括弧で囲むことで、各グループを単一の単位として扱います。

SELECT os, device, city, count(*)
FROM requests
GROUP BY CUBE((os, device), (device, city));

これは次と同等です。

SELECT os, device, city, count(*)
FROM requests
GROUP BY GROUPING SETS ((os, device, city), (os, device), (device, city), ());

結果：

+---------+--------+--------------+----------+
| os      | device | city         | count(*) |
+---------+--------+--------------+----------+
| linux   | Phone  | 北京         |        1 |
| windows | Phone  | 石家荘       |        1 |
| windows | PC     | 北京         |        2 |
| windows | PC     | 石家荘       |        1 |
| linux   | PC     | 北京         |        1 |
| ios     | Phone  | 石家荘       |        1 |
| linux   | Phone  | NULL         |        1 |
| windows | Phone  | NULL         |        1 |
| windows | PC     | NULL         |        3 |
| linux   | PC     | NULL         |        1 |
| ios     | Phone  | NULL         |        1 |
| NULL    | Phone  | 北京         |        1 |
| NULL    | Phone  | 石家荘       |        2 |
| NULL    | PC     | 北京         |        3 |
| NULL    | PC     | 石家荘       |        1 |
| NULL    | NULL   | NULL         |        7 |
+---------+--------+--------------+----------+

GROUPING 関数および GROUPING_ID 関数

GROUPING SETS、ROLLUP、または CUBE が小計行を生成する際、現在のグループ化セットに含まれない列にはプレースホルダーとして NULL が使用されます。データ内にも実際の NULL 値が含まれている場合、結果だけでは NULL がプレースホルダーなのか実際の値なのかを判断できません。GROUPING 関数および GROUPING_ID 関数は、このあいまいさを解消します。

GROUPING 関数

GROUPING 関数は、単一の列式を引数として受け取ります。戻り値は次のとおりです。

0 — その列で行がグループ化されています（この行の NULL はデータ値です）
1 — その列で行がグループ化されていません（NULL は集約によるプレースホルダーです）

構文

SELECT [ expr ...,] GROUPING( col_expr ) [, expr ] ...
FROM ...
GROUP BY { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr [, ...] ) [, ...]

GROUPING の引数は、ROLLUP、CUBE、または GROUPING SETS の式リストで使用されるディメンション列である必要があります。

GROUPING_ID 関数

GROUPING_ID 関数は複数の列を一度に処理し、列ごとに個別の GROUPING 呼び出しを行う必要をなくします。指定された列に対してビットマップアルゴリズムを適用し、現在の行でどの列がプレースホルダーであるかをエンコードした単一の整数を返します。

構文

SELECT [ expr ...,]
  GROUPING_ID( col_expr_1 [, col_expr_2 ] ... )
  [, expr ] ...
FROM ...
GROUP BY { ROLLUP | CUBE | GROUPING SETS }( [...,] col_expr_1 [, col_expr_2 ] [, ...] ) [, ...]

使用例

次のクエリは、3 つのインライン列に対して CUBE を使用し、各行のビットマップ値を示すために GROUPING 関数および GROUPING_ID 関数を含んでいます。

SELECT a, b, c, count(*),
  grouping(a)          ga,
  grouping(b)          gb,
  grouping(c)          gc,
  grouping_id(a, b, c) groupingid
FROM (SELECT 1 AS a, 2 AS b, 3 AS c)
GROUP BY CUBE(a, b, c);

結果：

+------+------+------+----------+------+------+------+------------+
| a    | b    | c    | count(*) | ga   | gb   | gc   | groupingid |
+------+------+------+----------+------+------+------+------------+
|    1 |    2 |    3 |        1 |    0 |    0 |    0 |          0 |
|    1 |    2 | NULL |        1 |    0 |    0 |    1 |          1 |
|    1 | NULL |    3 |        1 |    0 |    1 |    0 |          2 |
|    1 | NULL | NULL |        1 |    0 |    1 |    1 |          3 |
| NULL |    2 |    3 |        1 |    1 |    0 |    0 |          4 |
| NULL |    2 | NULL |        1 |    1 |    0 |    1 |          5 |
| NULL | NULL |    3 |        1 |    1 |    1 |    0 |          6 |
| NULL | NULL | NULL |        1 |    1 |    1 |    1 |          7 |
+------+------+------+----------+------+------+------+------------+

groupingid 値は、ビットベクター [ga, gb, gc] の整数表現です。たとえば、groupingid = 5 は、ga=1, gb=0, gc=1（バイナリ 101）を意味します。つまり、列 a および c はプレースホルダーであり、列 b は実際の値を保持しています。