More than 3 years have passed since last update.

可視化SQL | 期間を結合する(その2)

Last updated at 2022-10-11Posted at 2022-10-11

初めに

この問題は、SQLパズル #59 『期間を結合する』を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです
しかし、今回のパズルの解説はあまり記載されていません・・・が

このクイズは時間を扱います・・・が
『その１』と同様になかなか厄介です(。≧д≦。)

仕事の開始日と終了日があるテーブルから
『重なる or 連続する』時間を見つけます

青色線が仕事の時間・・と見てください
全部で10個の仕事(task_id)が有ります
　⇒　task_id 1 は 1が開始、3が終了
　⇒　task_id 2 は 2が開始、4が終了
　⇒　task_id 3 は 4が開始、5が終了
　⇒　task_id 4 は 6が開始、9が終了
　⇒　task_id 5 は 9が開始、9が終了
　　　　・　・　・　・　・
　　　　・　・　・　・　・
　⇒　task_id 10 は 17が開始、17が終了

ここから、青色の部分が『重なる or 連続する』所を取出すと
画像の下のオレンジ色の線となります
このオレンジ色の開始日と終了日をSQLで取り出します

PostgreSQL で動作確認しています

テーブルとデータ

SQL

CREATE TABLE Timesheets(
	task_id CHAR(10) NOT NULL PRIMARY KEY,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	CHECK(start_date <= end_date)
);

INSERT INTO Timesheets
VALUES (1, '1997-01-01', '1997-01-03'),
       (2, '1997-01-02', '1997-01-04'),
       (3, '1997-01-04', '1997-01-05'),
       (4, '1997-01-06', '1997-01-09'),
       (5, '1997-01-09', '1997-01-09'),
       (6, '1997-01-09', '1997-01-09'),
       (7, '1997-01-12', '1997-01-15'),
       (8, '1997-01-13', '1997-01-14'),
       (9, '1997-01-14', '1997-01-14'),
       (10, '1997-01-17', '1997-01-17');

▼ 出力(画像左側)

上記で使った重なる時間のイメージ図を並べています
start_date と end_date の年月日の『日』と数字が同じになっています

元データを作る

SQL

SELECT T1.*, T2.*
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.end_date <= T2.end_date

最終的に、この組み合わせから『重なる or 連続する』時間の
開始日(T1.start_date), 終了日(T2.end_date) を取り出します

元データを作る条件が・・・
　⇒　T1.end_date <= T2.end_date
　⇒　この条件は基準を終了日に置いています

T1から開始日,T2から終了日を取出すので T2の方が後日になります
まずT1.終了日をみて、これより右側に有るT2.終了日を全て取出します
この作業を、T2.終了日の上から順に全て行います
　⇒　条件に合う、すべての組合せを取出す
　⇒　必要のない組合せを除外しています

下記画像の右側を確認ください

task id が 5 の様に開始日と終了日が同じ仕事があります
この事を考慮して『=』がつきます
　⇒　T1.end_date <= T2.end_date

▼　出力

この組合せから開始日と終了日を取出します・・・が
なかなか手ごわいです　(x_x；)
　⇒　最終的に、★の行を取出します
　⇒　開始日(1997-01-01)　終了日(1997-01-05)
　⇒　開始日(1997-01-06)　終了日(1997-01-09)
　⇒　開始日(1997-01-12)　終了日(1997-01-15)
　⇒　開始日(1997-01-17)　終了日(1997-01-17)

元データを加工する①

SQL

SELECT T1.start_date, T2.end_date, T3.*
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date

『元データを作る』に対して Timesheet(T3) を CROSS JOIN しています
T3の使用用途ですが・・・
開始日(T1.start_date),終了日(T2.end_date)の精査を行います
　⇒　上記の ★の行をT3を使って取出します

この出力は590行になるので、一部抜粋して確認してみます

元データを加工する②

SQL

SELECT T1.start_date, T2.end_date, T3.*
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-13'

開始日(1997-01-13)だけを取出します
　⇒　T1.start_date(1997-01-13) は4行有ります
　⇒　Timesheetには 10行のデータが存在します
　⇒　これを CROSS JOIN するので 4 x 10 = 40行になります

元データを加工する② - 1

SQL

SELECT T1.start_date, T2.end_date, T3.*,
  CASE
    WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
    ELSE 0 END AS flg1
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-13'

