SQL > leetcode 1661. Average Time of Process per Machine (MySQL)

문제

동일한 수의 프로세스를 실행하는 컴퓨터가 있다. 각 기계가 프로세스를 완료하는 데 걸리는 평균 시간을 구하시오.

결과 테이블에는 machine_id와 함께 평균 시간이 표시되어야 하며, 소수점 이하 3자리에서 반올림 되어야한다.

 

테이블

[Activity]

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

 

[예제]

Input: 
Activity table:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+
Output: 
+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+

 

풀이

자기 자신을 Join하기

아이디어는 machine_id와 process_id의 end-start를 우선 구하고 그룹별 평균값으로 결과를 도출하는 것이다. 단계적으로 들어가본다.

1. machine_id와 process_id의 end-start를 구하기위해 조인하기

SELECT *
FROM Activity a1
JOIN Activity 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';

(아 참고로 쿼리 튜닝 링크드인 글을 보았는데 Limit을 걸어주는 것이 실제 환경에서는 꼭 필요하다고 한다.) 여튼 결과는 아래와 같다.

| machine_id | process_id | activity_type | timestamp | machine_id | process_id | activity_type | timestamp |
| ---------- | ---------- | ------------- | --------- | ---------- | ---------- | ------------- | --------- |
| 0          | 0          | start         | 0.712     | 0          | 0          | end           | 1.52      |
| 0          | 1          | start         | 3.14      | 0          | 1          | end           | 4.12      |
| 1          | 0          | start         | 0.55      | 1          | 0          | end           | 1.55      |
| 1          | 1          | start         | 0.43      | 1          | 1          | end           | 1.42      |
| 2          | 0          | start         | 4.1       | 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.5       | 2          | 1          | end           | 5         |

 

2. Start timestamp와 end timestamp의 차이를 구하고 이를 머신의 아이디 별로 그룹을 묶어준다.

이 떄 주의할것이 프로세스 별로 값이 묶여있기 떄문에 프로세스 별 처리 시간의 평균을 구하는 집계함수 AVG를 같이 써야한다.

SELECT a1.machine_id, AVG(a2.timestamp - a1.timestamp)
FROM Activity a1
JOIN Activity 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;

| machine_id | AVG(a2.timestamp - a1.timestamp) |
| ---------- | -------------------------------- |
| 0          | 0.8939998745918274               |
| 1          | 0.9949999451637268               |
| 2          | 1.4560000896453857               |

 

3. 마지막으로 잊지말고 소수점 3번째 자리까지 표시하기위해 ROUND를 사용해주면된다.

SELECT a1.machine_id, ROUND(AVG(a2.timestamp-a1.timestamp),3) AS processing_time
FROM Activity a1
JOIN Activity 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;

| machine_id | processing_time |
| ---------- | --------------- |
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |

 

---

조금 오래 걸린 문제이다. 이게 다른 방법으로 풀수있는데 CASE WHEN과 DISTINCT를 사용해서 진행하는 방법도있다.

SELECT 
    machine_id,
    ROUND(SUM(CASE WHEN activity_type='start' THEN timestamp*-1 ELSE timestamp END)*1.0
    / (SELECT COUNT(DISTINCT process_id)),3) AS processing_time
FROM 
    Activity
GROUP BY machine_id

 

여튼 틀릴때마다 꾸준하게 다시 푸는데 실력이 점점 올라오는 것 같아 좋다.