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

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

![re_1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/023c6c5d-ef26-6192-704e-a1cd511dc4d3.jpeg)

# テーブルとデータ
```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/fe5c283a-3372-f28a-278c-bd98aff9c7cf.jpeg)


# 元データ を作る① - 1
```SQL:SQL
SELECT T1.*,T2.*
FROM Timesheets AS T1
LEFT JOIN Timesheets AS T2
  ON T1.start_date > T2.start_date
```

Timesheets(T1)テーブルにTimesheets(T2)テーブルを
LEFT JOIN して元データを作ります
この時の条件
　⇒　T1.start_date > T2.start_date
　⇒　T1.開始日より前日となる開始日(T2.start_date)を
　　　T2から取り出して LEFT JOIN します

下記画像では開始日(1997-01-09)を詳しく見ています
　⇒　T1側には 1997-01-09が2行有ります
　⇒　T2側には条件を満たす開始日が4行有ります
　⇒　2 x 4 = 8 行のデータが結合されます

他の開始日も同じ要領です
T1.開始日 より前にある T2.開始日 が結合されます

ここでは全組合せを作っているのですが
**同じ日の組合せは必要有りません**

例えば・・・
1997-01-01(T1.start_date) 1997-01-01(T2.start_date) 
1997-01-02(T1.start_date) 1997-01-02(T2.start_date)

**これらを除くために結合条件に『=』が付きません**
　⇒　T1.start_date > T2.start_date

> 同じテーブルを結合させて組合せを作るのは
> 以前、似たような処理を行った事が有ります
> 
> 下記記事を参照ください

https://qiita.com/hidesroom/items/a6fdcf939c54d99b2417

▼ 出力

![re_4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ef532981-99e6-4b2b-e64b-a0887b911d6b.jpeg)


# 元データ を作る① - 2
```SQL:SQL
SELECT T1.*,T2.*
FROM Timesheets AS T1
LEFT JOIN Timesheets AS T2
  ON T1.start_date > T2.start_date AND T1.start_date <= T2.end_date
```

元データを作る条件に2つ目の条件を追加
　⇒　T1.start_date <= T2.end_date

2つの条件は AND でつながっているので
2つの条件を両方とも満たさないと
T2からT1にデータが JOIN されません・・・
　⇒　`LEFT JOIN なので T1側にはデータは残りますが`
　　　`T2側には空白が入ります`

下記画像をご確認ください

▼出力(画像右側)
![re_5.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/afff4408-a646-f3ef-2559-ff604946069d.jpeg)




# 元データ を解析する

先ほど LEFT JOIN で取り出したデータは
どのようなデータであるか？いくつか確認してみます


ここで行っている事は、『その２』と類似しています・・・
と言いますか、同じ事をしています

① T1.開始日を決めます
② T1.開始日より、左側に来るT2.開始日を探します
　　⇒　T1.start_date > T2.start_date
③ T1.開始日とT2.終了日を見て『T1 & T2』が
　『重なる or 連続する』か？を確認しています
　　⇒　T1.start_date <= T2.end_date
④　この処理を全ての T1.開始日で行います

ここで、T2のデータが結合されている行は
**T1.開始日より広い範囲をカバーできる開始日が存在する**
と言えます

言い換えると・・・
T2のデータが結合されているT1.開始日は必要ないので
**T2側が空白の行に、取出したい開始日が含まれています**



～～～～～～～～～～～～～～～～～～～～～～～～～～～～
[1] 開始日(1997-01-04)に注目します

![re_6.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ab33fb02-eef0-1a1c-66e6-cd3475b12e79.jpeg)


～～～～～～～～～～～～～～～～～～～～～～～～～～～～
[2] 開始日(1997-01-09)に注目します

![re_7.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9bc2b45c-815e-9599-db29-098e558daf67.jpeg)



# 元データ を作る② - 1
```SQL:SQL
SELECT T3.*,T4.*
FROM Timesheets AS T3
LEFT JOIN Timesheets AS T4
  ON T3.end_date < T4.end_date
