可視化 SQL | 重なる時間と CROSS JOIN (その4)

# 初めに
この問題は、SQLパズル #３ 忙しい麻酔医 を参考にしています

重なる時間を求める SQLパズル#3 『その４』ですが、『その２』とほぼ同じです

何が違うか・・？と言いますと

テーブルのJOINの方法が CROSS JOIN を使っている事・・・と
重なる時間を P1 として、P2の開始時間を用いて重なる条件を書いています

同じ課題でも人によってとらえ方が異なります

もしかしたら、ジョーセルコさんは、あえて『その４』をチョイスしてのかもしれません

#重なる時間の考え方
[1] 資料１ にあるオレンジの点線の部分を重なる時間とします
[2] 資料２ 開始時間と重なる時間の開始時間を見ます
[3] 資料３ 開始時間と重なる時間の終了時間を見ます

『その２』と同じく重なる条件を**開始時間だけ**見てます

全組合せを作成し、開始時間をP2で保持します
P1側に、全組合せの開始時間と終了時間を保持します
P2の開始時間が、P1の開始時間と終了時間の中に入っているか？を判定します

#重なる時間のイメージ図
P1を重なる時間とし、P2の開始時間がP1の内側に入っているか？で判定しています

『その２』と比べてみると、重なる時間を P1と見るか、P2と見るかの違いだけなので
重なる時間を取出したイメージ図を見ると、同じ結果になっています

![rep_0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/eaede584-cb78-6858-35d0-3692b7ec9ef0.png)

重なる時間の判定ですが
資料１、資料２の両方の条件に合う時間P1が、重なる時間となる


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d42fbd89-1cc6-9c8d-d2f8-302ba92f982f.png)

----

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/29ae5a95-b754-b324-c857-dd6ac69514de.png)

#Proc テーブルデータ
```SQL:SQL
CREATE TABLE Procs(
	proc_id integer,
	anest_name text,
	start_time text,
end_time text);

INSERT INTO Procs VALUES(10,'Baker','08:00','11:00');
INSERT INTO Procs VALUES(20,'Baker','09:00','13:00');
INSERT INTO Procs VALUES(30,'Dow','09:00','15:30');
INSERT INTO Procs VALUES(40,'Dow','08:00','13:30');
INSERT INTO Procs VALUES(50,'Dow','10:00','11:30');
INSERT INTO Procs VALUES(60,'Dow','12:30','13:30');
INSERT INTO Procs VALUES(70,'Dow','13:30','14:30');
INSERT INTO Procs VALUES(80,'Dow','18:00','19:30');
```

#CROSS JOIN でデータ結合
『その２』では INNER JOIN を使いましたが、『その４』ではCROSS JOINを使用しています
WHERE句で同じ名前の条件が記載されているので、INNER JOIN の場合と同じ結果になります

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8178683f-3380-ef8c-4139-d33c3e14dd80.png)

#集計対象となる元データの作成(1回目)
Bakerさんは proc_idが 10,20 2つのお仕事があるので全組合せは 2 x 2 = 4件
Dowさんは proc_idが 30,40,50,60,70,80 6件のお仕事が有るので 6 x 6 = 36件

CROSS JOIN を使って全データの組合せを作ります

『その２』と P1 と P2 が逆ですが、結果は同じになります


![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/66f76d76-adfc-2b94-8168-d5275923ba88.png)

---

![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8ff4ea32-c57d-45dc-228a-9220dcb94bda.png)

#重なる時間
上記で、重なる時間が取出せたので 個人毎にP2の開始時間で集約します
P2.start_time に対して、P1の時間がどれだけ重なっているか？カウントします


```SQL:SQL
-- 仕事開始時間と重なる時間の数
SELECT P2.anest_name, P2.start_time,COUNT(*)
FROM Procs AS P1, Procs AS P2
WHERE P1.anest_name = P2.anest_name
  AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
 GROUP BY P2.anest_name, P2.start_time --個人毎、開始時間で集約
```

