【SQL】集約関数にCASE式で条件をつける

はじめに

このページは特殊なSQLを紹介しています。
SQLの基本をまとめたページもあるので、参考にしてみてください。

集約関数つまりMAXやMIN、SUMといった関数は、WHERE句やGROUP句で指定した条件で集約することが一般的で、MAX()のカッコ内はカラム名がそのまま入ると思います。
しかし、カッコ内はカラム名だけではなく式を入れることもできるんです！CASE式も使うことができるんです！！

集約関数内に式が入っているのをはじめてみたのは、人が書いたSQLでした。
そして、先日自分も使う機会があったので、ここにまとめようと思いました。

実装例

本ケースに対して、@D-Threeさんがより可読性のあるSQLをコメント欄に記載いただけました、状況に応じて使い分けてください
具体的な使用場面は後述しますが、とりあえず使い方はこんな感じです。
塾の特別講座の生徒IDと受講日を管理しているテーブル(STUDY_DAY)があるとします。

STUDY_DAY

ID	Date
1001	2020-03-01
1002	2020-03-01
1001	2020-03-07
1002	2020-03-10
1001	2020-03-19
1002	2020-03-19
1001	2020-03-23
1002	2020-03-27
1001	2020-04-03
1002	2020-04-09
1001	2020-04-17
1002	2020-04-17
1001	2020-04-22
1002	2020-04-22
1001	2020-04-26
1001	2020-05-04
1002	2020-05-04

このテーブルから各生徒の3月と4月の最終受講日を抽出したいとします。
最終受講日なのでMAX関数を使うことになりますが、3月と4月という条件をWHEN句だけで対応しようとすると、SQLを2回実行する必要があります。
しかし、MAX関数の中にCASE式を入れることでこれを1回で取得することができます。

MAX(CASE)

SELECT
    ID, 
    MAX(CASE WHEN [Date] <= '2020-04-01' THEN [Date] ELSE NULL END) AS 'March_LastDay',
    MAX(CASE WHEN [Date] <= '2020-05-01' THEN [Date] ELSE NULL END) AS 'April_LastDay'
FROM
    STUDY_DAY
GROUP BY
    ID

これによって取得できるレコードは以下の2レコードでID別に3,4月の最終受講日を取得することができます。

ID	March_LastDay	April_LastDay
1001	2020-03-23	2020-04-26
1002	2020-03-27	2020-04-22

となります。

解説

解説といってもたいそうなものではないです。技術的なものではないです。

`MAX(CASE WHEN [DATE] <= '2020-04-01' THEN [DATE] ELSE NULL END)'
このMAX関数がどんな動きをしているかというと、

1. CASE式により'2020-04-01'以降の日付は全て'1'(システム上の最過去日)に変換
1. CASE式により'2020-04-01'以降の日付は全てNULLに変換
2. IDで集約して最大値を取得

となります。
'2020-04-01'以降のデータがMAX関数にヒットすることはなくなるので、結果として3月の最終受講日を取得することができるというわけです。
(書いていて思いましたが、3月に受講履歴が無いと3月以前のデータが取れてしまうので、もう少し工夫が必要ですね。。。)

用途例

私がこれを使ったのは、パフォーマンスの課題にぶつかったためです。
システム開発をしているとよくありますよね、機能は実装できたけどクソ重くて使い物にならない的なやつ。。

上記の月別最終受講日を取得しようとしたときに、単純なSQLでやろうとすると何度もDBサーバーにアクセスするか、テーブル全件をがっつりとってきてアプリでループを回して取得するなど、処理回数も多くなるのでパフォーマンスに難を抱え、プログラム量が多くなることでバグのリスクも高まります。

そんな課題にぶつかった時にこの方法に出会いました。
1つのSQLで取得することができるんです。DBへのアクセス回数も減らせますし、アプリでの処理もシンプルにすることができました。