背景・目的
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などは使用する機会がありそうと感じました。
参考