[SQL][LeetCode] 1661. Average Time of Process per Machine - INNER JOIN + SCALAR SUBQUERY MYSQL

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

코드설명

INNER JOIN + SCALAR SUBQUERY 활용합니다.

 

문제의 접근방법입니다.

1. 평균 레코드를 구하기 위해 각 MACHINE_ID와 PROCESS_ID 별로 시간차이를 구합니다.

그렇게 되면, 

MACHINE_ID | DIFF_TIMESTAMP 와 같이 구해집니다.

그리고 그 MACHINE_ID 의 평균을 구하기 위해 다시 GROUP BY 를 해주면, 평균 차이값을 구할 수 있습니다.

 

SCALAR SUBQUERY를 활용할경우에는 부분범위처리를 사용가능합니다.

만약 MACHINE_ID 1개당 존재하는 PROCESS_ID 레코드가 매우 많을경우 비효율적입니다. 하지만 부분범위처리 내에서는 이 방식이 더 효율적일 것 입니다.

 

세번째는, (MACHINE_ID 별 종료평균시간) - (MACHINE_ID 별 시작평균시간) 을 빼면, 각 기계당 평균시간을 구할 수 있습니다.

MYSQL 코드

JOIN을 활용한 코드1입니다.

SELECT SUB1.MID AS machine_id, ROUND(AVG(SUB1.TIMESTAMP),3) AS processing_time
FROM (SELECT A1.MACHINE_ID AS 'MID'
		,(A2.TIMESTAMP - A1.TIMESTAMP) AS 'TIMESTAMP'
        FROM ACTIVITY A1, ACTIVITY A2
        WHERE A2.MACHINE_ID = A1.MACHINE_ID 
        AND A1.PROCESS_ID = A2.PROCESS_ID
        AND A1.ACTIVITY_TYPE = 'start'
        AND A2.ACTIVITY_TYPE = 'end') SUB1
GROUP BY SUB1.MID

 

SCALAR SUBQUERY를 사용한 코드2입니다.

SELECT SUB1.MACHINE_ID AS machine_id, ROUND(AVG(SUB1.TIMESTAMP), 3) AS processing_time
FROM (SELECT A1.MACHINE_ID, 
        (SELECT TIMESTAMP 
        FROM ACTIVITY A2 
        WHERE A2.MACHINE_ID = A1.MACHINE_ID 
        AND A2.PROCESS_ID = A1.PROCESS_ID 
        AND A2.ACTIVITY_TYPE = 'end')  - A1.TIMESTAMP AS TIMESTAMP
FROM ACTIVITY A1
WHERE A1.ACTIVITY_TYPE = 'start') AS SUB1
GROUP BY SUB1.MACHINE_ID

 

평균 구하는 방법을 활용한 코드입니다.

SELECT A1.MACHINE_ID 'machine_id', 
	ROUND((SELECT AVG(TIMESTAMP)
    FROM ACTIVITY A2
    WHERE A2.ACTIVITY_TYPE = 'end'
    AND A2.MACHINE_ID = A1.MACHINE_ID) 
    - (SELECT AVG(TIMESTAMP)
    FROM ACTIVITY A2
    WHERE A2.ACTIVITY_TYPE = 'START'
    AND A2.MACHINE_ID = A1.MACHINE_ID), 3) AS 'processing_time'
FROM ACTIVITY A1
GROUP BY A1.MACHINE_ID;