[SQL] Average Time of Process per Machine

# 각 머신마다 평균 시간 구하기
# 시간은 end - start
# 셋째자리에서 반올림

select machine_id, round(sum(if(activity_type='start', -timestamp, timestamp))
    /count(distinct process_id), 3) as processing_time
from Activity
group by machine_id

 

process_id끼리 더한 후 나눈 거나 모두 다 더한 후 나눈 거랑 같음

activity_type이 start에 마이너스를 붙여주면 start-end할 필요없이 그냥 모두 다 더하면 됨

 

또 다른 방법

select a.machine_id, round(avg(b.timestamp - a.timestamp), 3) as processing_time 
from activity a join activity b
	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 1;

 

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