この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
データアナリティクス事業本部のコンサルティングチームの石川です。先月、AWS re:Invent 2022で、Redshiftが新しいSQL機能の拡張のサポートをお知らせしました。本日は、複雑な集計を1つのSQLで実行できる GROUPING SETS、ROLLUP、CUBEについてご紹介します。
※ 2023/02/28現在、これらの機能はGA（General Availability）になりました。
なお、以下のブログにて、MERGEステートメントについては試しています。
サポートされた GROUPING SETS、ROLLUP、CUBEは、GROUP BY の拡張です。GROUPING SETSは、GROUP BYとUNION ALL を使用して同じ結果を生成できます。また、ROLLUP、CUBEは、GROUPING SETSを用いて同じ結果を生成できます。
GROUPING SETS、ROLLUP、CUBEを新たにサポートすることによって、多次元分析アプリケーションの構築が簡素化されます。
これらのPreview新機能をお試しになるためには
Provisioned Clusterでお試しになるには、画面上部の[Create preview cluster]ボタンから作成画面に遷移します。
Prewiew trackは、preview_2022を選択します。
テストデータ
以降の検証では、売上データを保持するordersテーブルを使用しています。
dev=# CREATE TABLE ORDERS (
ID INT,
PRODUCT CHAR(20),
CATEGORY CHAR(20),
PRE_OWNED CHAR(1),
COST DECIMAL
);
CREATE TABLE
dev=# 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);
INSERT 0 5GROUPING SETS
1 つのステートメントで 1 つ以上の GROUPING SETS を計算します。GROUPING SETS は、単一の GROUP BY 句の集まりであり、クエリの結果セットをグループ化できる 0 個以上の列のセットです。
次の例では、productのcategoryと販売されたproductの種類の両方に従ってグループ化された ordersテーブルのproductのコストを返します。
実行例
dev=# 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)SUM関数以外の集計関数を組み合わせ可能
もちろん、SUM関数以外の集計関数を組み合わせ可能です。下記の例では、COUNT関数を用いて数量（quantity）を取得しています。
dev=# SELECT category, product, sum(cost) as total, count(*) as quantity
FROM orders
GROUP BY GROUPING SETS(category, product);
category | product | total | quantity
----------------------+----------------------+-------+----------
computers | | 2100 | 3
cellphones | | 1610 | 2
| laptop | 2050 | 2
| smartphone | 1610 | 2
| mouse | 50 | 1
(5 rows)GROUP BY と UNION ALLによるクエリの書き換え
GROUP BY GROUPING SETS は、異なる列でグループ化された1つの結果セットに対して UNION ALLクエリを実行するのと同じです。
dev=# SELECT category, null as product, sum(cost) as total
FROM orders
GROUP BY 1, 2
UNION ALL
SELECT null as category, product, sum(cost) as total
FROM orders
GROUP BY 1, 2
;
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 は、列をグループ化せずに結果セット全体を含む行も返します。
次の例では、最初にcategoryでグループ化され、次にproductでグループ化されたordersテーブルのproductのcostの集計値totalを、productをcategoryの下位区分として返します。
次の例では、categoryとproductの順にグループ化して、costの集計値total
ordersテーブルのproductのcostの集計値totalを、productをcategoryの下位区分として返します。
実行例
dev=# 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、smartphone）
cellphones | | 1610 <- 中計（cellphones）
computers | laptop | 2050 <- 小計（computers、laptop）
computers | mouse | 50 <- 小計（computers、mouse）
computers | | 2100 <- 中計（computers）
| | 3710 <- 総計
(6 rows)GROUPING SETSによる書き換え
GROUP BY ROLLUP((a), (b)) は、GROUP BY GROUPING SETS((a,b), (a), ()) に書き換え可能です。
dev=# SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS((1,2), (1), ()) 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))を使用して、最初にaでグループ化され、次にbでグループ化され、bがaのサブセクションであり、次にbのみでグループ化された結果セットを返すことができます。CUBEは、列をグループ化せずに結果セット全体を含む行も返します。
次の例では、最初にカテゴリでグループ化され、次に製品でグループ化された注文テーブルの製品のコストを、製品をカテゴリの下位区分として返します。前のROLLUPの例とは異なり、ステートメントはグループ化列のすべての組み合わせの結果を返します。
実行例
dev=# 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 SETSによる書き換え
GROUP BY CUBE((a), (b))は、GROUP BY GROUPING SETS((a, b), (a), (b), ())に書き換え可能です。
dev=# SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS((1, 2), (1), (2), ()) 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 SETS、ROLLUP、CUBEは、分析用途の複雑な集計ロジックを1つのSQLで取得するのに便利な関数です。ROLLUPは、小計や総計を取得するのに便利です。また、CUBEは、マルチディメンショナルな事前集計が可能です。GROUPING SETSを用いるとROLLUPやCUBEに書き換えることも可能であり、SUM以外の集計関数との組み合わせ次第ではより便利な集計も可能になります。
今回、CUBEについても紹介しましたが、Redshiftでは、マルチディメンショナルに検索が可能なInterleaved Sortkeyをテーブルに定義することができます。Interleaved Sortkeyは、指定したカラム（ディメンション）の指定した順に依存しない形でソートしますが事前集計はしていません。このあたり、CUBEとうまく使い分けていただくとマルチディメンショナルなデータ分析に対して効果的に利用していただけるのではないかと考えています。
13
