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

# 初めに
この問題は、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で取り出します**


![re_1.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d3370a4e-74cf-92c5-e57e-ae11d7b23c96.jpeg)



**PostgreSQL で動作確認しています**



# テーブルとデータ
```SQL: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 の年月日の『日』と数字が同じになっています

![re_2.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bfe6f4be-d5f7-1830-8051-8d76a5965343.jpeg)

# 元データ① を作る
```SQL: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 の様に
　　　開始日と終了日が同じ日が有るのを含める為です
　⇒　**この条件を満たす組合せが残ります**

▼ 出力(元データ①)

![re_3.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/01e36248-6d01-61ee-9858-d5e6698136a3.jpeg)


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

![re_4.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/506ecce7-ac30-4303-5f88-2d8a5ea47b46.jpeg)


# 元データ② を作る
```SQL: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 は重ならない組合せ**になります

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

![re_6.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/dbcde087-57b2-3c9c-e86c-85e60865b53e.jpeg)


▼ 出力(元データ②)

![re_7.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8a022a0c-0554-a9d9-c1d3-2e80f29e5c83.jpeg)

-----

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

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


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


![re_8.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ef90107f-b486-0662-2cfd-d4cb74b7832f.jpeg)


# 元データを加工する①
```SQL: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 に残ります**


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

![re_9.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/264cad68-4e38-f80e-f1c9-dd87246be88c.jpeg)


-----

▼ 出力

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

![re_10.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bf6a21ff-10f5-125c-c3f3-51ffbff97450.jpeg)

# 元データを加工する②
```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
)
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となり、これを使って開始時間を見つけます



▼ 出力(画像右側)

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

![re_11.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/75116b63-4998-efe9-5e6d-3c75fe791fba.jpeg)


# 元データを加工する③
```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
)
GROUP BY T1.start_date
HAVING T1.start_date = MIN(T2.start_date)  -- 追加
```

▼ 出力(画像下側)

![re_12.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1bccd41b-8df5-4673-6960-f972724a4e3c.jpeg)


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

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

![re_14.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f23ddf2d-0375-d788-b9c0-6929674a378c.jpeg)

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

![re_15.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5201c807-0565-57d1-b71b-06b2f1a9fb6b.jpeg)

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

![re_16.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9589d9ea-666d-2852-f41b-d313accbcaf3.jpeg)

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

![re_17.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fe6ae656-c073-911f-1157-279523a9545a.jpeg)


# この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|

![re_18.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7e238cf5-5156-1185-f456-ac21fb9fedab.jpeg)

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


# 元データを加工する①を修正
```SQL: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 に条件を追加しています。
　⇒　`これにより出力データが１行増えます`

![re_20.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f2ca1d73-d9cc-41c5-4be1-65985efb8b5a.jpeg)


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

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

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

![re_21.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/88c5d8f8-e7c6-e1bf-e47e-ab54fa0edf35.jpeg)

-------

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

![re_22.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bc70ccd8-f49b-4d9d-5124-48621dd53bf5.jpeg)

-------

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

画像を確認ください


![re_23.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/5d85f8fa-14fb-5325-5774-89877b80aadb.jpeg)

![re_24.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3643e881-cec6-da40-4155-6858f429d09e.jpeg)

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

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

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

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

![re_25.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/300a21ca-ec77-a51b-b709-69b16b768fa4.jpeg)


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




# 元データを加工する④
```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)
```

▼出力(画像右側)

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


![re_26.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6db1bec3-0c35-8e4b-be42-425a0410fe4c.jpeg)




# 回答SQL
```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)
```

▼出力

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


![re_27.JPG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a2826489-751e-dc38-1d17-00aafba4fb49.jpeg)


『重なる or 連続する』時間の開始日と終了日が取得できました

|開始日(T1.start_date)|終了日 MAX(T2.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|


> 少し長くなりましたが、最初は意図的に不十分なSQLで解説しました
> この方がSQLでのデータを扱う考え方が理解しやすいと思ったからです
> 
> 画像を多数用いて、処理の流れを目視できるように解説したつもりですが
> それでも、掴みづらい解説もあるかもしれません
> このようなSQLは多少の慣れも必要かと思います
>
> データを集合として扱うSQLが体感できるいいパズルです


# 参考文献
[SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)