ここでの目的は・・・
開始日(1997-01-13)より前日にあるT3.開始日を見つける事です
　⇒　1997-01-13より左側に来る開始日
　⇒　T1 & T2 と 『重なる or 連続する』 必要があります

SELECTの中に CASE文を追加。Timesheet(T3)テーブルを使って
2つの条件が記述されています。両方を満たすと (flg1 = 1) とします
　[1] T3.start_date < T1.start_date
　[2] T1.start_date <= T3.end_date

[1]　T3の開始日が 1997-01-13 より前日(同じ日はダメ)

　　開始日(T1.start_date)が1997-01-13の組合せの中で
　　この日より左側にある開始日(T3.start_date)を探す

　　T3.start_date < T1.start_date
　　1997-01-01 < 1997-01-13 　・・・・①
　　1997-01-02 < 1997-01-13 　・・・・②
　　1997-01-04 < 1997-01-13 　・・・・③
　　1997-01-06 < 1997-01-13 　・・・・④
　　1997-01-09 < 1997-01-13 　・・・・⑤
　　1997-01-09 < 1997-01-13 　・・・・⑥
　　1997-01-12 < 1997-01-13 　・・・・⑦

　　これだけでは不十分です。

　　T1 & T2 の組合せと重なる必要があります
　　　⇒　次の条件でカバーします

[2]　T3の終了日が 1997-01-13 と同じ又は後日

　　T1.start_date <= T3.end_date
　　1997-01-13 <= 1997-01-15
　　　⇒　上記の⑦が [1][2]を満たす開始日となります

　　この条件を AND で加える事で T3の開始日が
　　T1.start_date & T2.end_date の組合せと
　　『重なる or 連続する』事を保証します

つまり・・・

[1][2]を満たすT3.start_date(1997-01-12) は
1997-01-13 より左側に来る開始日になります
　⇒　この条件に合うT3には (flg1 = 1) とします
　⇒　下記画像をご確認ください

▼ 出力

元データを加工する② - 2

SQL

SELECT T1.start_date, T2.end_date, T3.*,
  CASE
    WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
    ELSE 0 END AS flg1,
  CASE
    WHEN (T3.start_date <= T2.end_date AND T2.end_date < T3.end_date) THEN 1
    ELSE 0 END AS flg2
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-13'

ここでの目的は・・・
終了日(T2.end_date)より後日となるT3.終了日を見つける事です
　⇒　T2.end_date より右側に来る終了日
　⇒　T1 & T2 と 『重なる or 連続する』 必要があります

SELECTの中に CASE文を追加。Timesheet(T3)テーブルを使って
2つの条件が記述されています。両方を満たすと (flg2 = 1) とします
　[2] T2.end_date < T3.end_date
　[1] T3.start_date <= T2.end_date

[2]　T3の終了日が 1997-01-14 より後日(同じ日はダメ)

　　終了日(T2.end_date)が1997-01-14の組合せの中で
　　この日より右側に有る終了日(T3.end_date)を探す

　　T2.end_date < T3.end_date
　　1997-01-14 < 1997-01-15　・・・・①
　　1997-01-14 < 1997-01-17　・・・・②

　　これだけでは不十分です。

　　T1 & T2 の組合せと重なる必要があります
　　　⇒　次の条件でカバーします

[1]　T3の開始日が 1997-01-14 より前日 or 同じ

　　T3.start_date <= T2.end_date
　　1997-01-12 <= 1997-01-14
　　　⇒　上記①が[2][1]を満たす終了日

　　この条件を AND で加える事で T3の終了日が
　　T1.start_date & T2.end_date の組合せと
　　『重なる or 連続する』事を保証します

つまり・・・

[1][2]を満たすT3.end_date(1997-01-15) は
1997-01-14 より右側に来る終了日になります
　⇒　この条件に合うT3終了日には flg2 = 1 とします
　⇒　下記画像をご確認ください

▼ 出力

元データを加工する② - 3

SQL

SELECT T1.start_date, T2.end_date, 
  MAX(CASE
       WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
       ELSE 0 END) AS flg1,
  MAX(CASE
       WHEN (T3.start_date <= T2.end_date AND T2.end_date < T3.end_date) THEN 1
       ELSE 0 END) AS flg2
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-13'

GROUP BY T1.start_date, T2.end_date

▼ 出力

T1.start_date	T2.end_date	MAX(flg1)	MAX(flg2)
1997-01-13	1997-01-15	1	0
1997-01-13	1997-01-14	1	1
1997-01-13	1997-01-17	1	0

