はじめに
インフラシステムグループの河野です。
最近集計・分析系のクエリを書く機会が多くなっています。
その中でGROUPING SETSに出会って感動したのでこの気持を分かち合いたいと思います。
記事中ではクエリエンジンとしてpresto 0.217を使用しています。
GROUPING SETSとは
GROUPING SETSはGROUP BY句に付与する構文で、複雑なGROUP BYを実現するときに使用できます。
具体例を見ていきましょう。例えば、以下のようなstockテーブルを考えます。店舗ごとにフルーツの在庫がいくつあるか、値段はいくらなのかをまとめたテーブルです。
|
1 2 3 4 5 6 7 8 9 |
SELECT * FROM ( VALUES ('orange', 1, 100, 51), ('lemon', 2, 50, 102), ('melon', 3, 1025, 23), ('banana', 1, 25, 154), ('orange', 2, 104, 105), ('lemon', 3, 55, 55) ) AS t (name, shop_id, price, qty) |
商品ごとの合計を出しつつ、全体の合計も出したいときが来たとします。この場合GROUPING SETSを使わないとすると以下のようなクエリで実現できます。
|
1 2 3 4 5 6 7 8 |
SELECT name, SUM(price) AS total_price FROM stock GROUP BY name UNION ALL SELECT NULL, SUM(price) AS total_price FROM stock |
|
1 2 3 4 5 6 7 |
# 実行結果 name total_price 1359 banana 25 orange 204 melon 1025 lemon 105 |
これをGROUPING SETSで書き換えると以下のようになります。
|
1 2 3 |
SELECT name, SUM(price) AS total_price FROM stock GROUP BY GROUPING SETS((), name) |
|
1 2 3 4 5 6 7 |
# 実行結果 name total_price 1359 lemon 105 melon 1025 orange 204 banana 25 |
このように複数の基準でGROUP BYしなければならないものを、GROUPING SETSで一つにまとめることができます。
実務ではクラウドコストを集計してグラフ表示するときに非常に役に立ちました。クラウドベンダごとに別のグラフを描く。合計値も同時に描画したい。といった要件が出てきて、はじめは愚直にUNIONしていて、30行程度のクエリになっていました。その後prestoのリファレンスを見ていたらGROUPING SETSを見つけたので試しに書き換えてみたら、15行ほどにすっきりとクエリを書き替えることができました。
ROLLUP、CUBEとの関係性
GROUPING SETSと似た構文にROLLUPとCUBEがあります。ROLLUPでは小計、総計をまとめて取得することができます。CUBEではすべての組み合わせに対して総計を取得することができます。
それぞれ先程のstockテーブルに対して適用した結果を見てみましょう。
ROLLUP:
|
1 2 3 |
SELECT name, shop_id, SUM(price) AS price, SUM(qty) AS qty FROM stock GROUP BY ROLLUP(name, shop_id) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 実行結果 name shop_id price qty 1359 490 banana 25 154 orange 204 156 orange 1 100 51 lemon 3 55 55 melon 1025 23 lemon 2 50 102 melon 3 1025 23 lemon 105 157 banana 1 25 154 orange 2 104 105 |
CUBE:
|
1 2 3 |
SELECT name, shop_id, SUM(price) AS price, SUM(qty) AS qty FROM stock GROUP BY CUBE(name, shop_id) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# 実行結果 name shop_id price qty 1359 490 banana 25 154 2 154 207 orange 1 100 51 melon 3 1025 23 lemon 3 55 55 melon 1025 23 1 125 205 orange 2 104 105 orange 204 156 lemon 2 50 102 banana 1 25 154 lemon 105 157 3 1080 78 |
これらをGROUPING SETSで書き換えると以下のようになります。
ROLLUP → GROUPING SETS:
|
1 2 3 |
SELECT name, shop_id, SUM(price) AS price, SUM(qty) AS qty FROM stock GROUP BY GROUPING SETS((), name, (name, shop_id)) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 実行結果 name shop_id price qty 1359 490 banana 25 154 melon 1025 23 lemon 2 50 102 melon 3 1025 23 lemon 105 157 banana 1 25 154 orange 2 104 105 orange 204 156 orange 1 100 51 lemon 3 55 55 |
CUBE → GROUPING SETS:
|
1 2 3 |
SELECT name, shop_id, SUM(price) AS price, SUM(qty) AS qty FROM stock GROUP BY GROUPING SETS((), name, shop_id, (name, shop_id)) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
# 実行結果 name shop_id price qty 1359 490 banana 25 154 2 154 207 orange 1 100 51 melon 3 1025 23 lemon 3 55 55 lemon 105 157 3 1080 78 orange 204 156 lemon 2 50 102 banana 1 25 154 melon 1025 23 1 125 205 orange 2 104 105 |
それぞれの差分だけ見るとどういう動きをしているかわかりやすいと思います。
|
1 2 |
ROLLUP(name, shop_id) → GROUPING SETS((), name, (name, shop_id)) CUBE(name, shop_id) → GROUPING SETS((), name, shop_id, (name, shop_id)) |
例えば、組み合わせが一個増えたときの対応はこうなります。ROLLUPでは一番左のカラムを基準としたときの組み合わせ、CUBEではすべての組み合わせをそれぞれグルーピングしています。
|
1 2 |
ROLLUP(name, shop_id, qty) → GROUPING SETS((), name, (name, shop_id), (name, shop_id, qty)) CUBE(name, shop_id, qty) → GROUPING SETS((), name, shop_id, qty, (name, shop_id), (name, qty), (shop_id, qty), (name, shop_id, qty)) |
GROUPING SETSで書くことで、どの組み合わせで集計しているのかがわかりやすくなるため、個人的にこの書き方が好みです。
終わりに
今回はGROUPING SETSについて使い方と、他の類似構文との関連性をまとめました。有効に使えるとクエリをすっきりとさせられるので機会があれば使ってみてください。
