[LeetCode] SQL 50 - 1661. Average Time of Process per Machine (MySQL)

문제 설명

- 테이블 명 : Activity

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

- 테이블 설명 :

이 테이블은 공장 웹사이트의 사용자 활동에 대한 테이블 입니다.
(machine_id, process_id, activity_type)은 이 테이블의 기본 키 입니다.
machine_id는 기계의 ID입니다.
process_id는 machine_id를 가지고 있는 기계에서 실행 중인 프로세스의 ID입니다.
activity_type 은 ('start', 'end') 유형의 ENUM(범주)입니다.
timestamp는 현재 시간을 초 단위로 나타내는 부동소수점입니다.
'start'는 기계가 주어진 timestamp에서 프로세스를 시작한다는 것을 의미하고 'end'는 기계가 주어진 timestamp에서 프로세스를 종료한다는 것을 의미합니다.
' start ' timestamp는 항상 모든 (machine_id, process_id) 쌍의 ' end' timestamp 이전입니다.


- 문제 :

각각 동일한 수의 프로세스를 실행하는 여러 컴퓨터가 있는 공장 웹사이트가 있습니다. 각 기계가 프로세스를 완료하는 데 걸리는 평균 시간을 구하는 솔루션을 작성하세요.

프로세스를 완료하는 데 걸리는 시간은 ' end' timestamp에서 ' start ' timestamp를 뺀 시간입니다. 평균 시간은 머신의 모든 프로세스를 완료하는 데 걸린 총 시간을 실행된 프로세스 수로 나누어 계산합니다.

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

순서 상관 없이 결과를 반환합니다.

 

입출력 예시

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           |
+------------+-----------------+

- 설명 :

각각 2개의 프로세스를 실행하는 3개의 머신이 있습니다.
Machine 0의 평균 시간은 ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894 입니다.
Machine 1의 평균 시간은 ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995 입니다.
Machine 2의 평균 시간은 ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456 입니다.

 

풀이

-- join 사용 runtime 237ms

select a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) as processing_time
from Activity a1
join Activity a2
using (machine_id, process_id)
where a1.activity_type = 'start'
and a2.activity_type = 'end'
group by machine_id
;

 

노트 (ROUND 함수)

ROUND 함수 :
지정된 소수 자릿수로 반올림

Syntax :
ROUND(숫자, 소수점)

예시 : 

SELECT ROUND(345.156, 2);

-- result :
-- 345.16

참고 : https://www.w3schools.com/mysql/func_mysql_round.asp

W3Schools.com

 

다른 풀이

-- 서브쿼리 사용 runtime 214 ms

select a.machine_id,
round(
      (select avg(a1.timestamp) from Activity a1 where a1.activity_type = 'end' and a1.machine_id = a.machine_id) - 
      (select avg(a1.timestamp) from Activity a1 where a1.activity_type = 'start' and a1.machine_id = a.machine_id)
,3) as processing_time
from Activity a
group by a.machine_id
;

 

 

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