[코드카타 스터디]sql_6(85번, 86번 문제)

문제링크

85.https://leetcode.com/problems/rising-temperature/

86.https://leetcode.com/problems/average-time-of-process-per-machine/

문제.85.Write a solution to find all dates' id with higher temperatures compared to its previous dates (yesterday).

쿼리비교

1.
SELECT W2.ID
FROM Weather w1 join Weather w2
ON DATEDIFF(w2.recordDate, w1.recordDate) = 1
WHERE w1.temperature < w2.temperature;

2.
SELECT t.id
FROM weather t left join weather y
ON datediff(t.recorddate, y.recorddate) = 1
WHERE t.temperature > y.temperature

3.
select w1.id
from Weather w1 left join Weather w2 on w1.id = w2.id + 1 
where w1.temperature > w2.temperature


4.
SELECT A1.ID
FROM WEATHER A1 JOIN WEATHER A2
ON DATEDIFF(A1.RECORDDATE, A2.RECORDDATE) = 1
WHERE A1.TEMPERATURE > A2.TEMPERATURE

best

select w1.id
from Weather w1 left join Weather w2 
on w1.id = w2.id + 1 
where w1.temperature > w2.temperature

익숙하지 않는 셀프조인을 생각하는게 일단 어려웠고,
on절에 datediff같은 조건이 들어가도 되는지 몰랐다.
그리고 조금더 단순하게 on w1.id = w2.id + 1 이런식으로 표현이 가능하다는 것을 스터디를 통해 배웠다.

문제86.There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.

코드 비교

1.
SELECT a.machine_id, 
       ROUND(AVG(b.timestamp - a.timestamp),3) processing_time
FROM Activity a JOIN Activity b
ON a.process_id = b.process_id 
and a.machine_id = b.machine_id
where a.activity_type = 'start' and b.activity_type = 'end'
GROUP BY 1;

2.
SELECT a.machine_id, round(avg(b.timestamp-a.timestamp), 3) as processing_time
FROM activity a JOIN activity b 
ON a.machine_id=b.machine_id AND a.activity_type = 'start' AND b.activity_type = 'end'
GROUP BY 1;

3.
select machine_id, round((max(ts) - min(ts)) / cnt, 3) processing_time
from
		( select machine_id, activity_type, sum(timestamp) ts, count(*) cnt
			from Activity 
			group by machine_id, activity_type 
		 ) sq1
group by machine_id 

4.
SELECT MACHINE_ID, ROUND(AVG(DIF),3) AS PROCESSING_TIME
FROM(SELECT *, 
        TIMESTAMP - LAG(TIMESTAMP) OVER(ORDER BY MACHINE_ID, PROCESS_ID, ACTIVITY_TYPE) DIF 
    FROM ACTIVITY
    )Q1
WHERE ACTIVITY_TYPE = 'end'
GROUP BY MACHINE_ID

best

SELECT a.machine_id, 
       ROUND(AVG(b.timestamp - a.timestamp),3) processing_time
FROM Activity a JOIN Activity b
ON a.process_id = b.process_id 
and a.machine_id = b.machine_id
where a.activity_type = 'start' and b.activity_type = 'end'
GROUP BY 1;

한번에 정리하기에는 조인을 사용한것이 가독성이 좋다고 판단.
셀프조인까지는 생각할 수 있지만, 조건절의 조건을 생각하는게 가장어려운 문제였던것 같다.

그리고 성능적으로 볼땐, lag()함수를 사용하는것이 좀더 빠르게 결과를 볼 수 있는 방법같다.

사용문법

on절 + DATEDIFF(): 다양한 조건이 올 수 있음.

on절 뒤에 +1 처럼 연산해서 조건을 맞출 수 있다.

셀프조인: table a join table b

LAG() OVER()