GROUPING SETS を使用した多次元集約 - MaxCompute

GROUPING SETS は、UNION ALL で複数の SELECT 文を連結することなく、単一のクエリで複数のグルーピングディメンションにわたる集約を計算できる GROUP BY 句の拡張です。これにより、MaxCompute はより高いパフォーマンスでより効率的な実行計画を生成できます。

基本概念

以下の拡張機能は GROUPING SETS と連携して動作します。

拡張機能	説明
`CUBE`	指定された列のすべての可能な組み合わせに対して集約を生成します。
`ROLLUP`	最も詳細なレベルから総計まで、列階層の各レベルで集約を生成します。
`GROUPING`	行が指定された列で集約されている場合 (NULL が実値である場合) は `0` を返し、列が現在のグループ化セットの一部ではない場合 (NULL がプレースホルダーである場合) は `1` を返します。
`GROUPING_ID`	1つ以上の列に対する `GROUPING` 結果をエンコードするビットマップ整数を返します。
`GROUPING__ID`	Hive互換クエリ用のパラメーターなしのバリアントです。`GROUPING_ID(<GROUP BY column list>)` と同等であり、列は `GROUP BY` 句と同じ順序です。Apache Hive 2.3.0以降での使用を推奨します。

注意事項

結果における NULL

GROUPING SETS が異なる基準でグループ化された複数の結果セットをマージする場合、現在のグループ化セットの一部ではない列は NULL で埋められます。この NULL はプレースホルダーであり、実際のNULL値ではありません。GROUPING 関数を使用して、この2つの違いを区別できます (「GROUPINGとGROUPING_ID」をご参照ください)。

複数のグループ化式の組み合わせ

同じ句で GROUP BY、CUBE、ROLLUP、および GROUPING SETS を混在させると、MaxCompute はクロスプロダクトセマンティクスを適用します。すべての式からのグループ化セットの各組み合わせが最終結果に含まれます。

例:

GROUP BY os, CUBE (os, device), GROUPING SETS (city)
-- Equivalent to:
GROUP BY GROUPING SETS ((os, device, city), (os, city), (os, device, city))

サンプルデータの準備

このトピックのすべての例では、同じ requests テーブルを使用します。

CREATE TABLE requests LIFECYCLE 20 AS
SELECT * FROM VALUES
    (1, 'windows', 'PC',    'Beijing'),
    (2, 'windows', 'PC',    'Shijiazhuang'),
    (3, 'linux',   'Phone', 'Beijing'),
    (4, 'windows', 'PC',    'Beijing'),
    (5, 'ios',     'Phone', 'Shijiazhuang'),
    (6, 'linux',   'PC',    'Beijing'),
    (7, 'windows', 'Phone', 'Shijiazhuang')
AS t(id, os, device, city);

GROUPING SETS の例

基本的な GROUPING SETS

次の例では、リクエストを (os, device)、city でグループ化し、総計を生成します。これらすべてを1つのクエリで行います。

GROUPING SETS を使用しない場合 (3つの個別のスキャン):

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

GROUPING SETS を使用する場合 (1つのスキャン):

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

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| NULL       | NULL       | NULL         | 7   |
| NULL       | NULL       | Beijing      | 4   |
| NULL       | NULL       | Shijiazhuang | 3   |
| ios        | Phone      | NULL         | 1   |
| linux      | PC         | NULL         | 1   |
| linux      | Phone      | NULL         | 1   |
| windows    | PC         | NULL         | 3   |
| windows    | Phone      | NULL         | 1   |
+------------+------------+--------------+-----+

4行目から8行目の city 列の NULL は、city がそのグループ化セットの一部ではなかったことを示すプレースホルダーであり、データ内のNULL値ではありません。

CUBE の例

CUBE は、指定された列のすべての可能な組み合わせに展開されます。

例 1: すべての個別列

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY CUBE (os, device, city);
-- Equivalent to:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os,city),(device,city),(os),(device),(city),())

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| NULL       | NULL       | NULL         | 7   |
| NULL       | NULL       | Beijing      | 4   |
| NULL       | NULL       | Shijiazhuang | 3   |
| NULL       | PC         | NULL         | 4   |
| NULL       | PC         | Beijing      | 3   |
| NULL       | PC         | Shijiazhuang | 1   |
| NULL       | Phone      | NULL         | 3   |
| NULL       | Phone      | Beijing      | 1   |
| NULL       | Phone      | Shijiazhuang | 2   |
| ios        | NULL       | NULL         | 1   |
| ios        | NULL       | Shijiazhuang | 1   |
| ios        | Phone      | NULL         | 1   |
| ios        | Phone      | Shijiazhuang | 1   |
| linux      | NULL       | NULL         | 2   |
| linux      | NULL       | Beijing      | 2   |
| linux      | PC         | NULL         | 1   |
| linux      | PC         | Beijing      | 1   |
| linux      | Phone      | NULL         | 1   |
| linux      | Phone      | Beijing      | 1   |
| windows    | NULL       | NULL         | 4   |
| windows    | NULL       | Beijing      | 2   |
| windows    | NULL       | Shijiazhuang | 2   |
| windows    | PC         | NULL         | 3   |
| windows    | PC         | Beijing      | 2   |
| windows    | PC         | Shijiazhuang | 1   |
| windows    | Phone      | NULL         | 1   |
| windows    | Phone      | Shijiazhuang | 1   |
+------------+------------+--------------+-----+

