Help us understand the problem. What is going on with this article?

カラムにnullが含まれるなら結果がnullになるmax()

More than 1 year has passed since last update.

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_datenullであるレコードを数え、それが0であるならmax(end_date)(そうでなければnull)」
と書いてあります。こちらも同仕様です。

Oracleユーザーならどちらを選びますか?

RDBのOSS移行も近い視野に入れているプロダクトであるなら可搬性から標準SQLのものを採用するしかありません。
そうでない場合。
一時的な問い合わせだったり、アプリコードの寿命のうちにRDB移行が視野に入らないような開発条件であるならOracle専用の前者を選びたいと私は考えます。

ここで強引めにエムスリーアドベントカレンダーというテーマに寄せていくのですが、私自身はコードレビュー文化のない職場だった前職からコードレビュー文化のあるエムスリーに来て、今さらながら“見てもらえるコード”をいかに書くか模索中です。

その目で二つの式を見比べてみます。

前者は、order by end_date nulls lastとあり、「nullはどんな日付よりも遅いのだ」という意図をそのまま書き下ろしています。

それに比べると後者は「nullが含まれなければmax()」という条件式ですから意図を理解するのにワンステップ思考を要求する上、その「nullが含まれなければ」も実際には「nullであるレコードを数え、それが0であるなら」と迂遠な書き方になるのでもうワンステップ思考を要求されます。

コードの意図を理解するための余計なステップをレビュアーにも将来の改修者にも踏ませたくないと考えると、使わずに済ませられる方言であっても使えるときには使いたいとなるのです。エムスリーに来るまではあまりしてこなかった発想だったかなと思っています。

yuba
全然わからない 俺たちは雰囲気でコードを書いている
http://cs.hatenablog.jp/
m3dev
インターネット、最新IT技術を活用し日本・世界の医療を改善することを目指します
https://m3.recruitment.jp/engineer/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした