SQLのmax()関数は、対象カラムのnullでない値の最大値を集約値として返します。
ところが、対象にnullが含まれるならnullを結果にしたいという場面と少なからず出会います。
よくあるのは、有効期限。
- 「無期限」を
nullの有効期限として表現している - 期限レコードが複数ありそのうち最も遅い日時を取得したい
ありそうな要件です。もちろん「無期限」レコードが含まれているなら結果も「無期限」になってほしいので、表題通り「カラムにnullが含まれるなら結果がnullになるmax()」パターンそのものですね。
さてこの集約、一発で式が思い浮かぶでしょうか？
あれっ簡単かと思いきやややこしいぞ。
副問い合わせとかしないと無理？
心配ありません。副問い合わせなしでそんなに長ったらしくもならず書き下ろせます。
まずは二通り、Oracle専用の書き方と標準SQLだけによる書き方をご紹介します。対象カラムの名前はend_dateとします。
Oracle専用
Oracleはkeep構文により、指定ソート条件で先頭群になるレコードか末尾群になるレコードだけを集約関数の対象にできます。
max(end_date) keep (dense_rank last order by end_date nulls last)
この式は「nullが最後に来るようにend_dateで昇順ソートし、その末尾群についてのmax(end_date)」と書いてあります。求めていた通りの式ですね。
標準SQLのみで
標準SQLのみでもそれほどややこしい式にはなりません。
case when count(case when end_date is null then 1 end) = 0 then max(end_date) end
「end_dateがnullであるレコードを数え、それが0であるならmax(end_date)（そうでなければnull）」
と書いてあります。こちらも同仕様です。
Oracleユーザーならどちらを選びますか？
RDBのOSS移行も近い視野に入れているプロダクトであるなら可搬性から標準SQLのものを採用するしかありません。
そうでない場合。
一時的な問い合わせだったり、アプリコードの寿命のうちにRDB移行が視野に入らないような開発条件であるならOracle専用の前者を選びたいと私は考えます。
ここで強引めにエムスリーアドベントカレンダーというテーマに寄せていくのですが、私自身はコードレビュー文化のない職場だった前職からコードレビュー文化のあるエムスリーに来て、今さらながら“見てもらえるコード”をいかに書くか模索中です。
その目で二つの式を見比べてみます。
前者は、order by end_date nulls lastとあり、「nullはどんな日付よりも遅いのだ」という意図をそのまま書き下ろしています。
それに比べると後者は「nullが含まれなければmax()」という条件式ですから意図を理解するのにワンステップ思考を要求する上、その「nullが含まれなければ」も実際には「nullであるレコードを数え、それが0であるなら」と迂遠な書き方になるのでもうワンステップ思考を要求されます。
コードの意図を理解するための余計なステップをレビュアーにも将来の改修者にも踏ませたくないと考えると、使わずに済ませられる方言であっても使えるときには使いたいとなるのです。エムスリーに来るまではあまりしてこなかった発想だったかなと思っています。