はじめに
ログラスの龍島（りゅうしま）です。今回はSQLのちょっとニッチな機能の紹介と簡単な性能検証をしてみたいと思います。紹介する機能はgrouping setsです。主にPostgreSQLを念頭に話します。
この記事でわかること
- grouping setsとはどういう機能なのか
- どのような場面で使うのか
- 性能面でどういった性質があるのか
grouping setsとは
SQL99で規定されているgroup byに関連する機能で、簡単に言うと「複数の組み合わせのgroup byを一度に実行できる」というものです。
PostgreSQLの公式ドキュメントとしては下記ですが、正直パッと理解しにくいと思いますので簡単な例を見てみましょう。
| 年月 | 大エリア | 小エリア | 商品 | 売上高 |
|---|---|---|---|---|
| 202310 | 北海道・東北 | 北海道 | りんご | 100 |
| 202310 | 北海道・東北 | 北海道 | なし | 200 |
| 202310 | 関東 | 東京 | なし | 500 |
| 202310 | 関東 | 千葉 | なし | 300 |
| 202311 | 北海道・東北 | 北海道 | りんご | 100 |
| 202311 | 北海道・東北 | 北海道 | なし | 300 |
| 202311 | 関東 | 東京 | なし | 400 |
| 202311 | 関東 | 千葉 | なし | 200 |
このデータに対して、年月ごとの全国、大エリア、小エリアの集計を出そうとするとどのようなクエリにすると良いでしょうか？つまり求めるアウトプットは下記のようなものです。
| 年月 | 大エリア | 小エリア | 合計売上高 |
|---|---|---|---|
| 202310 | 1100 | ||
| 202310 | 北海道・東北 | 300 | |
| 202310 | 北海道・東北 | 北海道 | 300 |
| 202310 | 関東 | 800 | |
| 202310 | 関東 | 千葉 | 300 |
| 202310 | 関東 | 東京 | 500 |
| 202311 | 1000 | ||
| 202311 | 北海道・東北 | 400 | |
| 202311 | 北海道・東北 | 北海道 | 400 |
| 202311 | 関東 | 600 | |
| 202311 | 関東 | 千葉 | 200 |
| 202311 | 関東 | 東京 | 400 |
「年月と大エリアと小エリア」（=小エリアの集計）、「年月と大エリア」(=大エリアの集計)、「年月」(=全国の集計)の3つの組み合わせでのgroup byが必要になります。単純にgroup byで表現すると下記のようになります。
-- 大エリア + 小エリアの集計
SELECT "年月", "大エリア", "小エリア", SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY "年月", "大エリア", "小エリア"
UNION ALL
-- 大エリアの集計
SELECT "年月", "大エリア", NULL AS "小エリア", SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY "年月", "大エリア"
-- 全国の集計
UNION ALL
SELECT "年月", NULL AS "大エリア", NULL AS "小エリア", SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY "年月"
ORDER BY "年月", "大エリア", "小エリア" nulls first;
grouping setsを使うとこれは下記のように書き換えられます。
SELECT "年月", "大エリア", "小エリア", SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY GROUPING SETS ( ("年月", "大エリア", "小エリア"),
("年月", "大エリア"),
("年月")
)
ORDER BY "年月", "大エリア", "小エリア" NULLS FIRST;
かなりスッキリしましたね。このような複雑な集計処理を書けるのがgrouping setsです。
grouping関数
grouping setsを利用しようと各種ドキュメントを読んでいる際のつまづきポイントとして、grouping関数があります。少し解説しようと思います。
先程のデータを少し変更して、下記のようなデータだったとします。
| 年月 | 大エリア | 小エリア | 商品 | 売上高 |
|---|---|---|---|---|
| 202310 | 北海道 | りんご | 100 | |
| 202310 | 北海道 | なし | 200 | |
| 202310 | 関東 | 東京 | なし | 500 |
| 202310 | 関東 | 千葉 | なし | 300 |
| 202311 | 北海道 | りんご | 100 | |
| 202311 | 北海道 | なし | 300 | |
| 202311 | 関東 | 東京 | なし | 400 |
| 202311 | 関東 | 千葉 | なし | 200 |
北海道の階層が変更され、大エリアに北海道、小エリアは値を持たなくなりました。このデータに対して先程のSQLを実行すると下記のようになります。
| 年月 | 大エリア | 小エリア | 合計売上高 |
|---|---|---|---|
| 202310 | 北海道 | 300 | |
| 202310 | 北海道 | 300 | |
| 202310 | 関東 | 800 | |
| 202310 | 関東 | 千葉 | 300 |
| 202310 | 関東 | 東京 | 500 |
| 202310 | 1100 | ||
| 202311 | 北海道 | 400 | |
| 202311 | 北海道 | 400 | |
| 202311 | 関東 | 600 | |
| 202311 | 関東 | 千葉 | 200 |
| 202311 | 関東 | 東京 | 400 |
| 202311 | 1000 |
北海道の行が重複して表示されるようになりました。これは
- 「年月、大エリア」で集約した際のレコード
- 「年月、大エリア、小エリア」で集約した際の小エリアがnullのレコード
が両方出てしまっていることによるものです。これを区別するのにgrouping関数が利用できます。
小エリアに対してgrouping関数の結果も併せて出力してみます。
SELECT "年月",
"大エリア",
"小エリア",
grouping("小エリア") as 小エリアgrouping,
SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY GROUPING SETS ( ("年月", "大エリア", "小エリア"),
("年月", "大エリア"),
("年月")
)
ORDER BY "年月", "大エリア", "小エリア" NULLS FIRST;
| 年月 | 大エリア | 小エリア | 小エリアgrouping | 合計売上高 |
|---|---|---|---|---|
| 202310 | 北海道 | 0 | 300 | |
| 202310 | 北海道 | 1 | 300 | |
| 202310 | 関東 | 1 | 800 | |
| 202310 | 関東 | 千葉 | 0 | 300 |
| 202310 | 関東 | 東京 | 0 | 500 |
| 202310 | 1 | 1100 | ||
| 202311 | 北海道 | 0 | 400 | |
| 202311 | 北海道 | 1 | 400 | |
| 202311 | 関東 | 1 | 600 | |
| 202311 | 関東 | 千葉 | 0 | 200 |
| 202311 | 関東 | 東京 | 0 | 400 |
| 202311 | 1 | 1000 |
grouping関数は引数の式が集約対象の場合1が立ちます。つまり1が「年月、大エリア」で集約した際のレコード、0が「年月、大エリア、小エリア」で集約した際の小エリアがnullのレコードというわけです。
どちらか不要な場合はhaving句で除いておくと良いです。
SELECT "年月",
"大エリア",
"小エリア",
grouping("小エリア") as 小エリアgrouping,
SUM("売上高") AS "合計売上高"
FROM sales_table
GROUP BY GROUPING SETS ( ("年月", "大エリア", "小エリア"),
("年月", "大エリア"),
("年月")
)
having "小エリア" is not null or grouping("小エリア") = 1
ORDER BY "年月", "大エリア", "小エリア" NULLS FIRST;
使い所
複数のカラムにまたがった値を集計して同列で扱いたい場合に使うことが多いです。具体的には上記の例のエリア情報のように、階層構造がカラムで分かれて表現されているようなデータがイメージしやすいです。
Loglassにおいてもレポート機能においてまさに階層化されたデータを集計するために利用しています。
grouping setsのパフォーマンス
grouping setsはgroup by + union allと比較してシンプルに記述できるだけでなく、パフォーマンス面でも有利と考えられます。なぜならgroup by + union allの手法はgroup byの回数分対象のデータセットをスキャンする必要があるのに対し、grouping setsでは一度のスキャンで済むためです。
実際に実行時間や実行計画を比較してみます。
実行したSQLや検証のスクリプトを記載していますが、結果のみ興味のある方は読み飛ばしていただければと思います。
データセットの用意
前例と似たような、年月とエリア(1~5)ごとに売上高を持っているテーブルで1,000万件のテストデータを作成します。
テストデータセット作成SQL
-- テストテーブルの作成
DROP TABLE IF EXISTS test_data;
-- テーブルを作成
CREATE TABLE test_data (
id serial PRIMARY KEY,
year_month INT,
area_1 INT,
area_2 INT,
area_3 INT,
area_4 INT,
area_5 INT,
revenue INT
);
-- ランダムなテストデータを挿入
INSERT INTO test_data (year_month, area_1, area_2, area_3, area_4, area_5, revenue)
SELECT
floor(random() * (202312 - 202301 + 1) + 202301)::INT, -- year_month
floor(random() * 10 + 1)::INT, -- area_1
floor(random() * 10 + 1)::INT, -- area_2
floor(random() * 10 + 1)::INT, -- area_3
floor(random() * 10 + 1)::INT, -- area_4
floor(random() * 10 + 1)::INT, -- area_5
floor(random() * 1000 + 1)::INT -- revenue
FROM generate_series(1, 10000000);
ANALYSE test_data;
union all + group byとgrouping setsの比較
検証環境PostgreSQL 15.2 on MacBookPro M1Proにて各SQLを10回実行し平均で比較してみます。
union all + group by SQL
-- year_month = 202310 と area_1 ~ area_5 で集計
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4, area_5
UNION ALL
-- 同様に area_1 ~ area_4 で集計
SELECT area_1, area_2, area_3, area_4, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4
UNION ALL
-- 同様に area_1 ~ area_3 で集計
SELECT area_1, area_2, area_3, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3
UNION ALL
-- 同様に area_1 ~ area_2 で集計
SELECT area_1, area_2, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2
UNION ALL
-- 同様に area_1 で集計
SELECT area_1, NULL, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1
ORDER BY area_1, area_2, area_3, area_4, area_5;
grouping sets SQL
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY GROUPING SETS ( (area_1, area_2, area_3, area_4, area_5),
(area_1, area_2, area_3, area_4),
(area_1, area_2, area_3),
(area_1, area_2),
(area_1)
)
ORDER BY area_1, area_2, area_3, area_4, area_5;
比較スクリプト
import psycopg2
import time
# PostgreSQLへの接続情報
conn = psycopg2.connect(
dbname="your_db_name",
user="your_user_name",
password="your_password",
host="your_host",
port="your_port"
)
cur = conn.cursor()
# 処理時間を保存するリスト
union_all_times = []
grouping_sets_times = []
# UNION ALL クエリ
union_all_query = '''
-- year_month = 202310 と area_1 ~ area_5 で集計
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4, area_5
UNION ALL
-- 同様に area_1 ~ area_4 で集計
SELECT area_1, area_2, area_3, area_4, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4
UNION ALL
-- 同様に area_1 ~ area_3 で集計
SELECT area_1, area_2, area_3, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3
UNION ALL
-- 同様に area_1 ~ area_2 で集計
SELECT area_1, area_2, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1, area_2
UNION ALL
-- 同様に area_1 で集計
SELECT area_1, NULL, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY area_1
ORDER BY area_1, area_2, area_3, area_4, area_5;
'''
# GROUPING SETS クエリ
grouping_sets_query = '''
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data
WHERE year_month = 202310
GROUP BY GROUPING SETS (
(area_1, area_2, area_3, area_4, area_5),
(area_1, area_2, area_3, area_4),
(area_1, area_2, area_3),
(area_1, area_2),
(area_1)
)
ORDER BY area_1, area_2, area_3, area_4, area_5;
'''
print("UNION ALL Query Times:")
for i in range(1, 11):
# 予めバッファに乗せてキャッシュの影響を少なくする
cur.execute("SELECT pg_prewarm('test_data');")
# UNION ALL
start_time = time.time()
cur.execute(union_all_query)
end_time = time.time()
elapsed_time = end_time - start_time
union_all_times.append(elapsed_time)
print(f" Run {i}: {elapsed_time}")
# 平均の計算
union_all_avg = sum(union_all_times) / len(union_all_times)
print(f" Average Time: {union_all_avg}")
print("\nGROUPING SETS Query Times:")
for i in range(1, 11):
# 予めバッファに乗せてキャッシュの影響を少なくする
cur.execute("SELECT pg_prewarm('test_data');")
# GROUPING SETS
start_time = time.time()
cur.execute(grouping_sets_query)
end_time = time.time()
elapsed_time = end_time - start_time
grouping_sets_times.append(elapsed_time)
print(f" Run {i}: {elapsed_time}")
# 平均の計算
grouping_sets_avg = sum(grouping_sets_times) / len(grouping_sets_times)
print(f" Average Time: {grouping_sets_avg}")
cur.close()
conn.close()
結果
UNION ALL Query Times:
Run 1: 1.499704122543335
Run 2: 1.4252417087554932
Run 3: 1.4035181999206543
Run 4: 1.4195802211761475
Run 5: 1.4169158935546875
Run 6: 1.4118247032165527
Run 7: 1.4271800518035889
Run 8: 1.4248430728912354
Run 9: 1.4343249797821045
Run 10: 1.4205358028411865
Average Time: 1.4283668756484986
GROUPING SETS Query Times:
Run 1: 1.2942430973052979
Run 2: 1.2704789638519287
Run 3: 1.2706820964813232
Run 4: 1.2818210124969482
Run 5: 1.3167130947113037
Run 6: 1.2699871063232422
Run 7: 1.2786409854888916
Run 8: 1.2685649394989014
Run 9: 1.2740168571472168
Run 10: 1.2605938911437988
Average Time: 1.2785742044448853
予想通りunion allが遅い結果となりましたが、平均で1.43s, 1.29sと自分の予想よりは大きな差となりませんでした。実行計画を見てもう少し深掘りしてみます。
union allの実行計画
pgMustardにて実行計画をビジュアライズしてみます。リンク先が見やすいです。
全体としてクエリの見た目通り5回scan + aggregateを行いそれらをマージしている形になっています。
１度のscan + aggregateはばらつきがありますが、scanが337ms~836ms, aggregateが84ms~382ms程度です。各ノードの実行時間を足すと総実行時間を超えてしまいますが、GatherMergeなのでパラレルクエリで並列化されて実行されているためです。今回は3並列となっていました。
grouping setsの実行計画
シンプルに一度scan(444ms) + aggregate(845ms)となりました。scanはunion all方式の一回と同程度、aggregateはunion all方式の5回分を足した値(874ms)と同程度です。
上記からunion all方式はscanが多い分遅くなるが、並列実行することでその遅さを一定巻き返せていると言えます。
並列数を増やせればaggregate部分を並列化できる分union all方式が優勢となることも考えられますが、
- 並列数は設定で上げることが可能だが、恩恵を受けられる上限はマシンのコア数に依存する
- 並列化するためのオーバーヘッドも一定ある
ということから現実的にはgrouping setsの方が多くの場合で有利と考えられます。
scanにより時間がかかる場合の比較
集計処理を行う場合に複数のテーブルをjoinしてgroup byすることなどは一般によくあるケースだと思います。group byする対象のレコードを取得する処理により時間がかかる場合、上記結果を踏まえるとより差が開くことが予想されます。
一応検証してみます。今回は簡単のため、pg_sleep関数を使って擬似的にgroup by対象レコードの取得処理を遅延させてみます。データ取得ごとに1秒遅延します。
union allのSQL(集計対象取得遅延ver)
-- year_month = 202310 と area_1 ~ area_5 で集計
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data,
(select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4, area_5
UNION ALL
-- 同様に area_1 ~ area_4 で集計
SELECT area_1, area_2, area_3, area_4, NULL, SUM(revenue) AS total_revenue
FROM test_data,
(select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3, area_4
UNION ALL
-- 同様に area_1 ~ area_3 で集計
SELECT area_1, area_2, area_3, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data,
(select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY area_1, area_2, area_3
UNION ALL
-- 同様に area_1 ~ area_2 で集計
SELECT area_1, area_2, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data,
(select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY area_1, area_2
UNION ALL
-- 同様に area_1 で集計
SELECT area_1, NULL, NULL, NULL, NULL, SUM(revenue) AS total_revenue
FROM test_data,
(select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY area_1
ORDER BY area_1, area_2, area_3, area_4, area_5;
grouping setsのSQL(集計対象取得遅延ver)
SELECT area_1, area_2, area_3, area_4, area_5, SUM(revenue) AS total_revenue
FROM test_data
, (select pg_sleep(1)) t
WHERE year_month = 202310
GROUP BY GROUPING SETS ( (area_1, area_2, area_3, area_4, area_5),
(area_1, area_2, area_3, area_4),
(area_1, area_2, area_3),
(area_1, area_2),
(area_1)
)
ORDER BY area_1, area_2, area_3, area_4, area_5;
結果
UNION ALL Query Times:
Run 1: 8.361353874206543
Run 2: 7.820831060409546
Run 3: 7.780340909957886
Run 4: 7.77163290977478
Run 5: 7.79163122177124
Run 6: 7.7224767208099365
Run 7: 7.766075849533081
Run 8: 7.764142036437988
Run 9: 7.7846808433532715
Run 10: 7.722808122634888
Average Time: 7.828597354888916
GROUPING SETS Query Times:
Run 1: 2.3444080352783203
Run 2: 2.3388819694519043
Run 3: 2.335810899734497
Run 4: 2.391515016555786
Run 5: 2.3471269607543945
Run 6: 2.336261749267578
Run 7: 2.3436548709869385
Run 8: 2.334725856781006
Run 9: 2.3339650630950928
Run 10: 2.3504409790039062
Average Time: 2.3456791400909425
差が開くことが確認できました。
検証まとめ
予想通りパフォーマンス面でもgrouping setsが有利ということがわかりました。union all方式は並列化できるため一定巻き返せてはいますが、並列化には上限があるため多くの場合でgrouping setsが有利です。
また結果から集計対象データの取得処理が重たい程grouping setsが有利になると言えます。また今回検証はしていませんが、union all方式はgroup byするパターンが増えるほど掛け算で計算量が増えるため、パターンが多い程grouping setsが有利であるとも言えそうです。
株式会社ログラスは「良い景気を作ろう。」をミッションに、企業経営を推進する次世代型経営管理クラウド「Loglass 経営管理」を開発し提供しています。
Discussion