1661. Average Time of Process per Machine ⭐️

문제

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

---

문제 설명

• 공장 웹사이트에서는 여러 대의 기계가 각각 같은 수의 프로세스를 실행합니다.
• 각 프로세스는 시작(start)과 종료(end) 시간(timestamp)이 기록되어 있습니다.
• 각 기계(machine_id)에 대해, 해당 기계가 프로세스를 완료하는 데 걸리는 평균 시간(processing_time)을 구하세요.
• 처리 시간 계산 방법
  • 각 프로세스 처리 시간 = end.timestamp - start.timestamp
  • 평균 처리 시간 = 각 기계의 모든 처리 시간의 합 / 프로세스 수
  • 결과는 소수점 셋째 자리까지 반올림

---

정답 코드

WITH start AS (
    SELECT * FROM activity WHERE activity_type = 'start'
),
end AS (
    SELECT * FROM activity WHERE activity_type = 'end'
)

SELECT 
    start.machine_id, 
    ROUND(AVG(end.timestamp - start.timestamp), 3) AS processing_time
FROM start
JOIN end 
    ON start.machine_id = end.machine_id 
   AND start.process_id = end.process_id
GROUP BY start.machine_id

---

코드 설명

-- 'start'라는 임시 테이블(CTE)을 생성하여 activity_type이 'start'인 행만 선택
WITH start AS (
    SELECT * FROM activity WHERE activity_type = 'start'
),

-- 'end'라는 임시 테이블(CTE)을 생성하여 activity_type이 'end'인 행만 선택
end AS (
    SELECT * FROM activity WHERE activity_type = 'end'
)

-- 본문 SELECT 문: 각 machine_id에 대해 평균 처리 시간을 계산
SELECT 
    start.machine_id,  -- 기계 ID 출력
    ROUND(AVG(end.timestamp - start.timestamp), 3) AS processing_time  -- end와 start 시간 차이의 평균을 계산하여 소수점 셋째 자리까지 반올림
FROM start

-- 'start'와 'end'를 process_id와 machine_id를 기준으로 조인하여 같은 프로세스의 start와 end를 연결
JOIN end 
    ON start.machine_id = end.machine_id 
   AND start.process_id = end.process_id

-- 각 machine_id별로 그룹화하여 평균 처리 시간을 계산
GROUP BY start.machine_id

---

개선된 코드

WITH paired_times AS (
    SELECT 
        a.machine_id,
        a.process_id,
        MAX(CASE WHEN a.activity_type = 'end' THEN a.timestamp END) -
        MAX(CASE WHEN a.activity_type = 'start' THEN a.timestamp END) AS duration
    FROM activity a
    GROUP BY a.machine_id, a.process_id
)

SELECT 
    machine_id,
    ROUND(AVG(duration), 3) AS processing_time
FROM paired_times
GROUP BY machine_id

---

코드 설명

-- CTE(Common Table Expression)를 정의: 각 공정의 처리 시간을 계산
WITH paired_times AS (
    SELECT 
        a.machine_id,     -- 기계 ID
        a.process_id,     -- 공정 ID
        -- 같은 process에서 'end' timestamp - 'start' timestamp를 계산해 처리 시간(duration)을 구함
        MAX(CASE WHEN a.activity_type = 'end' THEN a.timestamp END) -
        MAX(CASE WHEN a.activity_type = 'start' THEN a.timestamp END) AS duration
    FROM activity a
    GROUP BY a.machine_id, a.process_id  -- 각 기계와 공정별로 그룹화하여 처리 시간 계산
)

-- CTE 결과를 바탕으로, 각 기계별 평균 처리 시간을 계산
SELECT 
    machine_id,                                 -- 기계 ID
    ROUND(AVG(duration), 3) AS processing_time  -- 처리 시간의 평균을 구해 소수점 셋째 자리까지 반올림
FROM paired_times
GROUP BY machine_id                             -- 기계별로 그룹화

---

MAX(CASE WHEN a.activity_type = 'end' THEN a.timestamp END) -
MAX(CASE WHEN a.activity_type = 'start' THEN a.timestamp END) AS duration

---

첫 번째 시도 (실패)

WITH start AS (SELECT *
FROM activity
WHERE activity_type = 'start'),

end AS (SELECT *
FROM activity
WHERE activity_type = 'end')

SELECT machine_id, ROUND(AVG(end.timestamp - start.timestamp), 3) AS processing_time
FROM start JOIN end ON start.machine_id = end.machine_id
GROUP BY machine_id, process_id

---

틀린 이유

1. JOIN 조건이 부정확

• 현재는 start.machine_id = end.machine_id 로만 조인
• 이렇게 하면 같은 머신 내 모든 start와 end가 조합 가능한 모든 방식으로 조인되며 잘못된 결과를 만듦
  
  → JOIN end ON start.machine_id = end.machine_id AND start.process_id = end.process_id

2. GROUP BY가 잘못됨

• 이건 각 (machine, process) 별 행이 나와서 평균이 아닌 개별 처리시간을 보여주게 됨
• 하지만 문제에서 원하는 건 machine_id 별 평균 처리시간
  
  → GROUP BY start.machine_id
      (GROUP BY machine_id 도 가능하지만, 모호성 때문에 오류 날 수 있으므로 구체적으로 명시)