GROUPING SETS ROLLUP CUBE GROUPING/GROUPING_ID functions 部分的な ROLLUP および CUBE 連結グループネストされたグループ使用に関する注意事項

集計拡張機能

Amazon Redshift では、1 つのステートメントで複数の GROUP BY オペレーションの処理を実行するための集計拡張機能をサポートしています。

集計拡張機能の例では、ある電子機器会社の売上データを格納する orders テーブルを使用しています。orders は以下を使用して作成できます。


CREATE TABLE ORDERS (
    ID INT,
    PRODUCT CHAR(20),
    CATEGORY CHAR(20),
    PRE_OWNED CHAR(1),
    COST DECIMAL
);

INSERT INTO ORDERS VALUES
    (0, 'laptop',       'computers',    'T', 1000),
    (1, 'smartphone',   'cellphones',   'T', 800),
    (2, 'smartphone',   'cellphones',   'T', 810),
    (3, 'laptop',       'computers',    'F', 1050),
    (4, 'mouse',        'computers',    'F', 50);

GROUPING SETS

1 つのステートメントで 1 つ以上のグループ化セットを計算します。グループ化セットとは、1 つの GROUP BY 句のセットで、クエリの結果セットをグループ化できる 0 個以上の列のセットです。GROUP BY GROUPING SETS は、異なる列でグループ化された 1 つの結果セットに対して UNION ALL クエリを実行することに相当します。例えば、GROUP BY GROUPING SETS((a), (b)) は、GROUP BY a UNION ALL GROUP BY b と同等です。

次の例では、製品のカテゴリと販売された製品の種類の両方に従ってグループ化された注文テーブルの製品のコストを返します。



SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);

       category       |       product        | total
----------------------+----------------------+-------
 computers            |                      |  2100
 cellphones           |                      |  1610
                      | laptop               |  2050
                      | smartphone           |  1610
                      | mouse                |    50

(5 rows)

ROLLUP

前の列が後続の列の親と見なされる階層を前提としています。ROLLUP は、指定された列ごとにデータをグループ化し、グループ化された行に加えて、グループ化列の全レベルの合計を表す追加の小計行を返します。例えば、GROUP BY ROLLUP((a), (b)) を使用すると、b が a のサブセクションであると仮定して、最初に a でグループ化された結果セットを返し、次に b でグループ化された結果セットを返すことができます。また、ROLLUP では、列をグループ化せずに結果セット全体を含む行を返します。

GROUP BY ROLLUP((a), (b)) は、GROUP BY GROUPING SETS((a,b), (a), ()) と同等です。

次の例では、最初にカテゴリ別にグループ化された注文テーブルの製品のコストを返し、次にカテゴリが細分化された製品を返します。


SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      |                      |  3710
(6 rows)

CUBE

指定した列ごとにデータをグループ化し、グループ化された行に加えて、グループ化列の全レベルの合計を表す追加の小計行を返します。CUBE は ROLLUP と同じ行を返しますが、ROLLUP の対象とならないグループ列のすべての組み合わせで小計行を追加します。例えば、GROUP BY CUBE ((a), (b)) を使用すると、b が a のサブセクションであると仮定して、最初に a でグループ化された結果セットを返し、次に b でグループ化された結果セット、さらに b のみでグループ化された結果セットを返すことができます。また、CUBE では、列をグループ化せずに結果セット全体を含む行を返します。

GROUP BY CUBE((a), (b)) は GROUP BY GROUPING SETS((a, b), (a), (b), ()) と同等です。

次の例では、最初にカテゴリ別にグループ化された注文テーブルの製品のコストを返し、次にカテゴリが細分化された製品を返します。前述の ROLLUP の例とは異なり、このステートメントはグループ化列のすべての組み合わせの結果を返します。


SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;

       category       |       product        | total
----------------------+----------------------+-------
 cellphones           | smartphone           |  1610
 cellphones           |                      |  1610
 computers            | laptop               |  2050
 computers            | mouse                |    50
 computers            |                      |  2100
                      | laptop               |  2050
                      | mouse                |    50
                      | smartphone           |  1610
                      |                      |  3710
(9 rows)

GROUPING/GROUPING_ID functions

ROLLUP と CUBE は、小計行を示す NULL 値を結果セットに追加します。例えば、GROUP BY ROLLUP((a), (b)) は、b グループ列の値が NULL である行を 1 つ以上返し、それらが a グループ列のフィールドの小計であることを示します。これらの NULL 値は、返されるタプルの形式を満たすためにのみ機能します。

それ自体に NULL 値が格納されているリレーションに対して ROLLUP と CUBE を使用して GROUP BY オペレーションを実行すると、同じグループ化列を持つように見える行を含む結果セットが生成されることがあります。前の例に戻ると、b のグループ化列に NULL 値が格納されている場合、GROUP BY ROLLUP((a), (b)) は、小計ではない b のグループ化列に値が NULL の行を返します。

ROLLUP と CUBE によって作成された NULL 値と、テーブル自体に格納されている NULL 値を区別するには、GROUPING 関数またはそのエイリアスである GROUPING_ID を使用できます。GROUPING は、引数として 1 つのグループ化セットを取り、結果セットの各行について、その位置のグループ化列に対応する 0 または 1 ビットの値を返し、その値を整数に変換します。その位置の値が集計拡張によって作成された NULL 値の場合、GROUPING は 1 を返します。格納されている NULL 値を含む他のすべての値に対しては 0 を返します。

