BLOG

2018-04-04 テクニカル

【SQLの神髄】第1回 GROUP BYとCASE式を使った行列変換

どうも、PostgreSQL推進委員会のアフロ・ナミヘイです。

今回はGROUP BYとCASE式を使った行列変換のテクニックをご紹介します。

 

開発の現場では、DB上では2行になっている(行持ち)データを、ある項目で集計して1行に変換(列持ち)したいケースがあると思います。
例えば、以下のようなケースです。

 

【半期別社員評価】

社員名 半期区分 評価点
川崎敏正 2018 上期 98
川崎敏正 2018 下期 2
左衛門三郎二郎 2018 上期 32
左衛門三郎二郎 2018 下期 34

 

ある会社で社員を半期ごとに評価し、その評価点を記録しているテーブルです。

浮き沈みの激しい川崎くんと、安定して低空飛行な左衛門三郎くんの半期別の評価を例にしてみます。

実際は社員名ではなく社員ID、半期区分もコード値で保持するといった構成が一般的ですが、わかりやすくするために上記のようなテーブル構成で実践します。

このテーブルから以下のように、社員ごとに上期・下期をまとめた年別の評価を出したいとします。

 

【社員+年別の評価】

社員名 上期評価点 下期評価点
川崎敏正 2018 98 2
左衛門三郎二郎 2018 32 34

 

このようなケースでありがちなのが、上期の評価点と下期の評価点を別々のSQLで取得するやり方です。

 

リスト1 上期と下期を別々にselectして結合

SELECT
 上期評価点.社員名, 上期評価点.年, 上期評価点.上期評価点, 下期評価点.下期評価点
FROM 
(SELECT 社員名, 年, 評価点 AS 上期評価点
 FROM 半期別社員評価
 WHERE 半期区分 = '上期'
) 上期評価点
INNER JOIN 
(SELECT 社員名, 年, 評価点 AS 下期評価点
 FROM 半期別社員評価
 WHERE 半期区分 = '下期'
) 下期評価点
ON 上期評価点.社員名 = 下期評価点.社員名

 

現場では往々にして、このように似たSQLでインラインビューを作って結合する方式が使われがちです。

このSQLでは当然ながら同じテーブルに対して2回SELECTを発行することになってしまいます。

これが2回ならまだ運用に耐えられる性能かもしれませんが、もしもっと条件が多い場合はどうでしょうか?

5個、10個といった数のSELECTを発行していけば、多くの現場でパフォーマンスに問題ありとなると思います。

ちなみに、この問題をGROUP BYとCASE式を使って解決するとこうなります。

 

リスト2 集計してCASE式で上期と下期を判別

SELECT
 社員名,
 年,
 MAX(CASE 半期区分 WHEN '上期' THEN 評価点 ELSE NULL END) AS 上期評価点,
 MAX(CASE 半期区分 WHEN '下期' THEN 評価点 ELSE NULL END) AS 下期評価点
FROM 
 半期別社員評価
GROUP BY 社員名, 年

 

リスト2 実行結果

社員名              |   年   | 上期評価点 | 下期評価点 
川崎敏正           | 2018 |            98 |          2 
左衛門三郎二郎  | 2018 |            32 |         34 

 

リスト1のSQLだと、WHERE句に「半期区分=上期」、「半期区分=下期」というように分岐するための条件を書くことになります。

しかし、リスト2ではCASE式を使いSELECT句で分岐しています。

半期区分が上期だったら上期評価点として、下期だったら下期評価点として集計しています。

これにより、テーブルに対するスキャンは1回で済みますので、母体が大きくなればリスト1との性能差は顕著に現れてきます。

 

それでは最後にSQLの世界におけるこんな格言をご紹介します。

 

『WHERE句で分岐させるのは素人、プロはSELECT句で分岐させる』

 

※文中の実行環境はPostgreSQL9.6を使用しています。

 

The following two tabs change content below.

アフロ・ナミヘイ

PostgreSQL推進委員長。アンチOracle派筆頭。 標準SQLに反する書式は許しません。 (+)とか使ってると激おこ。 アフロは天然。