SQL풀이: Leetcode 1661. Average Time of Process per Machine

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

 

문제

machine_id와 기계 종류별로 처리 평균 시간을 구하면 되는 문제로 평균 시간은 소수 3째자리에서 반올림해주어야 한다. 

방법1 

select machine_id, 
       round(sum(case when activity_type = 'start' then - timestamp else timestamp end) / count(distinct process_id), 3) as processing_time
from Activity
group by machine_id;

 

방법2

테이블을 두 개로 분리해서 푸는 방법

SELECT a.machine_id, 
       ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time
FROM Activity a, 
     Activity b
WHERE 
    a.machine_id = b.machine_id
AND 
    a.process_id = b.process_id
AND 
    a.activity_type = 'start'
AND 
    b.activity_type = 'end'
GROUP BY machine_id

 

방법3

셀프 조인을 해서 푸는 방법 

Select a.machine_id , 
       ROUND(avg(b.timestamp - a.timestamp),3) as processing_time
FROM Activity as a
JOIN Activity as b
              ON a.machine_id = b.machine_id 
              and a.process_id = b.process_id 
              and a.activity_type = 'start' 
              and b.activity_type = 'end'
GROUP BY a.machine_id;

 

[MySQL] SELF JOIN 셀프 조인을 쓰는 이유

참고.