GROUP BY と GROUP BY ROLLUP

GROUP BY ROLLUP

Snowflakeにおいてgroup byには3つの派生(拡張機能)があるとのこと。今回はそのうちの一つであるgroup by rollupについて調査を行いました。そのほかの拡張機能はGROUP BY CUBE, GROUP BY GROUPING SETSです。

サンプルデータ

早速本題に入っていきたいと思います。
Snowflakeが示してくれている例を基に挙動を確認したいと思います。

サンプルデータ作成クエリ(公式ドキュメントをコピペしています)

create table products (product_id integer, wholesale_price real);
insert into products (product_id, wholesale_price) values 
    (1, 1.00),
    (2, 2.00);

products テーブル

PRODUCT_ID	WHOLESALE_PRICE
1	1
2	2

create table sales (product_id integer, retail_price real, 
    quantity integer, city varchar, state varchar);
insert into sales (product_id, retail_price, quantity, city, state) values 
    (1, 2.00,  1, 'SF', 'CA'),
    (1, 2.00,  2, 'SJ', 'CA'),
    (2, 5.00,  4, 'SF', 'CA'),
    (2, 5.00,  8, 'SJ', 'CA'),
    (2, 5.00, 16, 'Miami', 'FL'),
    (2, 5.00, 32, 'Orlando', 'FL'),
    (2, 5.00, 64, 'SJ', 'PR');

sales テーブル

PRODUCT_ID	RETAIL_PRICE	QUANTITY	CITY	STATE
1	2	1	SF	CA
1	2	2	SJ	CA
2	5	4	SF	CA
2	5	8	SJ	CA
2	5	16	Miami	FL
2	5	32	Orlando	FL
2	5	64	SJ	PR

SQL をたたいていく

いったんproductsテーブルとsalesテーブルを結合します。実行結果は以下になります。

テーブルを結合

PRODUCT_ID	WHOLESALE_PRICE	RETAIL_PRICE	QUANTITY	CITY	STATE
1	1	2	1	SF	CA
1	1	2	2	SJ	CA
2	2	5	4	SF	CA
2	2	5	8	SJ	CA
2	2	5	16	Miami	FL
2	2	5	32	Orlando	FL
2	2	5	64	SJ	PR

まずは group by

ではstate, city毎のprofitを計算するために group by の処理を行います。

 select
	state,
	city,
	sum((s.retail_price - p.wholesale_price) * s.quantity) as profit
 from
	 products as p
 left join
	 sales as s
 on
	 s.product_id = p.product_id
 group by
	 state, city
 order by
	 state, city;

STATE	CITY	PROFIT
CA	SF	13
CA	SJ	26
FL	Miami	48
FL	Orlando	96
PR	SJ	192

意図通り、state, city毎のprofitを出力することができました。

本題の group by rollup

同様にstate, city毎のprofitを計算しますが、今度は group by rollup の処理を行っていきます。

STATE	CITY	PROFIT
CA	SF	13
CA	SJ	26
CA	null	39
FL	Miami	48
FL	Orlando	96
FL	null	144
PR	SJ	192
PR	null	192
null	null	375

出力結果を見ると、group by の出力結果と比べると4レコード増加しています。出力結果を確認するとcityのみが null になっているレコードが確認できます。これはstate毎のprofitの合計を表現しています。例えば、stateが CA の地域において、各cityのprofitは13, 26 であり、それらの合計は39です。この合計と同じ結果が、3レコード目の(state, city) = ('CA', null)のprofitで出力されています。テーブル最後のレコードはstate 及び city カラムが null になっています。ここprofitで出力されている結果は'state' と 'city' の合計を示したものになります。

まとめ

group by rollupの挙動をまとめると、以下の結果になります。

column1	column2	count
A	X	A1
A	Y	A2
A	null	A1+A2
B	X	B1
B	Y	B2
B	Z	B3
B	null	B1+B2+B3
null	null	A1+A2+B1+B2+B3

感想

使う機会があるかはわかりませんが、使うときはササっと思い出せるように頭の片隅に置いておきます。

参考記事

• GROUP BY ROLLUP Snowflake docs