T1.start_date と T2.end_date で GROUP BY してるので
3つの組合せができます

　開始日　　　　終了日
1997-01-13　　1997-01-15
1997-01-13　　1997-01-14
1997-01-13　　1997-01-17

この組合せの中で、flg1,flg2 が 1 となる行が存在するか？を
MAX関数で確認しています

MAX(flg1) が 1 の時
　⇒　1997-01-13(赤色) より左側に開始日が存在します
　⇒　1997-01-12 は 1997-01-13 をカバーできる

MAX(flg2) が 1 の時
　⇒　1997-01-14(青色) より右側に終了日が存在します
　⇒　1997-01-15 は 1997-01-14 をカバーできる

・・・と言うことは
開始日(T1.start_date)、終了日(T2.end_date)より広い範囲をカバーできる
『重なる or 連続する』開始日と終了日が存在する・・・事になります

MAX(flg1),MAX(flg2)が1となる開始日と終了日は必要ないのでは？？
　⇒　後で確認します

次に、違う開始日(1997-01-06)で同じように確認してみます

元データを加工する③ - 1

SQL

SELECT T1.start_date, T2.end_date, T3.*,
  CASE
    WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
    ELSE 0 END AS flg1
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-06'

開始日(1997-01-06)だけを取出します
　⇒　T1.start_date(1997-01-06) は7行有ります
　⇒　Timesheetには 10行のデータが存在します
　⇒　これを CROSS JOIN するので 7 x 10 = 70行になります

ここでの目的は・・・
開始日(1997-01-06)より前日となるT3.開始日を見つける事です
　⇒　1997-01-06より左側に来る開始日
　⇒　T1 & T2 と 『重なる or 連続する』 必要があります

[1]　T3の開始日が 1997-01-06 より前日 (同じ日はダメ)

　　開始日(T1.start_date)が1997-01-06の組合せの中で
　　この日より左側にある開始日(T3.start_date)を探す

　　T3.start_date < T1.start_date
　　　⇒　1997-01-01 < 1997-01-06　・・・・①
　　　⇒　1997-01-02 < 1997-01-06　・・・・②
　　　⇒　1997-01-04 < 1997-01-06　・・・・③

　　1997-01-06 より前日に
　　T3.start_date：1997-01-01,1997-01-02,1997-01-04
　　が存在しますがこれだけでは不十分です

　　T1 & T2 の組合せと重なる必要があります
　　　⇒　次の条件でカバーします

[2]　T3の終了日が 1997-01-06 と同じ or 後日

　　上記[1]を満たすT3.end_date は
　　1997-01-03,1997-01-04,1997-01-05

　　これら終了日(T3.end_date)とT1.start_dateは
　　下記条件を満たしません
　　　⇒　T1.start_date <= T3.end_date
　　　⇒　1997-01-06 <= xxxx-xx-xx

開始日が1996-01-06で始まる組合せは
[1][2]を満たすT3.start_date は有りません
　⇒　重ならない
　⇒　flg1 が全部 0

▼ 出力

元データを加工する③ - 2

SQL

SELECT T1.start_date, T2.end_date, T3.*,
  CASE
    WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
    ELSE 0 END AS flg1,
  CASE
    WHEN (T3.start_date <= T2.end_date AND T2.end_date < T3.end_date) THEN 1
    ELSE 0 END AS flg2
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-06'

ここでの目的は・・・
終了日(T2.end_date)より後日となるT3.終了日を見つける事です
　⇒　T2.end_date より右側にある終了日
　⇒　T1 & T2 と 『重なる or 連続する』 必要があります

[2]　T3の終了日が 1997-01-14 より後日(同じ日はダメ)

　　終了日(T2.end_date)が 1997-01-14 の組合せの中で
　　この日より右側にある終了日(T3.end_date)を探す

　　T2.end_date < T3.end_date
　　　⇒　1997-01-14 < 1997-01-15　・・・・①
　　　⇒　1997-01-14 < 1997-01-17　・・・・②

　　　T3.end_date(1997-01-15,1997-01-17)が
　　　条件に合いますがこれだけでは不十分です。

　　T1 & T2 の組合せと重なる必要があります
　　　⇒　次の条件でカバーします

[1]　T3の開始日が 1997-01-14 と同じ or 前日

