[LeetCode] average-time-of-process-per-machine

https://leetcode.com/problems/average-time-of-process-per-machine/?envType=study-plan-v2&envId=top-sql-50

Average Time of Process per Machine - LeetCode

1. machine, process별 timespent (=end-start)

SELECT a.machine_id,
	a.process_id,
	b.timestamp - a.timestamp AS timespent
FROM activity a
    LEFT JOIN activity b ON a.machine_id = b.machine_id 
		AND a.process_id = b.process_id
WHERE a.activity_type = 'start' AND b.activity_type = 'end'

start와 end를 1개의 row에 이어붙이기 위해 JOIN을 사용해줬다

 

2. 1번의 쿼리를 FROM절 서브쿼리로 사용하고, machine_id로 GROUP BY 후 processing_time을 구하기 위한 집계함수를 적절히 사용해준다.

SELECT machine_id,
    ROUND(SUM(timespent)/COUNT(process_id), 3) AS processing_time
FROM (
    SELECT a.machine_id,
        a.process_id,
        b.timestamp - a.timestamp AS timespent
    FROM activity a
        LEFT JOIN activity b ON a.machine_id = b.machine_id AND a.process_id = b.process_id
    WHERE a.activity_type = 'start' AND b.activity_type = 'end'
) AS tmp
GROUP BY machine_id