Zenn
📝

GROUP BY と GROUP BY ROLLUP

2023/01/10に公開
SQL
Snowflake
tech

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

感想

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

参考記事

Discussion