MySQL 리트코드 | Average Time of Process per Machine

 

문제

https://leetcode.com/problems/average-time-of-process-per-machine/?envType=study-plan-v2&envId=top-sql-50

• machine 별 프로세스 평균 시간 구하기
• 프로세스 완료 시간은 activity_type이 'end'인 timestamp에서 'start'인 timestamp를 빼면 됨.
• 결과는 소수점 셋째자리로 표현하기

 

테이블

Activity	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

• machine_id 별로 processing_time 구하기
• processing_time은 한 기계(machine_id) 당 process_id 별 (end 시간 - start 시간)의 평균  

 

풀이과정

# 튜플 형식으로 셀프조인, activity_type이 다른 경우 조인

• 한 기계 당 여러 프로세스들이 돌아가고 있음. 기계 별로 프로세스들의 평균 러닝타임을 구해야 함.
  • 평균 러닝타임 = 기계 별 프로세스 별 ( '마감' 시간 -  '시작' 시간)의 평균 
• 프로세스 별로 '마감'시간과 '시작' 시간을 한 행으로 보기 위해 셀프 조인을 해줌 
• 조인 조건은 ① machine_id와 process_id가 같은 경우에, ② activiy_type이 다른 경우('end', 'start')에만 조인되도록 설정 
  • 같은 machine_id와 process_id를 가지고 있으면서,
    activity_type이 'start'인 경우에는 'end'인 경우의 데이터가 추가되고,
    activity_type이 'end'인 경우에는 'start'인 경우의 데이터가 추가됨.
    → (셀프 코드 피드백) 메모리를 비효율적으로 사용하고 있음.
         하나의 machine_id와 process_id 별 start 시간과 end 시간을 표현할 때 두 행을 사용하고 있음.  

조인 조건에 activity_type이 같지 않을 때 조인되도록 설정하면 한 기계와 프로세스 당 (같은 정보를 담은) 두 행의 데이터가 생성됨.

 

# 정답

• SELECT 문으로 machine_id와 activity_type 간 차이의 절댓값의 평균을 계산한 processing_time을 조회해주기
  • 상단 풀이과정에서 서술했듯,
    같은 machine_id와 process_id 별로 activity_type이 다른 경우에 셀프 조인이 된 상태이기 때문에
    두 activity_type 데이터를 빼면 한 행은 +값이 나오고, 다른 행은 -값이 나오게 됨.

그렇기 때문에 ABS() 절댓값 함수를 사용함.

• activity_type이 다른 경우, 두 값을 빼고 절댓값을 취해줌. 그 후 평균을 구하고, ROUND 함수로 소수점 자리를 맞춰줌.
  → 다행히 평균을 구하는 문제였기 때문에 여기에서 절댓값들을 더한 뒤 1/N 했을 때 정답이 나오게 된 것. 
• 한 기계 당 프로세스 평균 러닝타임을 구하기 위해서는 machine_id로 GROUP BY 해야 함.

SELECT a1.machine_id, ROUND(AVG(ABS(a1.timestamp-a2.timestamp)),3) processing_time
FROM Activity a1
JOIN Activity a2 ON (a1.machine_id, a1.process_id) = (a2.machine_id, a2.process_id)
AND a1.activity_type != a2.activity_type
GROUP BY a1.machine_id

 

 

다른 사람 풀이

# 튜플 형식으로 셀프조인, activity_type을 지정해서 조인

• 프로세스 별로 '마감'시간과 '시작' 시간을 한 행으로 보기 위해 셀프 조인을 해줌 
• 조인 조건은 ① machine_id와 process_id가 같은 경우에, ② 왼쪽 테이블은 activity_type이 'start'이고, ③ 오른쪽 테이블은 activity_type이 'end'인 경우에 조인되도록 설정
  
  • 같은 machine_id와 process_id를 가지고 있으면서,
    왼쪽 테이블의 activity_type은 'start'이면서 오른쪽 테이블의 activity_type이 'end'일 때 데이터가 추가됨.
    → 한 machine_id와 process_id 별 시작(start)과 마감(end) 시간이 한 행에 나옴. 
• 두 timestamp 값의 차이를 구한 뒤 평균을 구하고, ROUND 함수로 소수점 자리를 맞춰줌.
• 한 기계 당 프로세스 평균 러닝타임을 구하기 위해서는 machine_id로 GROUP BY 해야 함.

SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) processing_time
FROM Activity a1
JOIN Activity a2 ON (a1.machine_id, a1.process_id) = (a2.machine_id, a2.process_id)
AND a1.activity_type = 'start' AND a2.activity_type = 'end'
GROUP BY a1.machine_id