[leetCode] #1661

문제 - Average Time of Process per Machine

https://leetcode.com/problems/average-time-of-process-per-machine/

Average Time of Process per Machine - LeetCode

There is a factory website that has several machines each running the same number of processes. Write an SQL query 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.

 

작성 답안

select round(sum(end.timestamp - start.timestamp)/count(end.machine_id),3) as processing_time, start.machine_id
from
    (select *
    from activity as a
    where a.activity_type = 'start') start
join
    (select *
    from activity as a
    where a.activity_type = 'end') end
on start.machine_id = end.machine_id and start.process_id = end.process_id
group by start.machine_id

- activity_type이 'start'인 테이블과 'end'인 테이블을 나눈다.

- start와 end 테이블을 process_id와 machine_id 기준으로 조인하여, 동일한 머신과 프로세스에 해당하는 값을 연산에 사용하도록 한다.

- machine_id기준으로 그루핑하여 start와 end테이블의 평균 timestamp와 machine_id를 추출한다.