　　上記[2]を満たすT3.start_date は
　　1997-01-12,1997-01-17
　
　　これら開始日(T3.start_date)とT2.end_dateは
　　下記条件を満たす場合、重なります
　　T3.start_date <= T2.end_date
　　　⇒　1997-01-12 <= 1997-01-14　・・・OK
　　　⇒　1997-01-17 <= 1997-01-14　・・・NG

　　この条件を AND で加える事で T3の終了日が
　　T1.start_date & T2.end_date の組合せと
　　『重なる or 連続する』事を保証します

[1][2]を満たすT3.end_date(1997-01-15) は
T2.end_date(1997-01-14)より後日の終了日になります
　⇒　この条件に合うT3終了日には flg2 = 1 とします

▼ 出力

元データを加工する③ - 3

SQL

SELECT T1.start_date, T2.end_date, 
  MAX(CASE
       WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
       ELSE 0 END) AS flg1,
  MAX(CASE
       WHEN (T3.start_date <= T2.end_date AND T2.end_date < T3.end_date) THEN 1
       ELSE 0 END) AS flg2
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date AND T1.start_date = '1997-01-06'

GROUP BY T1.start_date, T2.end_date

▼ 出力

T1.start_date	T2.end_date	MAX(flg2)
1997-01-06	1997-01-09	0
1997-01-06	1997-01-15	0
1997-01-06	1997-01-14	1
1997-01-06	1997-01-17	0

T1.start_date と T2.end_date で GROUP BY しているので
4つの組合せができます

　開始日　　　　終了日
1997-01-06　　1997-01-09
1997-01-06　　1997-01-15
1997-01-06　　1997-01-14
1997-01-06　　1997-01-17

この組合せの中で、flg1,flg2が 1 となる行が存在するか？を
MAX関数で確認しています

MAX(flg1) が 0 の時
　⇒　1997-01-06 をカバーできる開始日は存在しません

MAX(flg2) が 1 の時
　⇒　1997-01-14(青色)より右側に終了日が存在します
　⇒　1997-01-15 は 1997-01-14 をカバーできる

終了日(T2.end_date)より広い範囲をカバーできる
『重なる or 連続する』終了日が存在する・・・事になります

これまで、開始日が1997-01-13 と 1997-01-06 の組合せを選んで
もっと左側に有る開始日を探し、もっと右側に有る終了日を探しました

あくまでも、ざっくりとした開始日と終了日を取出していた・・・事が
上の画像から、なんとなく理解できるかと思います

連続している or 連続していない・・・は
ここではまだ見てません

ここからがこのパズルの肝です

元データを加工する④

SQL

SELECT T1.start_date, T2.end_date, 
  MAX(CASE
       WHEN (T3.start_date < T1.start_date AND T1.start_date <= T3.end_date) THEN 1
       ELSE 0 END) AS flg1,
  MAX(CASE
       WHEN (T3.start_date <= T2.end_date AND T2.end_date < T3.end_date) THEN 1
       ELSE 0 END) AS flg2
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date -- AND T1.start_date = '1997-01-06'

GROUP BY T1.start_date, T2.end_date

ORDER by T1.start_date, T2.end_date

T1.start_date を指定せずに
T1.start_date, T2.end_date で GROUP BY して
MAX(flg1) と MAX(flg2) を確認します

flg1 が 1 になる開始日は
　⇒　これよりもっと前日(左側)に他の開始日が存在する
　⇒　求めたい開始日ではない

flg2 が 1 になる終了日は
　⇒　これよりもっと後日(右側)に他の終了日が存在する
　⇒　求めたい終了日ではない

～～～～～～～～～～～～～～～～～～～～～～～～～～～
最初に作った元データにはすべての組合せが有ります
ここから必要のない開始日と終了日を持つ行を除きます
　⇒　flg1 = 1, flg2 = 1 となる行

つまり、残った行
flg1 and flg2 が『0』となる組合せの中に
ここで取り出したい開始日と終了日が有ります

▼出力

元データを加工する⑤

SQL

SELECT T1.start_date, T2.end_date
FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
WHERE T1.end_date <= T2.end_date
GROUP BY T1.start_date, T2.end_date
HAVING MAX(CASE WHEN (T1.start_date > T3.start_date AND T1.start_date <= T3.end_date)
                     OR
                     (T2.end_date >= T3.start_date AND T2.end_date < T3.end_date)
				THEN 1
				ELSE 0 END) = 0

ORDER BY start_date, end_date