例 2: 列グループ

CUBE に列グループを渡すと、各グループは組み合わせロジックにおいて単一の単位として扱われます。

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY CUBE ((os, device), (device, city));
-- Equivalent to:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(device,city),())

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| NULL       | NULL       | NULL         | 7   |
| NULL       | PC         | Beijing      | 3   |
| NULL       | PC         | Shijiazhuang | 1   |
| NULL       | Phone      | Beijing      | 1   |
| NULL       | Phone      | Shijiazhuang | 2   |
| ios        | Phone      | NULL         | 1   |
| ios        | Phone      | Shijiazhuang | 1   |
| linux      | PC         | NULL         | 1   |
| linux      | PC         | Beijing      | 1   |
| linux      | Phone      | NULL         | 1   |
| linux      | Phone      | Beijing      | 1   |
| windows    | PC         | NULL         | 3   |
| windows    | PC         | Beijing      | 2   |
| windows    | PC         | Shijiazhuang | 1   |
| windows    | Phone      | NULL         | 1   |
| windows    | Phone      | Shijiazhuang | 1   |
+------------+------------+--------------+-----+

ROLLUP の例

ROLLUP は、最も詳細なグループ化から総計まで、階層の各レベルで集約を生成します。

例 3: 単純な階層

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY ROLLUP (os, device, city);
-- Equivalent to:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os),())

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| NULL       | NULL       | NULL         | 7   |
| ios        | NULL       | NULL         | 1   |
| ios        | Phone      | NULL         | 1   |
| ios        | Phone      | Shijiazhuang | 1   |
| linux      | NULL       | NULL         | 2   |
| linux      | PC         | NULL         | 1   |
| linux      | PC         | Beijing      | 1   |
| linux      | Phone      | NULL         | 1   |
| linux      | Phone      | Beijing      | 1   |
| windows    | NULL       | NULL         | 4   |
| windows    | PC         | NULL         | 3   |
| windows    | PC         | Beijing      | 2   |
| windows    | PC         | Shijiazhuang | 1   |
| windows    | Phone      | NULL         | 1   |
| windows    | Phone      | Shijiazhuang | 1   |
+------------+------------+--------------+-----+

例 4: 列グループを含む階層

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY ROLLUP (os, (os, device), city);
-- Equivalent to:
-- GROUP BY GROUPING SETS ((os,device,city),(os,device),(os),())

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| NULL       | NULL       | NULL         | 7   |
| ios        | NULL       | NULL         | 1   |
| ios        | Phone      | NULL         | 1   |
| ios        | Phone      | Shijiazhuang | 1   |
| linux      | NULL       | NULL         | 2   |
| linux      | PC         | NULL         | 1   |
| linux      | PC         | Beijing      | 1   |
| linux      | Phone      | NULL         | 1   |
| linux      | Phone      | Beijing      | 1   |
| windows    | NULL       | NULL         | 4   |
| windows    | PC         | NULL         | 3   |
| windows    | PC         | Beijing      | 2   |
| windows    | PC         | Shijiazhuang | 1   |
| windows    | Phone      | NULL         | 1   |
| windows    | Phone      | Shijiazhuang | 1   |
+------------+------------+--------------+-----+

例 5: GROUP BY、CUBE、GROUPING SETS の組み合わせ

次の例では、3つのグループ化式にわたってクロスプロダクトセマンティクスを適用します。

SELECT os, device, city, COUNT(*)
FROM requests
GROUP BY os, CUBE (os, device), GROUPING SETS (city);
-- Equivalent to:
-- GROUP BY GROUPING SETS ((os,device,city),(os,city),(os,device,city))

結果:

+------------+------------+--------------+-----+
| os         | device     | city         | _c3 |
+------------+------------+--------------+-----+
| ios        | NULL       | Shijiazhuang | 1   |
| ios        | Phone      | Shijiazhuang | 1   |
| linux      | NULL       | Beijing      | 2   |
| linux      | PC         | Beijing      | 1   |
| linux      | Phone      | Beijing      | 1   |
| windows    | NULL       | Beijing      | 2   |
| windows    | NULL       | Shijiazhuang | 2   |
| windows    | PC         | Beijing      | 2   |
| windows    | PC         | Shijiazhuang | 1   |
| windows    | Phone      | Shijiazhuang | 1   |
+------------+------------+--------------+-----+

GROUPINGとGROUPING_IDの例

GROUPING SETS はグループ化されていない列を NULL で埋めるため、結果だけでは NULL がデータから来たのか、集約から来たのかを判断できません。GROUPING と GROUPING_ID はこの曖昧さを解消します。