例えば、GROUPING(category, product) は、その行のグループ化列の値に応じて、特定の行について次の値を返すことができます。この例では、テーブル内のすべての NULL 値は、集計拡張によって作成された NULL 値です。

カテゴリ列	製品列	GROUPING 関数のビット値	10 進値
null でない	null でない	00	0
null でない	NULL	01	1
NULL	null でない	10	2
NULL	NULL	11	3

GROUPING 関数は、クエリの SELECT リスト部分に次の形式で表示されます。


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

次の例は、前述の CUBE の例と同じですが、グループ化セットに GROUPING 関数が追加されています。


SELECT category, product,
       GROUPING(category) as grouping0,
       GROUPING(product) as grouping1,
       GROUPING(category, product) as grouping2,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;

       category       |       product        | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
 cellphones           | smartphone           |         0 |         0 |         0 |  1610
 cellphones           |                      |         0 |         1 |         1 |  1610
 computers            | laptop               |         0 |         0 |         0 |  2050
 computers            | mouse                |         0 |         0 |         0 |    50
 computers            |                      |         0 |         1 |         1 |  2100
                      | laptop               |         1 |         0 |         2 |  2050
                      | mouse                |         1 |         0 |         2 |    50
                      | smartphone           |         1 |         0 |         2 |  1610
                      |                      |         1 |         1 |         3 |  3710
(9 rows)

部分的な ROLLUP および CUBE

ROLLUP および CUBE オペレーションは、小計の一部のみで実行できます。

部分的な ROLLUP および CUBE オペレーションの構文は次のとおりです。


GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])

ここで、GROUP BY 句は expr2 以降のレベルの小計行のみを作成します。

次の例は、注文テーブルでの ROLLUP および CUBE オペレーションの一部を示しています。最初に製品が中古品かどうかでグループ化し、次にカテゴリ列と製品列で ROLLUP と CUBE を実行します。


SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(9 rows)

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
(13 rows)

「pre_owned」列は ROLLUP および CUBE オペレーションには含まれないため、他のすべての行を含む総計行はありません。

連結グループ

複数の GROUPING SETS/ROLLUP/CUBE 句を連結して、さまざまなレベルの小計を計算できます。連結グループでは、指定されたグループ化セットの直積集合を返します。

GROUPING SETS/ROLLUP/CUBE 句を連結する構文は次のとおりです。


GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
         {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]

次の例を考慮して、小さな連結グループがどのようにして大きな最終結果セットを生成できるかを確認してください。


SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;

 pre_owned |       category       |       product        | group_id | total
-----------+----------------------+----------------------+----------+-------
 F         | computers            | laptop               |        0 |  1050
 F         | computers            | mouse                |        0 |    50
 T         | cellphones           | smartphone           |        0 |  1610
 T         | computers            | laptop               |        0 |  1000
           | cellphones           | smartphone           |        1 |  1610
           | computers            | laptop               |        1 |  2050
           | computers            | mouse                |        1 |    50
 F         | computers            |                      |        2 |  1100
 T         | cellphones           |                      |        2 |  1610
 T         | computers            |                      |        2 |  1000
           | cellphones           |                      |        3 |  1610
           | computers            |                      |        3 |  2100
 F         |                      | laptop               |        4 |  1050
 F         |                      | mouse                |        4 |    50
 T         |                      | laptop               |        4 |  1000
 T         |                      | smartphone           |        4 |  1610
           |                      | laptop               |        5 |  2050
           |                      | mouse                |        5 |    50
           |                      | smartphone           |        5 |  1610
 F         |                      |                      |        6 |  1100
 T         |                      |                      |        6 |  2610
           |                      |                      |        7 |  3710
(22 rows)

ネストされたグループ

GROUPING SETS/ROLLUP/CUBE オペレーションを GROUPING SETS expr として使用して、ネストされたグループを作成できます。ネストされた GROUPING SETS 内のサブグループはフラット化されます。

ネストされたグループの構文は次のとおりです。


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

次の例を考えます。


SELECT category, product, pre_owned,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;

       category       |       product        | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
 cellphones           |                      |           |        3 |  1610
 computers            |                      |           |        3 |  2100
                      | laptop               | F         |        4 |  1050
                      | laptop               | T         |        4 |  1000
                      | mouse                | F         |        4 |    50
                      | smartphone           | T         |        4 |  1610
                      | laptop               |           |        5 |  2050
                      | mouse                |           |        5 |    50
                      | smartphone           |           |        5 |  1610
                      |                      | F         |        6 |  1100
                      |                      | T         |        6 |  2610
                      |                      |           |        7 |  3710
                      |                      |           |        7 |  3710
(13 rows)

ROLLUP(category) と CUBE(product, pre_owned) の両方にグループ化セット () が含まれているため、総計を表す行が重複していることに注意してください。

使用に関する注意事項

GROUP BY 句は最大 64 のグループ化セットをサポートします。ROLLUP と CUBE、または GROUPING SETS、ROLLUP、および CUBE の組み合わせの場合、この制限はグループ化セットの暗黙の数に適用されます。例えば、GROUP BY CUBE((a), (b)) は、2 つではなく 4 つのグループ化セットとしてカウントされます。
集計拡張機能を使用する場合、定数をグループ化列として使用することはできません。
重複する列を含むグループ化セットを作成することはできません。

ブラウザで JavaScript が無効になっているか、使用できません。

AWS ドキュメントを使用するには、JavaScript を有効にする必要があります。手順については、使用するブラウザのヘルプページを参照してください。

ドキュメントの表記規則

GROUP BY 句

HAVING 句