기계당 평균 처리 시간

기계당 평균 처리 시간

문제

각 기계가 프로세스를 완료하는 데 걸리는 평균 시간을 구하는 해결책 쿼리를 작성

 

입력 테이블

1. Activity
   • machine_id
   • process_id
   • activity_type
   • timestamp

 

풀이

1. machine_id별 process_id별 end-start 구하기

lead() : 다음행 값 가져오기
partition by : machine_id별, process_id별 집계
order by : 'start' 명시적으로 1 지정 'end' 명시적 2 지정

 select *,
    lead(timestamp) over (partition by machine_id, process_id order by 
            CASE WHEN activity_type = 'start' THEN 1
                WHEN activity_type = 'end' THEN 2 
           END ASC) - timestamp as time_diff
from Activity

 

2. GROUP BY 및 집계 함수 사용

avg(time_diff)를 사용해 machine_id별 평균 구하기

select machine_id, round(avg(time_diff), 3) as processing_time 
from
(
    select *,
        lead(timestamp) over (partition by machine_id, process_id order by activity_type asc) - timestamp as time_diff
    from Activity
) a
group by machine_id