#84. [LeetCode / MySQL] 1661. Average Time of Process per Machine

문제

 

입력 코드

1. JOIN - AND 조건

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM Activity AS a1
    JOIN Activity AS a2
    ON (a1.machine_id = a2.machine_id) 
        AND (a1.process_id = a2.process_id) 
        AND (a1.activity_type='start') 
        AND (a2.activity_type='end')
GROUP BY a1.machine_id

 

2. JOIN

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM Activity AS a1
    JOIN Activity AS a2
    ON (a1.machine_id = a2.machine_id) 
        AND (a1.process_id = a2.process_id) 
        AND (a1.timestamp < a2.timestamp)
GROUP BY a1.machine_id

→ 프로세스를 완료하는데 걸리는 시간은 'end' timestamp - 'start' timestamp 이고, 'end' timestamp > 'start' timestamp이므로 해당 조건을 이용해서 조인

 

 

3. JOIN - USING

SELECT
    a1.machine_id,
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM Activity AS a1
    JOIN Activity AS a2
    USING (machine_id)
WHERE (a1.activity_type = 'start') 
    AND (a2.activity_type = 'end')
GROUP BY a1.machine_id

 

 

코드 설명

#SELECT #ROUND #AVG #JOIN #SELF JOIN #GROUP BY #USING

 

 

문제 출처

Average Time of Process per Machine - LeetCode