rubus0304 님의 블로그

[데이터 분석] 코드카타 86 본문

Data Analyst/daily

[데이터 분석] 코드카타 86

rubus0304 2024. 10. 23. 09:52

86. Average Time of Process per Machine

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

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.

 

select a.machine_id,
          round(avg(b.timestamp-a.timestamp),3) 'processing_time'
from activity a join activity b using(machine_id, process_id)
where a.activity_type = 'start' and b.activity_type = 'end'
group by machine_id
 
자기 자신 Join 으로 따로 테이블로 빼서 다시 사용하는 문제 또 보이네
이번엔 join에서 id들만 사용한다 하고
timstamp들끼리 빼는 건 되네  using 처음 보는 건데..?
헐 테이블 뺀게 end type을 b 테이블로 뺀 거임 그래서 빼기 할려고..!!
 
 
이게 좀더 직관적으로 보임!!
select a.machine_id,
          round(avg(b.timestamp - a.timestamp),3) '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 a.machine_id