```

Timesheets(T3)テーブルにTimesheets(T4)テーブルを
LEFT JOIN して元データを作ります
この時の条件
　⇒　T3.end_date < T4.end_date
　⇒　T3.終了日より後日となる終了日(T4.end_date)を
　　　T4から取り出して LEFT JOIN します

下記画像では終了日(1997-01-03)を詳しく見ています
　⇒　T3側には 1997-01-03が1行有ります
　⇒　T4側には条件を満たす終了日が9行有ります
　⇒　1 x 9 = 9 行のデータが結合されます

他の終了日も同じ要領です
T3.終了日 より後にある T4.終了日 が結合されます

先ほどと同じように、全組合せを作る際に
同じ日の組合せは必要有りません

例えば・・・
1997-01-03(T3.end_date) 1997-01-03(T4.end_date)
1997-01-04(T3.end_date) 1997-01-04(T4.end_date)

**これらを除くために結合条件に『=』が付きません**
　⇒　T3.end_date < T4.end_date

![re_10.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1e94aece-0aab-4f50-9627-a9e24f808a34.jpeg)



# 元データ を作る② - 2
```SQL:SQL
SELECT T3.*,T4.*
FROM Timesheets AS T3
LEFT JOIN Timesheets AS T4
  ON T3.end_date < T4.end_date AND T3.end_date >= T4.start_date
```

元データを作る条件に2つ目の条件を追加
　⇒　T3.end_date >= T4.start_date

2つの条件は AND でつながっているので
2つの条件を両方とも満たさないと
T4からT3にデータが JOIN されません・・・
　⇒　`LEFT JOIN なので T3側にはデータは残りますが`
　　　`T4側には空白が入ります`

下記画像をご確認ください

▼出力(画像右側)
![re_11.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/172ef2ac-64d9-b73c-9b6e-5cd38f2b96c5.jpeg)




# 元データ を解析する

先ほど LEFT JOIN で取り出したデータは
どのようなデータであるか？いくつか確認してみます

ここで行っている事も同じく『その２』と類似しています


① T3.終了日を決めます
② T3.終了日より、右側に来るT4.終了日を探します
　　⇒　T3.end_date < T4.end_date
③ T3.終了日とT4.開始日を見て『T3 & T4』が
　『重なる or 連続する』か？を確認しています
　　⇒　T3.end_date >= T4.start_date
④　この処理を全ての T3.終了日で行います

ここで、T4のデータが結合されている行は
**T3.終了日より広い範囲をカバーできるT4.終了日が存在する**
と言えます

言い換えると・・・
T4のデータが結合されているT3.終了日は必要ないので
**T4側が空白の行に、取出したい終了日が含まれています**


～～～～～～～～～～～～～～～～～～～～～～～～～～～～
[1] 終了日(1997-01-04)に注目します

![re_12.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/92ac88c9-47a7-2515-7b85-a85cac31457c.jpeg)


～～～～～～～～～～～～～～～～～～～～～～～～～～～～
[2] 終了日(1997-01-14)に注目します

![re_13.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ced097de-3807-ddea-dca7-c3818ac5c3d7.jpeg)

# 元データ を作る③
```SQL:SQL
SELECT X.start_date,Y.end_date
FROM
( -- 開始日
  SELECT T1.start_date --,COUNT(T2.start_date)
  FROM Timesheets AS T1
  LEFT JOIN  Timesheets AS T2
    ON T1.start_date > T2.start_date 
       AND T1.start_date <= T2.end_date
  GROUP BY T1.start_date
  HAVING COUNT(T2.start_date) = 0
) AS X(start_date)

INNER JOIN
( -- 終了日
  SELECT T3.end_date --,COUNT(T4.start_date)
  FROM Timesheets AS T3
  LEFT JOIN Timesheets AS T4
    ON T3.end_date < T4.end_date
      AND T3.end_date >= T4.start_date
  GROUP BY T3.end_date
  HAVING COUNT(T4.start_date) = 0
) AS Y(end_date) ON X.start_date <= Y.end_date

