どうも、PostgreSQL推進委員会のアフロ・ナミヘイです。
開発の現場でありがちなダメSQLを粛正し、同じ過ちが二度と繰り返されないことを願ってこのSQLの神髄シリーズを始めました。
第1回はGROUP BYとCASE式を使った行列変換のテクニックを紹介します。
課題 二郎の上期と下期の売上を1行にまとめて見たい
現場において、DB上では2行になっている（行持ち）データを、ある項目で集計して1行に変換（列持ち）したいケースがあると思います。
例えば、以下のようなケースです。
ラーメン二郎半期毎売上TBL
| 営業年 | 店舗名 | 半期区分 | 売上 |
|---|---|---|---|
| 2018 | 三田 | 上期 | 1穣 |
| 2018 | 三田 | 下期 | 2穣 |
| 2018 | 目黒 | 上期 | 1垓 |
| 2018 | 目黒 | 下期 | 2垓 |
| 2018 | 小岩 | 上期 | 1京 |
| 2018 | 小岩 | 下期 | 2京 |
こちらはラーメン二郎各店舗の売上を半期ごとに記録しているテーブルです。
2018年度の三田、目黒、小岩の売上データを登録した状態です。
このように半期や四半期別にデータを保持するケースが現場でもあると思います。
実際の現場では店舗名はIDで、半期区分もコード値で保持するといった設計が一般的ですが、わかりやすくするために上記のようなテーブル構成で説明していきます。
このテーブルから以下のように、二郎の上期・下期の売上を年別に1行にまとめた結果を取得したいとします。
二郎の上期・下期の売上を年別に1行にまとめた結果
| 営業年 | 店舗名 | 上期売上 | 下期売上 |
|---|---|---|---|
| 2018 | 三田 | 1穣 | 2穣 |
| 2018 | 目黒 | 1垓 | 2垓 |
| 2018 | 小岩 | 1京 | 2京 |
ダメなSQLの例
このような2行で保持されているデータを1行にまとめて取得したいケースでありがちなのが、上期の売上と下期の売上を別々のSELECT文で取得する方法です。
リスト1 二郎の上期売上と下期売上を別々にSELECTして結合（ギルティ）
SELECT
上期売上.営業年, 上期売上.店舗名, 上期売上.売上 AS 上期売上, 下期売上.売上 AS 下期売上
FROM
(SELECT
営業年, 店舗名, 売上
FROM
ラーメン二郎半期毎売上
WHERE
半期区分 = '上期'
) 上期売上
INNER JOIN
(SELECT
営業年, 店舗名, 売上
FROM
ラーメン二郎半期毎売上
WHERE
半期区分 = '下期'
) 下期売上
ON 上期売上.店舗名 = 下期売上.店舗名
現場では往々にして、このように似たSELECT文でインラインビューを作って結合する方式が使われがちです。
確かにこのSQLでも求めている結果は取得できます。
しかし、このSQLでは当然ながら同じテーブルに対して2回SELECT文を発行することになってしまいます。
以下実行計画を見ると、「Seq Scan on "ラーメン二郎半期毎売上"」が2回出ており、テーブルを2回スキャンしていることがわかります。
リスト1 実行計画
QUERY PLAN
Nested Loop (cost=0.00..35.64 rows=1 width=148)
Join Filter: (("ラーメン二郎半期毎売上"."店舗名")::text = ("ラーメン二郎半期毎売上_1"."店舗名")::text)
-> Seq Scan on "ラーメン二郎半期毎売上" (cost=0.00..17.75 rows=3 width=90)
Filter: (("半期区分")::text = '上期'::text)
-> Materialize (cost=0.00..17.77 rows=3 width=70)
-> Seq Scan on "ラーメン二郎半期毎売上" "ラーメン二郎半期毎売上_1" (cost=0.00..17.75 rows=3 width=70)
Filter: (("半期区分")::text = '下期'::text)
これが2回ならまだ運用に耐えられる性能かもしれませんが、もしもっと別々に取得する条件が多い場合はどうでしょうか？
4個、5個といった数のSELECTを発行していけば、現場でパフォーマンスに問題ありとなる可能性も高くなります。
このようなSQLは二郎に並んでいるときに横に広がって通行の妨げになるくらいギルティです。
そういった行為の苦情は店にいきます。最悪営業に差し障るかもしれません。
やめましょう。
GROUP BYとCASE式を使ったSQL
この問題を1つのSELECT文で解決するには、GROUP BYとCASE式を使います。
具体的には以下のSQLです。
リスト2 営業年と店舗名で集計してCASE式で上期と下期を判別
SELECT
営業年,
店舗名,
MAX(CASE 半期区分 WHEN '上期' THEN 売上 ELSE NULL END) AS 上期売上,
MAX(CASE 半期区分 WHEN '下期' THEN 売上 ELSE NULL END) AS 下期売上
FROM
ラーメン二郎半期毎売上
GROUP BY 営業年, 店舗名
リスト2 実行結果
| 営業年 | 店舗名 | 上期売上 | 下期売上 |
|---|---|---|---|
| 2018 | 三田 | 1穣 | 2穣 |
| 2018 | 目黒 | 1垓 | 2垓 |
| 2018 | 小岩 | 1京 | 2京 |
「GROUP BY 営業年, 店舗名」で営業年と店舗名でグルーピングし、各店舗の上期と下期のデータを1行にまとめています。
そしてCASE式で上期か下期かを判定して、それぞれの売上を上期売上、下期売上として振り分けています。
リスト1の醜く冗長なSQLだと、WHERE句に「半期区分＝上期」、「半期区分＝下期」というように分岐するための条件を書いていました。
しかし、リスト2ではCASE式を使いSELECT句でスマートに分岐しています。
これがすなわちSQLの世界における名言、
WHERE句で分岐させるのは素人、プロはSELECT句で分岐させる
ですね。
リスト2のSQLでは、ラーメン二郎半期毎売上TBLに対するスキャンは1回で済みます。前述の通りリスト1のSQLだと2回スキャンすることになります。
以下リスト2の実行計画を見ると、「Seq Scan on "ラーメン二郎半期毎売上"」が1回のみで、、テーブルのスキャンが1回で済んでいることがわかります。
母体が大きくなればなるほど、リスト1とリスト2との性能差は顕著に現れてきます（当然リスト2の方が早い）。
リスト2 実行計画
QUERY PLAN
HashAggregate (cost=25.50..27.50 rows=200 width=96)
Group Key: "営業年", "店舗名"
-> Seq Scan on "ラーメン二郎半期毎売上" (cost=0.00..16.20 rows=620 width=102)
まとめ
CASE式の存在は知っていても、どう活用すればいいかはまだまだ普及していないと思います。
スピードとスマートを実現するCASE式、是非活用されてください。
WHERE句で分岐させるのは素人、プロはSELECT句で分岐させる
んっん～～、名言だな、これは。
※同じく筆者が株式会社HomeGrowinコーポレートサイトにアップした以下記事を元に加筆・修正しています。
【SQLの神髄】第１回 GROUP BYとCASE式を使った行列変換
※文中のSQL実行環境はPostgreSQL9.6を使用しています。
※OracleやMySQLでも同様のSQLは実行可能です。