Visualize SQL | Overlap Time and CROSS JOIN (part 1)

# Beginning
This technique is based on SQL Puzzle # 3 anesthesia

In this puzzle, it is required to get overlap time from data that contains time fields.
How many jobs are overlapped for each job(proc_id)?

I will explain using images so that it is easy to imagine.
It is better you have 『SQL Puzzle』by your side.

# how to see overlap time
[1] rep 1 : inside the blue dotted lines are the time that overlaps
[2] ①,②,③,④ are the time overlaps, and ①,② are not 
[3] rep 2 : choose only the time that does not overlap from rep 1
[4] rep 3 : the time that overlaps. The reverse of rep 2

#images of the time that overlaps
![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/554f4da6-13fc-678f-ec80-3e1574595109.png)

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/92db998b-dd58-dbf7-0c27-44226136a288.png)

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ce7f6e1c-064a-d306-fb71-92caf2c504b7.png)

#Proc table
```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');
```

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/32ef35ec-576a-65a4-5504-73e5efd87797.png)

Dr.Baker and Dr.Dow. There are two doctors. Since the start time and end time are different,
some job has overlap working time with other jobs.
find jobs that overlaps with other jobs by SQL

#CROSS JOIN | Multiplication of data
Creating the combination of all data using CROSS JOIN
Since multiplication of data, you need to be careful when the data is large.
I don't think you will use it often, but it may be necessary.

below is the image of CROSS JOIN

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9e812ee9-2df8-cb12-160d-2683f2862a18.png)

# Creating original data to be aggregated
Dr.Baker has two jobs(proc_id) 10 and 20. All combinations are 2 x 2 = 4
Dr.Dow has six jobs(proc_id) 30,40,50,60,70 and 80. All combinations are 6 x 6 = 36.

Let's make all combinations of data using CROSS JOIN
from above image,
proc_id(one color) in the left side has all combinations of proc_id(six colors) in the right side

**Judging whether the time on the right side(P2) overlaps with the time on the left side(P1)**

left image is all combinations of data
right image is the time that overlaps with conditions

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4eb97b02-669f-4458-76e2-49d815e1ae37.png)

-----

![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f8d0fa0c-f67f-4e5a-c490-a2d4955d06ce.png)

# Reshape time data
use CTE to create like VIEW data

Add +1 to the starting data
Add -1 to the ending data

combine those data using UNION vertically

please watch carefully ・・・

the top side of UNION, `P2.start_time is named AS 't1'`
the bottom side of UNION, `P2.end_time is named AS 't1'`

P2.start_time and P2.end_time are lined horizontally ・・・
**below process make those data line vertically**
so that the amount of data become twice

```SQL:SQL
WITH Events AS
(
   SELECT P1.proc_id AS a1, P2.proc_id AS a2, P1.anest_name
        , P2.start_time AS t1 -------------------------------------- start_time
        , +1 AS event_type ----------------------------------------- plus 1
   FROM Procs AS P1, Procs AS P2
   WHERE P1.anest_name = P2.anest_name AND -- join with the same Dr name
      NOT (P2.end_time <= P1.start_time OR P2.start_time >= P1.end_time)
       --overlap conditions
   UNION

   SELECT P1.proc_id AS a1, P2.proc_id AS a2, P1.anest_name
        , P2.end_time AS t1 ---------------------------------------- end_time
        , -1 AS event_type  ---------------------------------------- minus 1
   FROM Procs AS P1, Procs AS P2
   WHERE P1.anest_name = P2.anest_name AND -- join with the same Dr name
      NOT (P2.end_time <= P1.start_time OR P2.start_time >= P1.end_time)
       --overlap conditions
)

SELECT * FROM Events
```

the output from Events created by CTE is the left side of table image
　⇒ top side is sart_time data, bottom side of end_time data

the right side of table image is sorted by proc_id, t1(time)

![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/3c65c182-1448-c819-9853-2bf676380677.png)

# Sum overlapping times
```SQL:SQL
WITH Events AS (
 -- see above SQL
)
SELECT E1.anest_name,E1.a1 AS prod_id, E1.t1 AS time,
 (SELECT SUM(E2.event_type) FROM Events AS E2 WHERE E2.a1 = E1.a1 AND E2.t1 < E1.t1) AS goukei,
 (SELECT array_agg(E2.t1) FROM Events AS E2 WHERE E2.a1 = E1.a1 AND E2.t1 < E1.t1) AS jikan
FROM  Events AS E1
```

there is a field named 'jikan'. the conditions to get the jikan is below
　⇒ WHERE E2.a1 = E1.at AND E2.t1 < E1.t1 
　　 1 : join with the same anest_name
　　 2 : choose smaller E2.t1(start) rather than E1.t1(start)

please watch carefully ・・・

At beginning, you created original data to aggregate using CROSS JOIN
the data have all combinations of data 

So that there are combinations of the same starting job data
**You do not need to have those time to count as overlap time.**
　⇒This means that combination of start at 8:00 and start at 8::00 should not be count

what you are going to get are ・・・

the count of the time that overlaps for each proc_id
so you should count SUM(E2.event_type)
　⇒ I hope the image below will help you to imagine what you should count

　・combine data with start and end times using UNION, then sorted by proc_id
　・E2.t1 has the time that overlaps for each E1.t1
　・pick all E2.t1 which are smaller than E1.t1 (do not include the same time)
　　　⇒ array_agg(E2.t1) is the time
　・sum all E2.evnt_type that each array_agg(E2.t1) time has

![rep_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6b039ccc-079a-2a98-ad89-54f22f74ae93.png)

#answer：Number of overlap time
the answer of #3 puzzle is below(max overlap time count for each proc_id)

```SQL:SQL
WITH Events AS (
	SELECT 
		P1.proc_id AS a1, 
		P2.proc_id AS a2, 
		P1.anest_name,
		P2.start_time AS t1, 
		+1 AS event_type
	FROM Procs AS P1, Procs AS P2
	WHERE 
		P1.anest_name = P2.anest_name AND 
		NOT (P2.end_time <= P1.start_time OR P2.start_time >= P1.end_time)

	UNION

	SELECT 
		P1.proc_id AS a1, 
		P2.proc_id AS a2, 
		P1.anest_name,
		P2.end_time AS t1,
		-1 AS event_type
	FROM Procs AS P1, Procs AS P2
	WHERE
		P1.anest_name = P2.anest_name AND
		NOT (P2.end_time <= P1.start_time OR P2.start_time >= P1.end_time)
)

SELECT b1.proc_id,MAX(cnt) AS cnt
FROM
(
	SELECT E1.a1 AS proc_id, E1.t1 AS event_time,
		  (SELECT SUM(E2.event_type) FROM Events AS E2 
                   WHERE E2.a1 = E1.a1 AND E2.t1 < E1.t1) AS cnt
	FROM Events AS E1
) AS b1

GROUP BY b1.proc_id 
```
|proc_id |cnt |
|---|---|
|10|2|
|20|2|
|30|3|
|40|3|
|50|3|
|60|3|
|70|2|
|80|1|

# References
[Joe Celko's SQL Puzzles and Answers]
(https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)