先ほどまで、SELECT文にあった CASE文をHAVING句の中に記述しています
HAVINGの中身は・・・
　⇒　flg1 または flg2 が 1 の時は『1』
　⇒　flg1, flg2 の両方が 0 の時は『0』
　⇒　HAVING MAX(・・・) = 0
　⇒　HAVING で『0』の行だけを取出している

かみ砕いて解説すると・・・

さっきまで使っていた flg1, flg2 で記述すると
下記のような感じになります
HAVING MAX(CASE WHEN flg1 = 1 OR flg2 = 1 THEN 1 ELSE 0 END) = 0

CASE文からflg1, flg2のどちらかが1の時に1が返ってくる
HAVING の条件で CASE文からの戻り値が 0 を指定しているので
　⇒　HAVING MAX(CASE ・・・) = 0
flg1, flg2が 0 となる行だけを取出している

▼出力(画像右側)

回答SQL

SQL

SELECT X.start_date, MIN(X.end_date) AS end_date
FROM
(
   SELECT T1.start_date, T2.end_date
   FROM Timesheets AS T1, Timesheets AS T2, Timesheets AS T3
   WHERE T1.end_date <= T2.end_date
   GROUP BY T1.start_date, T2.end_date
   HAVING MAX(CASE WHEN (T1.start_date > T3.start_date AND T1.start_date <= T3.end_date) 
                        OR
                        (T2.end_date >= T3.start_date AND T2.end_date < T3.end_date)
				   THEN 1 
				   ELSE 0 END) = 0
) AS X
GROUP BY X.start_date

▼回答出力

開始日(start_date)	終了日(end_date)
1997-01-01	1997-01-05
1997-01-06	1997-01-09
1997-01-12	1997-01-15
1997-01-17	1997-01-17

ここで行っているのは
① T1.start_date で GROUP BY して開始日を取出す
② T2.end_dateが一番早い日を終了日とする

先ほども言いましたが

連続している or 連続していない・・・は
ここではまだ見てません

終了日を見つけるには・・・
連続が途切れる場所を見つける必要が有ります
下記画像を見るとイメージしやすいです

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
開始日が 1997-01-01 に注目すると
1997-01-05,1997-01-09,1997-01-15,1997-01-17
のどれかが終了日になります

終了日が 1997-01-05 の後は隙間が有り連続しません
　⇒　1997-01-05 を取出すには・・・
　⇒　最小の日が終了日となります
　⇒　MIN(X.end_date)

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
開始日が 1997-01-06 に注目すると
1997-01-09,1997-01-15,1997-01-17
のどれかが終了日になります

終了日が 1997-01-09 の後は隙間が有り連続しません
　⇒　1997-01-09 を取出すには・・・
　⇒　最小の日が終了日となります
　⇒　MIN(X.end_date)

最後は、終了日の取り出し方です

開始日と終了日を時系列に並べると回答と同じにはなりますが
1997-01-01 -> 1997-01-06 -> 1997-01-12 -> 1997-01-17
1997-01-05 -> 1997-01-09 -> 1997-01-15 -> 1997-01-17

しかしここでは、start_date で GROUP BY しているので
一番小さい終了日を取出すと時系列と同じように取り出せます
　⇒　MIN(X.end_date)

長々となりましたが
その１と同様に、その２もかなり奥の深いSQLです

データを集合として扱うSQLの考え方が見える
いいクイズだと思います・・・が
サックっと読めるSQLではないと思います

この本の著作、Joe Celkoさんもこのパズルについて
以下の様に述べています

一部抜粋・・・

ただ、これを簡単なクエリで行うのは困難である
以下に紹介するコードも
目で追って動作を確認するのは容易ではない。
注意深く読んでほしい

参考文献

SQLパズル第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

可視化SQL | 期間を結合する(その2)

初めに

テーブルとデータ

元データ を作る

元データ を加工する①

元データ を加工する②

元データ を加工する② - 1

元データ を加工する② - 2

元データ を加工する② - 3

元データ を加工する③ - 1

元データ を加工する③ - 2

元データ を加工する③ - 3

元データ を加工する④

元データ を加工する⑤

回答SQL

参考文献

元データを作る

元データを加工する①

元データを加工する②

元データを加工する② - 1

元データを加工する② - 2

元データを加工する② - 3

元データを加工する③ - 1

元データを加工する③ - 2

元データを加工する③ - 3

元データを加工する④

元データを加工する⑤