【Redshift】Group by 句のAggregation extentionsを試してみた

# 背景・目的
RedshiftでGroup Bｙ句で下記の拡張クエリがGAしましたので、試してみました。
- ROLLUP
- CUBE
- GROUPING SETS

# 実践
## 事前準備
[こちら](https://docs.aws.amazon.com/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html)を参考に検証用のテーブルとデータを準備します。

1. データベースを作成します。
```
create database sandbox;
```

1. スキーマを作成します。
```
CREATE SCHEMA groupby_extensions
```

1. テーブルを作成します。
```
CREATE TABLE groupby_extensions.ORDERS (
    ID INT,
    PRODUCT CHAR(20),
    CATEGORY CHAR(20),
    PRE_OWNED CHAR(1),
    COST DECIMAL
);
```

1. データを登録します。
```
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などは使用する機会がありそうと感じました。

# 参考
https://aws.amazon.com/about-aws/whats-new/2023/02/amazon-redshift-rollup-cube-grouping-sets-group-by-clause/

https://docs.aws.amazon.com/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html