More than 1 year has passed since last update.

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

Posted at 2022-08-10

初めに

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

このクイズは時間を扱います・・・が、なかなか厄介です

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.start_date <= T2.end_date

『重なる or 連続する』時間を取出す元データを
Timesheets テーブルをCROSS JOIN して作成します
　⇒　T1 から開始日、 T2 から終了日を取り出します
　⇒　開始日より終了日の方が後 になるので条件を追加
　　　T1.start_date <= T2.end_date
　⇒　『=』が付くのは task_idが 6 の様に
　　　開始日と終了日が同じ日が有るのを含める為です
　⇒　この条件を満たす組合せが残ります

▼ 出力(元データ①)

この時間の組合せが、『重なる or 連続する』箇所の
開始日(T1.start_date), 終了日(T2.end_date) を取出す元データとなります
　⇒　下記画像のオレンジ色の部分

元データ② を作る

SQL

SELECT T3.*, T4.*
FROM Timesheets AS T3, Timesheets AS T4
WHERE T3.end_date < T4.start_date

Timesheets テーブルを CROSS JOIN して元データを作っていますが
元データ①と条件が違います
　⇒　T3.end_date < T4.start_date
　⇒　T3の終了日より、T4の開始日の方が後日となるので
　　　T3,T4 は重ならない組合せになります

▼ 『元データ②』のイメージ

▼ 出力(元データ②)

元データ①：『開始日』と『終了日』を持つ時間の集合
元データ②：『重ならない』時間の集合

目的は『重なる or 連続する』時間の開始日と終了日の取得なので
これから行おうとすることは・・・
　[1]　元データ①から元データ②を取り除く
　[2]　残りのデータから開始日と終了日を取出す

▼ 出力(元データ① と元データ② を並べてみた)

元データを加工する①

SQL

SELECT T1.*, T2.*
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
   SELECT *
   FROM Timesheets AS T3, Timesheets AS T4
   WHERE
     T3.end_date < T4.start_date AND
     T1.start_date <= T3.start_date AND T3.end_date <= T2.end_date
     AND
     T1.start_date <= T4.start_date AND T4.end_date <= T2.end_date
)

元データ①から元データ②に有る組合せを除外したいのですが
T3,T4 は T1,T2 と紐づいていないのでそのままでは除外できません。

元データ①の WHERE句に NOT EXISTS を追加しています
この中に元データ②が入っていますが、条件が増えています
　⇒　T1.start_date <= T3.start_date AND T3.end_date <= T2.end_date
　　　AND
　　　T1.start_date <= T4.start_date AND T4.end_date <= T2.end_date
　⇒　T3,T4 は T1.start_date と T2.end_date の中に存在する
　　　時間の集合であることを定義しています
　⇒　T1.start_date と T2.end_date の内側に存在する T3,T4が
　　　T3.end_date < T4.start_date となる場合
　　　NOT EXISTS で除外されます
　⇒　『重なる or 連続する』時間の集合がT1,T2 に残ります

▼ ここで行う処理のイメージ

▼ 出力

NOT EXISTSで『重ならない時間』の組合せを取り除いた後のT1とT2
　⇒　このデータを加工して開始日と終了日を取出します

元データを加工する②

SQL

SELECT T1.start_date, MIN(T2.start_date), MAX(T2.end_date)
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
  SELECT *
  FROM Timesheets AS T3, Timesheets AS T4
  WHERE
    T3.end_date < T4.start_date AND
    T3.start_date >= T1.start_date AND T3.end_date <= T2.end_date
    AND
    T4.start_date >= T1.start_date AND T4.end_date <= T2.end_date
)
GROUP BY T1.start_date

T1.start_dateで GROUP BY しているのでT1.start_dateがユニークとなり
この内どれかが？開始日になります

MAX(T2.end_date)はT1.start_date が持つ組合せの中で一番大きな
T2.end_dateとなり、この内どれかが？終了日になります

MIN(T2.start_date) はT1.start_date が持つ組合せの中で一番小さな
T2.start_dateとなり、これを使って開始時間を見つけます

▼ 出力(画像右側)

このデータから開始日と終了日を取出します

元データを加工する③

SQL

SELECT T1.start_date, MIN(T2.start_date), MAX(T2.end_date)
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
  SELECT *
  FROM Timesheets AS T3, Timesheets AS T4
  WHERE
    T3.end_date < T4.start_date AND
    T3.start_date >= T1.start_date AND T3.end_date <= T2.end_date
    AND
    T4.start_date >= T1.start_date AND T4.end_date <= T2.end_date
)
GROUP BY T1.start_date
HAVING T1.start_date = MIN(T2.start_date)  -- 追加

▼ 出力(画像下側)

HAVING T1.start_date = MIN(T2.start_date) を追加
　⇒　この条件に合うのが取り出したい開始日です
　⇒　開始日が決まると、終了日も決まります
　⇒　何故・・・？細かく確認していきます

▼ 例：T1.start_date(1997-01-02) が開始日にはならない理由

▼ 例：T1.start_date(1997-01-14)が開始日にはならない理由

