はじめに
インフラシステムグループの河野です。 最近集計・分析系のクエリを書く機会が多くなっています。その中で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) |
|
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 |
|
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 |
実務ではクラウドコストを集計してグラフ表示するときに非常に役に立ちました。クラウドベンダごとに別のグラフを描く。合計値も同時に描画したい。といった要件が出てきて、はじめは愚直に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 |
|
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 |
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 |
|
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)) |
|
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について使い方と、他の類似構文との関連性をまとめました。有効に使えるとクエリをすっきりとさせられるので機会があれば使ってみてください。参考記事
https://prestodb.io/docs/0.217/sql/select.htmlWe are hiring!
ニフティでは、さまざまなプロダクトへ挑戦するエンジニアを絶賛募集中です！ご興味のある方は以下の採用サイトよりお気軽にご連絡ください！ Tech TalkやMeetUpも開催しております！
こちらもお気軽にご応募ください！
