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

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

このクイズは時間が入っているデータから重複する時間を取出す問題です
各仕事ID(proc_id)がいくつの仕事と時間が重なっているか？を抽出します

『その３』ではちょっと複雑です。何が複雑か？と言いますと、自己結合を3回行っています
Procsテーブルを3つ CROSS JOIN しているので、全体のイメージが想像しにくいです

しかし、見方を変えるとそれほど難しくはありません
何故なら、**SQL を書き換えると 『その２』で解説した内容と同じになるからです**

答えとなる SQL を最初に記載し、これを『その２』と同じとなる様に修正していきます

#まずは回答となる SQL を確認する
回答の SQL を確認すると、P2のstart_time が基準になっています。
これは P1を時間の重なる部分とみなして、P2の開始時間を使って重なる時間を取出しています
この時間を P1のstart_time に置き換えます。P1.start_time, P1.end_time の置換も行います

置換前と置換後のSELECT文の出力を見ると、P1.proc_id と P2.proc_idが逆になっていますが
カウント数は同じです。 CROSS JOIN で組み合わせを作り、最終的に P1.proc_id と P2.proc_id
のユニークで数をカウントしているので、start_time の置換は問題ないかと思います

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c26e8c71-cbfd-a322-852b-230a817b72e7.png)

何故、P1.start_time におきかえたのか？

**『その２』で重なる時間を考えた時 P1.start_time を使って 条件を書いていました
こちらの方が全体の統一感が有り、よりイメージしやすい・・・と思ったからです**

#SQL を 『その２』の形に変形してみる
```SQL:SQL
-- 元のSQL
SELECT P1.proc_id, P2.proc_id, COUNT(*)
FROM Procs AS P1, Procs AS P2, Procs AS P3
WHERE P2.anest_name = P1.anest_name 
	AND P3.anest_name = P1.anest_name
	AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time 
	AND P3.start_time <= P1.start_time AND P1.start_time < P3.end_time
GROUP BY P1.proc_id, P2.proc_id
```

元のSQL で Procs AS P1, Procs AS P2 での処理を1つ目として対応し
その後に、 Procs AS P3 を結合する・・・対応を2つ目の処理とみなします

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/751f79db-c270-6760-ff5b-20f0c066c27d.png)

この処理に合う形に SQL を変形させます。出力は元の SQL と同じになります。
元のSQLは CROSS JOIN でデータ結合を行っていますが、このクイズのデータの場合
結合条件が人の名前なので、CROSS JOIN と INNER JOIN では出力される結果は同じになる・・・
事を 『その１』、『その２』で確認しています。

```SQL:SQL
-- 『その２』の形に合したSQL
SELECT a1.proc_id_1, a1.proc_id_2, COUNT(*)
FROM
(-- 2つ目の処理
   SELECT Con.*
   FROM
   ( -- 1つ目の処理
      SELECT P1.proc_id AS proc_id_1, P2.proc_id AS proc_id_2, P1.anest_name,P1.start_time
      FROM Procs AS P1
        INNER JOIN Procs AS P2 ON P2.anest_name = P1.anest_name 
          AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time
   ) AS Con
   INNER JOIN Procs AS P3 ON P3.anest_name = Con.anest_name 
      AND P3.start_time <= Con.start_time AND Con.start_time < P3.end_time
) AS a1
GROUP BY a1.proc_id_1, a1.proc_id_2
```


#変形した SQL を解析
全く同じ記述ではありませんが、『その２』の処理と似ています
①内側の INNER JOIN で 仕事(proc_id)に対して、重なる時間を抽出しています
　　⇒重なる時間の判定は P1.start_time を見ています
②外側の INNER JOIN で 重なる時間の仕事に対して、再度全組合せを再作成しています
　　⇒重なる時間尾判定は Con.start_time(P1.start_time)を見ています
③最後に GROUP BY を1回だけ実施しています。

『その２』では2つのINNER JOIN を使って、2段階でデータを結合していますが
『その３』元のSQLでは**最初に3つの Procs テーブルをまとめて CROSS JOIN** し
その後、WHERE句で『その２』と同じ条件となるように重なる時間を定義しています

#回答：重なり合う時間の最大数
回答では 最後の GROUP BY を P1.proc_id で行っていますが、ここでは P2.proc_id で行っています

その理由は

**『その２』で解説した時と同じ様に、P2を重なる時間とみなしているからです
P2を重なる時間とみなすために、最初に P2.start_time を P1.start_time に変換しました**



```SQL:SQL
-- SQLパズルの回答と同じとなるように変数を変えます
WITH Vprocs AS (
  SELECT a1.proc_id_1, a1.proc_id_2, COUNT(*) AS total
  FROM
  (
    SELECT Con.*
    FROM
    (
      SELECT P1.proc_id AS proc_id_1, P2.proc_id AS proc_id_2, P1.anest_name,P1.start_time
      FROM Procs AS P1
        INNER JOIN Procs AS P2 ON P2.anest_name = P1.anest_name 
          AND P2.start_time <= P1.start_time AND P1.start_time < P2.end_time
     ) AS Con
     INNER JOIN Procs AS P3 ON P3.anest_name = Con.anest_name 
       AND P3.start_time <= Con.start_time AND Con.start_time < P3.end_time
  ) AS a1
  GROUP BY a1.proc_id_1, a1.proc_id_2
)

SELECT proc_id_2 AS proc_id, MAX(total) AS max_inst_count
FROM Vprocs
GROUP BY proc_id_2
```

|proc_id |max_inst_count|
|---|---|
|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)