期間が重複しているかを判定する条件式の導出方法

開始時間(start_time)と終了時間(end_time)を持つテーブル(例: schedule)で、あるレコードXと時間が重複したデータを抽出するSQLは、

SELECT *
FROM schedule
WHERE
    start_time < レコードXのend_time
    AND
    end_time > レコードXのstart_time

です。 しかしstart_timeとend_timeの関係性を考えるのに毎回頭が混乱してしまうので、今回はこのSQLの導出方法を書いておきます。

ちなみに、このSQLは、 一方のend_timeが他方のstart_timeと同じときには重複してい ないと判定する場合です。 例えば、[start_time, end_time] = [14: 00, 15:00][start_time, end_time] = [15: 00, 16:00]の2つのレコードは重複として判定しません。 [start_time, end_time] = [14: 00, 15:00][start_time, end_time] = [15: 00, 16:00]を重複と判定する場合は、不等号にイコール記号を加えてください。

重なりのパターン

レコードXとほかのレコードとの重なりのパターンは以下のレコードABCDEFの6パターンです。

f:id:yucatio:20180816175358p:plain

このうち、レコードXと重複するのは、レコードB,C,D,E、重複しないのがレコードA,Fです。

レコードB,C,D,Eに当てはまり、レコードA,Fに当てはまらない条件を導き出せればよいのですが、自分にはすぐには導き出せませんでした。

重ならない条件

そこで、一旦重ならないパターンに焦点を当てます。

重ならないパターンはレコードA,Fの2つで、レコードAは、

end_time <= レコードXのstart_time

となっているもの、

もう片方(レコードF)は、

レコードXのend_time <= start_time

です。(不等式にイコールを、含むかどうかは仕様によります。今回は、一方のend_timeが他方のstart_timeと同じときには重複してい ないと判定するものとします)

このどちらかの条件を満たしたすべてのレコードがレコードXと重複しません。式で書くと以下になります。

(end_time <= レコードXのsrart_time) OR (レコードXのend_time <= start_time)

重なる条件

重ならない条件式ができあがったので、これの否定をとれば、重なる条件式になります。

NOT((end_time <= レコードXのstart_time) OR (レコードXのend_time <= start_time))

ド・モルガンの法則により、

NOT(end_time <= レコードXのsrart_time) AND NOT(レコードXのend_time <= start_time)
↓
(end_time > レコードXのsrart_time) AND (レコードXのend_time > start_time)

となり、不等式が導出できました。

こちらもどうぞ

yucatio.hatenablog.com