こんにちは。開発ブログ運営担当のktです。

現在担当している案件で、「ん？どうすれば解決できるだろう？」とちょっと詰まったことがあったので、解決した方法のメモです。

ちょっとしたテクニックですが、知っている人だったらすぐに思いつく方法だと思います。

前提

従業員の所属を保持するテーブルがあって、その所属がいつから有効になるかを意味する「有効日」という項目を持っています。

[所属テーブル]

従業員NO	有効日	所属
77777	2016/04/01	営業部
77777	2017/01/01	開発部
77777	2017/03/01	総務部

「77777」さんは2016/04/01から営業部に所属していますが、2017/01/01から開発部に異動し、2017/03/01からは総務部に異動したことを意味しています。

やりたかったこと

ある日付時点の所属を取得したい。

例えば、2017/02/01時点の所属をSQLで取得するにはどのようにすればいいでしょう？

2017/02/01以前の有効日を条件にした下記のSQLで取得できるでしょうか？

SELECT
　　所属
FROM
　　所属テーブル
WHERE
　　従業員NO = ‘77777’
AND 有効日 <= ‘2017/02/01’

このSQLだと2016/04/01の営業部と2017/01/01の開発部の2件ヒットしてしまいます。

2017/01/01の開発部だけを取得したいです。

解決した方法

ORACLEのLEAD関数を利用して、1件目の有効日とその次のレコードの有効日を1レコードとして取得するサブクエリにしました。

SELECT
　　SUB.有効日
　, SUB.所属
FROM
　(SELECT
　　　従業員NO
　　, 有効日
　　, LEAD(有効日, 1, ‘2999/12/31’)
　　　　　OVER(PARTITION BY 従業員NO, ORDER BY 有効日) 終了日
　　, 所属
　FROM
　　所属テーブル
　) SUB
WHERE
　　SUB.従業員NO = ‘77777’
AND SUB.有効日 <= ‘2017/02/01’
AND SUB.終了日 > ‘2017/02/01’

このSQLだと欲しかった2017/01/01の開発部だけが取得できました。

解説

まずサブクエリ部分で以下の結果を返す一時テーブルを作っているようなものです。

従業員NO	有効日	終了日	所属
77777	2016/04/01	2017/01/01	営業部
77777	2017/01/01	2017/03/01	開発部
77777	2017/03/01	2999/12/31	総務部

LEAD関数は引数で指定した項目(この例では有効日)を、指定した行数(この例では1)だけ後のレコードから取得します。‘2999/12/31’の指定は次の行がない場合に返す値です。ここでは遠い未来の日付を返すようにしています。

そしてこの一時テーブルに対してWHERE条件で’2017/02/01’以前の有効日で、終了日が’2017/02/01’よりも後の日付になるレコードに絞りこんでいます。

まとめ

今回はLEAD関数が利用できるORACLEだったので比較的すっきりしたSQLになりましたが、利用できないDBで同じことをしようとするともっと複雑なSQLになってしまいます。

プログラムはシンプルな方がいいので、用意されている関数があればそれを利用する。

可能ならテーブル定義を変更して、有効日と終了日を持たせてしまうのがよりシンプルになりそうですが、その場合終了日が分からない場合はどうするかといった別の問題がでてきますね。

問題を解決する方法は様々あるので、ケースバイケースで良い方法を選択していくのがいいですね。