GROUPINGによる NULL プレースホルダーの区別

GROUPING(col) は以下を返します。

0 — 行はcolで集計されます（もしあれば、NULLは実際のNULL値です）
1 — col は現在のグループ化セットに含まれない (NULL はプレースホルダーである)

GROUPING_ID(col1, col2, ...) は、複数の列に対する GROUPING 結果をビットマップ整数としてエンコードします。

SELECT a, b, c, COUNT(*),
    GROUPING(a)          AS ga,
    GROUPING(b)          AS gb,
    GROUPING(c)          AS gc,
    GROUPING_ID(a, b, c) AS groupingid
FROM VALUES (1, 2, 3) AS t(a, b, c)
GROUP BY CUBE (a, b, c);

結果:

+------+------+------+-----+----+----+----+------------+
| a    | b    | c    | _c3 | ga | gb | gc | groupingid |
+------+------+------+-----+----+----+----+------------+
| NULL | NULL | NULL | 1   | 1  | 1  | 1  | 7          |
| NULL | NULL | 3    | 1   | 1  | 1  | 0  | 6          |
| NULL | 2    | NULL | 1   | 1  | 0  | 1  | 5          |
| NULL | 2    | 3    | 1   | 1  | 0  | 0  | 4          |
| 1    | NULL | NULL | 1   | 0  | 1  | 1  | 3          |
| 1    | NULL | 3    | 1   | 0  | 1  | 0  | 2          |
| 1    | 2    | NULL | 1   | 0  | 0  | 1  | 1          |
| 1    | 2    | 3    | 1   | 0  | 0  | 0  | 0          |
+------+------+------+-----+----+----+----+------------+

NULL プレースホルダーを読みやすいラベルに置き換える

GROUPING を IF 式内で使用して、プレースホルダー行の NULL の代わりにラベルを表示します。

SELECT
    IF(GROUPING(os)     = 0, os,     'ALL') AS os,
    IF(GROUPING(device) = 0, device, 'ALL') AS device,
    IF(GROUPING(city)   = 0, city,   'ALL') AS city,
    COUNT(*) AS count
FROM requests
GROUP BY os, device, city GROUPING SETS ((os, device), (city), ());

結果:

+------------+------------+--------------+-------+
| os         | device     | city         | count |
+------------+------------+--------------+-------+
| ALL        | ALL        | ALL          | 7     |
| ALL        | ALL        | Beijing      | 4     |
| ALL        | ALL        | Shijiazhuang | 3     |
| ios        | Phone      | ALL          | 1     |
| linux      | PC         | ALL          | 1     |
| linux      | Phone      | ALL          | 1     |
| windows    | PC         | ALL          | 3     |
| windows    | Phone      | ALL          | 1     |
+------------+------------+--------------+-------+

GROUPING__ID の例

GROUPING__ID は、Hive互換クエリのために提供される GROUPING_ID(<GROUP BY column list>) のパラメーターなしのエイリアスです。列の順序は GROUP BY 句と一致します。

Hive 2.3.0以降を使用する場合は、MaxCompute でこの関数を使用することを推奨します。Hive 2.3.0より前のバージョンを使用する場合は、MaxCompute でこの関数を使用しないことを推奨します。

SELECT a, b, c, COUNT(*), grouping__id
FROM VALUES (1, 2, 3) AS t(a, b, c)
GROUP BY a, b, c GROUPING SETS ((a, b, c), (a));
-- Equivalent to:
SELECT a, b, c, COUNT(*), GROUPING_ID(a, b, c)
FROM VALUES (1, 2, 3) AS t(a, b, c)
GROUP BY a, b, c GROUPING SETS ((a, b, c), (a));

結果:

+------+------+------+-----+-----+
| a    | b    | c    | _c3 | _c4 |
+------+------+------+-----+-----+
| 1    | NULL | NULL | 1   | 3   |
| 1    | 2    | 3    | 1   | 0   |
+------+------+------+-----+-----+

CUBEとROLLUPの等価性

次の表は、一般的な CUBE および ROLLUP 式の GROUPING SETS 等価物を示しています。

CUBE の等価性

式	等価な GROUPING SETS
`GROUP BY CUBE (a, b, c)`	`GROUPING SETS ((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),())`
`GROUP BY CUBE ((a, b), (c, d))`	`GROUPING SETS ((a,b,c,d),(a,b),(c,d),())`
`GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))`	`GROUPING SETS ((a,b,c,d),(a,b,c,e),(a,b,d),(a,b,e),(a,c,d),(a,c,e),(a,d),(a,e))`

ROLLUP の等価性

式	等価な GROUPING SETS
`GROUP BY ROLLUP (a, b, c)`	`GROUPING SETS ((a,b,c),(a,b),(a),())`
`GROUP BY ROLLUP (a, (b, c), d)`	`GROUPING SETS ((a,b,c,d),(a,b,c),(a),())`
`GROUP BY GROUPING SETS ((b), (c), ROLLUP (a,b,c))`	`GROUPING SETS ((b),(c),(a,b,c),(a,b),(a),())`