ORDER BY X.start_date,Y.end_date
```

▼ 出力
|X.start_date|Y.end_date|
|---|---|
|1997-01-01|1997-01-05|
|1997-01-01|1997-01-09|
|1997-01-01|1997-01-15|
|1997-01-01|1997-01-17|
|1997-01-06|1997-01-09|
|1997-01-06|1997-01-15|
|1997-01-06|1997-01-17|
|1997-01-12|1997-01-15|
|1997-01-12|1997-01-17|
|1997-01-17|1997-01-17|

このSQLは開始日の出力に対して
終了日の出力を結合しています

INNER JOIN を使っていますが
LEFT JOIN でも結果は同じです

～～～～～～～～～～～～～～～～～～～～～～～～～
まずは開始日を詳しく見てみます
開始日のサブクエリでは[1][2][3]を実行しています

[1]　T1.start_date で GROUP BYする
　　これにより、開始日がユニークになります
[2]　この開始日に結合されるT2.start_dateをカウント
[3]　カウントの数が0の行を取出します
　　⇒　HAVING COUNT(T2.start_date) = 0

最終的に下記開始日が出力されます
　⇒　T1.start_date
　⇒　1997-01-01,1997-01-06,1997-01-12,1997-01-17

▼ 開始日の中身
![re_16.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a656fca9-b83a-9ec9-06f7-e793c2f3c8a5.jpeg)


～～～～～～～～～～～～～～～～～～～～～～～～～
次に終了日を詳しく見てみます
終了日のサブクエリでは[1][2][3]を実行しています

[1]　T3.end_date で GROUP BYする
　⇒　終了日がユニークになります
[2]　この終了日に結合されるT4.start_dateをカウント
[3]　カウントの数が0の行を取出します
　⇒　HAVING COUNT(T4.start_date) = 0

最終的に下記終了日が出力されます
　⇒　T3.end_date
　⇒　1997-01-05,1997-01-09,1997-01-15,1997-01-17

▼ 終了日の中身
![re_17.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1a107798-d461-14b6-7d12-4c66c1d0155f.jpeg)


～～～～～～～～～～～～～～～～～～～～～～～～～
取出した開始日と終了日をJOINするのですが
その時の条件が X.start_date <= Y.end_date
　⇒　`開始日より後日となる終了日が結合されます`
　⇒　比較演算子に『=』がついています
　　　**同じ開始日、終了日を含む 全組合せを作ります**
　　　1997-01-17(T1.start_date) 1997-01-17(T3.end_date)
　⇒　下記画像の右下をご確認ください

▼ 出力(画像左下)

![re_18.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b4373c18-af28-5b04-0265-fdb69b875908.jpeg)

# 回答SQL
```SQL:SQL
SELECT X.start_date, MIN(Y.end_date) AS end_date
FROM
( -- 開始日
	SELECT T1.start_date --,COUNT(T2.start_date)
	FROM Timesheets AS T1
	LEFT JOIN  Timesheets AS T2
		ON T1.start_date > T2.start_date 
		AND T1.start_date <= T2.end_date
	GROUP BY T1.start_date
	HAVING COUNT(T2.start_date) = 0
) AS X(start_date)

INNER JOIN
( -- 終了日
	SELECT T3.end_date --,COUNT(T4.start_date)
	FROM Timesheets AS T3
	LEFT JOIN Timesheets AS T4
		ON T3.end_date >= T4.start_date
		AND T3.end_date < T4.end_date
	GROUP BY T3.end_date
	HAVING COUNT(T4.start_date) = 0
) AS Y(end_date) ON X.start_date <= Y.end_date

GROUP BY X.start_date

ORDER BY X.start_date
```

▼ 出力(回答)

|X.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|

先ほど作成した開始日と終了日をJOINしたデータに
取出したい開始日と終了日の全組合せが有るので
日にちが早い順に上から取り出すと
『重なる or 連続する』時間が取り出せます
　⇒　『その２』と同じですね
　　　詳しくは、『その２』をご確認ください

![re_19.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fb9897a3-5d56-74cf-1dcc-570ee403a24d.jpeg)

> 『その２』ではGROUP BY で集約した後のデータを
> HAVING と CASE文を使って
> 戻り値が 0 となる行を取出していた
> 
> 『その３』では同じく 0 となる行を
> JOIN を使って結合している
> 
> 『その３』の方が読みやすいSQLだと思いますが
> どちらも面白い解法だと思います

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