[SQL 문제 풀이] Average Time of Process per Machine (기계당 평균 처리 시간)

Stupefyee's avatar
May 28, 2025
[SQL 문제 풀이] Average Time of Process per Machine (기계당 평균 처리 시간)
Average Time of Process per Machine - LeetCode
Can you solve this real interview question? Average Time of Process per Machine - Table: Activity +----------------+---------+ | Column Name | Type | +----------------+---------+ | machine_id | int | | process_id | int | | activity_type | enum | | timestamp | float | +----------------+---------+ The table shows the user activities for a factory website. (machine_id, process_id, activity_type) is the primary key (combination of columns with unique values) of this table. machine_id is the ID of a machine. process_id is the ID of a process running on the machine with ID machine_id. activity_type is an ENUM (category) of type ('start', 'end'). timestamp is a float representing the current time in seconds. 'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp. The 'start' timestamp will always be before the 'end' timestamp for every (machine_id, process_id) pair. It is guaranteed that each (machine_id, process_id) pair has a 'start' and 'end' timestamp.   There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process. The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run. The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places. Return the result table in any order. The result format is in the following example.   Example 1: 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 | +------------+-----------------+ Explanation: There are 3 machines running 2 processes each. Machine 0's average time is ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894 Machine 1's average time is ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995 Machine 2's average time is ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456
Average Time of Process per Machine - LeetCode
notion image
여러 대의 기계가 각각 동일한 수의 공정을 실행하는 공장 웹사이트가 있습니다. 각 기계가 공정을 완료하는 데 걸리는 평균 시간을 구하는 솔루션을 작성합니다. 프로세스를 완료하는 데 걸리는 시간은 '종료' 타임스탬프에서 '시작' 타임스탬프를 뺀 값입니다. 평균 시간은 기계에서 모든 프로세스를 완료하는 총 시간을 실행된 프로세스 수로 나눈 값으로 계산됩니다. 결과 테이블은 machine_id와 평균 시간을 processing_time으로 가져야 하며, 이는 소수점 셋째 자리로 반올림되어야 합니다. 결과 테이블을 순서에 상관없이 반환하세요.
 

내가 작성한 쿼리

MySQL, Oracle

-- start, end 별로 그룹화 SELECT a.machine_id, ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time FROM Activity a -- start 담당 JOIN Activity b -- end 담당 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;
Share article

stupefyee