![rep_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c260a8c2-225b-8b68-fd68-6151f16e1013.png)

#集計対象となる元データの作成(2回目)
『その２』と結合する順序が逆にはなりますが、結果は同じになります

元データの Procs テーブルに対して、上記で取得できたデータを CROSS JOIN させて
集計対象となる元データを再作成します

『その２』では、集計データに対して、Procs テーブルをINNER JOINしています

```SQL:SQL
-- Procsテーブルに対して、重なる時間を cross join して全組合せを作る
SELECT P1.*,E1.*
FROM Procs AS P1,
(
	SELECT P2.anest_name, P2.start_time, COUNT(*) 
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name 
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  --『その２』と違い P2で集約
) AS E1(anest_name, etime, ecount)
WHERE E1.anest_name = P1.anest_name 
```
```SQL:SQL
-- Procsテーブルに対して、重なる時間を cross join して全組合せを作る
-- CROSS JOIN が見てわかりやすい様に少し変形させます
WITH Pro AS (
	SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  --『その２』と違い P2で集約
)

SELECT P1.*,E1.*
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
```
```SQL:SQL
-- 全組合せから重なる時間を取出す
WITH Pro AS (
	SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
	FROM Procs AS P1, Procs AS P2 
	WHERE P1.anest_name = P2.anest_name
		AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time  --『その２』と違い P2で集約
)

SELECT P1.*,E1.*
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
  AND E1.etime >= P1.start_time AND E1.etime < P1.end_time
```

![rep_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/dbbadb78-fce8-408d-041c-cc0471ccd4be.png)

-----

ここで、少し『その３』についてお話すると・・・下記 SQL を見てください

**Procs テーブルを3回 CROSS JOIN** しているように見えませんか？
『その３』 で オリジナルの SQL を『その２』の形に変形できたのが想像できましたでしょうか・・・・

Procs AS P1, (Procs AS P1, Procs AS P2) E1

![rep_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/69e086b5-20fa-a298-bcb2-df594e4f392e.png)

#回答：重なり合う時間の最大数
このクイズの回答は下記となります(各proc_id に対する、最大の掛持ちの個数)

```SQL:SQL
-- 各proc_id に対する、最大の掛持ちの個数
WITH Pro AS (
    SELECT P2.anest_name AS anest_name, P2.start_time AS etime, COUNT(*) AS ecount
    FROM Procs AS P1, Procs AS P2 
    WHERE P1.anest_name = P2.anest_name
        AND P1.start_time <= P2.start_time AND P1.end_time > P2.start_time
    GROUP BY P2.anest_name, P2.start_time  --『その２』と違い P2で集約
)

SELECT P1.proc_id, P1.anest_name, MAX(E1.ecount) AS maxops
FROM Procs AS P1,Pro AS E1  -- CROSS JOIN
WHERE E1.anest_name = P1.anest_name
  AND E1.etime >= P1.start_time AND E1.etime < P1.end_time
  
GROUP BY P1.proc_id, P1.anest_name
```

```SQL:SQL
-- オリジナルの回答
SELECT P1.proc_id, P1.anest_name, MAX(E1.ecount) AS maxops
FROM Procs AS P1, 
(
	SELECT P2.anest_name, P2.start_time, COUNT(*)
	FROM Procs AS P1, Procs AS P2
	WHERE P1.anest_name = P2.anest_name 
		AND P1.start_time <= P2.start_time 
		AND P1.end_time > P2.start_time
	GROUP BY P2.anest_name, P2.start_time
) AS E1(anest_name, etime, ecount)
WHERE E1.anest_name = P1.anest_name 
	AND E1.etime >= P1.start_time AND E1.etime < P1.end_time

GROUP BY P1.proc_id, P1.anest_name
```

|proc_id |maxops|
|---|---|
|10|2|
|20|2|
|30|3|
|40|3|
|50|3|
|60|3|
|70|2|
|80|1|

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