▼ 例：T1.start_date(1997-01-12) が開始日になる理由

▼ 例：T1.start_date(1997-01-06) が開始日になる理由

このSQLには不備があります

ここまで長々と解説させていただきましたが・・・
このSQLでは終了日が正しく取得できていません

開始日(T1.start_date)	終了日(T2.end_date)	正しい終了日
1997-01-01	1997-01-04	1997-01-05
1997-01-06	1997-01-09	← OK
1997-01-12	1997-01-15	← OK
1997-01-17	1997-01-17	← OK

これから、何処に不備が有るのか？を確認して修正します

元データを加工する①を修正

SQL

SELECT T1.*, T2.*
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
   SELECT *
   FROM Timesheets AS T3, Timesheets AS T4
   WHERE
     T3.end_date < T4.start_date AND
     T1.start_date <= T3.start_date AND T3.end_date <= T2.end_date
     AND
     T1.start_date <= T4.start_date AND T4.end_date <= T2.end_date
     AND --- ここから下を追加 ↓ ↓ ↓ ↓ ↓ ↓
     NOT EXISTS(
       SELECT * FROM Timesheets AS T5
       WHERE
         T5.start_date BETWEEN T3.start_date AND T3.end_date
         AND
         T5.end_date BETWEEN T4.start_date AND T4.end_date
     )
)

▼出力(画像右側)

新たに内側に NOT EXISTS を追加してこの中でTimesheetsのコピー T5
を使って T3,T4 に条件を追加しています。
　⇒　これにより出力データが１行増えます

ここで定義しているのは
外側の NOT EXISTS でT3,T4を使って重ならない組合せを除外していますが
内側の NOT EXISTS で必要な組み合わせを残しています

必要な組合せを残す・・・とは
Timesheets(T5) にある仕事の開始日と終了日が
T3,T4を使って定義している重ならない組合せの中に入る場合
除外する対象から外す。つまり、データが残ります

▼ (この処理のイメージ)

ここでの処理を細かく見てみます
外側のNOT EXISTS で取り除かれる時間の組合せを取り出します
　⇒　NOT EXISTS を EXISTS に変更して
　　　除外される組合せを表示させています
　⇒　下記画像の、緑色の点線部分です

取り除かれる時間の組合せの中にTimesheets(T5)の開始日と終了日が入るか？
時間を抜粋して確認してみます
　⇒　task_id=2 が条件を満たします
　　　開始日(1997-01-02) & 終了日(1997-01-04)
　⇒　赤い枠の行が残ります
　⇒　残りは条件を満たさないので除外されます

画像を確認ください

外側と内側の NOT EXISTS はセット

Timesheets にある仕事が『重なる or 連続する』開始日と終了日を取り出すので
T3.end_date < T4.start_date となる時間の組合せは除外するのは理解できますが
ただ連続していない・・・からと言って除外してしまうのは考慮が欠けています

今回のケースで言うと
『仕事１』の終了日が『仕事２』の開始日と重ならない場合は
連続しない・・・と見る事ができますが
この内側に『仕事３』が存在する場合
仕事１，仕事３、仕事２は重なっていると見る事ができます
この処理を外側と内側の NOT EXISTS で対応しています

下記がその処理のイメージです

このSQLの出力が開始日を終了日を取り出す元データとなります
これを使って、先ほどと同じ処理を行います

元データを加工する④

SQL

SELECT T1.start_date, MIN(T2.start_date), MAX(T2.end_date)
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
  SELECT *
  FROM Timesheets AS T3, Timesheets AS T4
  WHERE
    T3.end_date < T4.start_date AND
    T3.start_date >= T1.start_date AND T3.end_date <= T2.end_date
    AND
    T4.start_date >= T1.start_date AND T4.end_date <= T2.end_date
    AND NOT EXISTS
    (
       SELECT * FROM Timesheets AS T5
       WHERE T5.start_date BETWEEN T3.start_date AND T3.end_date
         AND T5.end_date BETWEEN T4.start_date AND T4.end_date
    )
)
GROUP BY T1.start_date
--HAVING T1.start_date = MIN(T2.start_date)

▼出力(画像右側)

上記で解説しておりますので、解説は割愛します

回答SQL

SQL

SELECT T1.start_date, MIN(T2.start_date), MAX(T2.end_date)
FROM Timesheets AS T1, Timesheets AS T2
WHERE T1.start_date <= T2.end_date
AND NOT EXISTS
(
  SELECT *
  FROM Timesheets AS T3, Timesheets AS T4
  WHERE
    T3.end_date < T4.start_date AND
    T3.start_date >= T1.start_date AND T3.end_date <= T2.end_date
    AND
    T4.start_date >= T1.start_date AND T4.end_date <= T2.end_date
    AND NOT EXISTS
    (
       SELECT * FROM Timesheets AS T5
       WHERE T5.start_date BETWEEN T3.start_date AND T3.end_date
         AND T5.end_date BETWEEN T4.start_date AND T4.end_date
    )
)
GROUP BY T1.start_date
HAVING T1.start_date = MIN(T2.start_date)

▼出力