背景・目的

RedshiftでGroup Bｙ句で下記の拡張クエリがGAしましたので、試してみました。

ROLLUP
CUBE
GROUPING SETS

実践

事前準備

こちらを参考に検証用のテーブルとデータを準備します。

データベースを作成します。

create database sandbox;

スキーマを作成します。

CREATE SCHEMA groupby_extensions

テーブルを作成します。

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

データを登録します。

INSERT INTO groupby_extensions.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

異なるグループ化列を指定して、それぞれを集計し、一つの表にまとめます。
group by grouping sets(A,B)は、group by a union all group by bの結果と同等です。

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

~~~~~

category, product, total
===
computers           	NULL	2100	
cellphones          	NULL	1610	
NULL	laptop              	2050	
NULL	smartphone          	1610	
NULL	mouse               	50

ROLLUP

小計、合計のようにまとめる場合に使用します。

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

~~~~~

category, product, total
===

cellphones          	smartphone          	1610	
cellphones          	NULL	1610	
computers           	laptop              	2050	
computers           	mouse               	50	
computers           	NULL	2100	
NULL	NULL	3710

CUBE

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

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

~~~~~

category, product, total
===

cellphones          	smartphone          	1610	
cellphones          	NULL	1610	
computers           	laptop              	2050	
computers           	mouse               	50	
computers           	NULL	2100	
NULL	laptop              	2050	
NULL	mouse               	50	
NULL	smartphone          	1610	
NULL	NULL	3710

GROUPING/GROUPING_ID functions

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

GROUPING関数と、GRUPING IDを使用して、Rollupまたは、Cubeによって作成されたNULLと元のテーブル自体に格納されているNULLを区別できる。
GROUPINGでは、引数に1つのグループ化セットを受け取り、その結果をその位置のグループ化列に対応する 0 または 1 のビット値を返し、その値を整数に変換する。
結果の1は、集計拡張機能によって返されたものを表す。他のすべての値に対しては0を返す。

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

~~~

category product grouping0 grouping1 grouping2 total
===

cellphones          	smartphone          	0	0	0	1610	
cellphones          	NULL	0	1	1	1610	
computers           	laptop              	0	0	0	2050	
computers           	mouse               	0	0	0	50	
computers           	NULL	0	1	1	2100	
NULL	laptop              	1	0	2	2050	
NULL	mouse               	1	0	2	50	
NULL	smartphone          	1	0	2	1610	
NULL	NULL	1	1	3	3710

Partial ROLLUP and CUBE

小計の一部のみを使用して ROLLUP および CUBE 操作を実行できます。
下記の例では、最初に製品が中古品であるかどうかによってグループ化し、次にカテゴリおよび製品列に対して ROLLUP および CUBE を実行している。

ROLLUPの例

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM groupby_extensions.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           	NULL	2	1100	
T	cellphones          	NULL	2	1610	
T	computers           	NULL	2	1000	
F	NULL	NULL	6	1100	
T	NULL	NULL	6	2610

CUBEの例

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM groupby_extensions.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           	NULL	2	1100	
T	cellphones          	NULL	2	1610	
T	computers           	NULL	2	1000	
F	NULL	laptop              	4	1050	
F	NULL	mouse               	4	50	
T	NULL	laptop              	4	1000	
T	NULL	smartphone          	4	1610	
F	NULL	NULL	6	1100	
T	NULL	NULL	6	2610

Concatenated grouping

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

SELECT pre_owned, category, product,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM groupby_extensions.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	
NULL	cellphones          	smartphone          	1	1610	
NULL	computers           	laptop              	1	2050	
NULL	computers           	mouse               	1	50	
F	computers           	NULL	2	1100	
T	cellphones          	NULL	2	1610	
T	computers           	NULL	2	1000	
NULL	cellphones          	NULL	3	1610	
NULL	computers           	NULL	3	2100	
F	NULL	laptop              	4	1050	
F	NULL	mouse               	4	50	
T	NULL	laptop              	4	1000	
T	NULL	smartphone          	4	1610	
NULL	NULL	laptop              	5	2050	
NULL	NULL	mouse               	5	50	
NULL	NULL	smartphone          	5	1610	
F	NULL	NULL	6	1100	
T	NULL	NULL	6	2610	
NULL	NULL	NULL	7	3710

Nested grouping

GROUPING SETS/ROLLUP/CUBE 操作を GROUPING SETS expr として使用して、ネストされたグループ化を形成できる。ネストされた GROUPING SETS 内のサブグループはフラット化される。

SELECT category, product, pre_owned,
       GROUPING(category, product, pre_owned) as group_id,
       sum(cost) as total
FROM groupby_extensions.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          	NULL	NULL	3	1610	
computers           	NULL	NULL	3	2100	
NULL	laptop              	F	4	1050	
NULL	laptop              	T	4	1000	
NULL	mouse               	F	4	50	
NULL	smartphone          	T	4	1610	
NULL	laptop              	NULL	5	2050	
NULL	mouse               	NULL	5	50	
NULL	smartphone          	NULL	5	1610	
NULL	NULL	F	6	1100	
NULL	NULL	T	6	2610	
NULL	NULL	NULL	7	3710	
NULL	NULL	NULL	7	3710

考察

集計拡張のクエリについて、ドキュメントに沿って実行してみました。比較的に、Rollupなどは使用する機会がありそうと感じました。

参考