[Leetcode] SELF JOIN 다중조건

 

1661. Average Time of Process per Machine

 

activity_type: start, end

machine_id별 start~end 소요시간 차의 평균을 구하는 문제였다.

그런데 machine_id별로 process가 여러개라서 그냥 machine_id로 groupby를 한 뒤, 차이를 구하기가 쉽지 않았다.

 

[해결 방법1]

SELECT a1.machine_id,
       ROUND(AVG(a2.timestamp - a1.timestamp),3) AS processing_time
FROM activity a1
JOIN activity a2 -- SELF JOIN
    ON a1.machine_id = a2.machine_id
   AND a1.process_id = a2.process_id
   AND a1.activity_type = 'start' -- start인 행 추출
   AND a2.activity_type = 'end' -- end인 행 추출
GROUP BY a1.machine_id

 

1. SELF JOIN으로 activity테이블을 조인시킨 뒤, 한 테이블에서는 start인 행을, 나머지 한 테이블에서는 end인 행만 추출한다.

2. end - start를 한 값의 평균을 계산한다.

 

 

JOIN에서 연결해줄 컬럼만 지정하지 않고 추가로 각 테이블의 activity_type을 지정해주면 된다.

 

 

[해결방법2]

SELECT a.machine_id,
ROUND(
      (SELECT AVG(a1.timestamp) -- end 시간
       FROM activity a1
       WHERE a1.activity_type = 'end' AND a1.machine_id = a.machine_id) - 
      (SELECT AVG(a1.timestamp) -- start 시간
       FROM activity a1
       WHERE a1.activity_type = 'start' AND a1.machine_id = a.machine_id)
	 ,3) as processing_time
FROM Activity a
GROUP BY a.machine_id

처음에 이렇게 짰었는데, SELECT 안에 직접 SELECT문을 또 적는게 복잡하기도 하고 쿼리가 깔끔하지가 않은 것 같아서 다른 방법을 찾아봤다.

해결방법1이 나중에 쿼리를 볼 때 이해하기가 더 쉬운 방법이라는 생각이 든다.

 

 

 

---

197. Rising Temperature

 

이 문제도 비슷하게 SELF JOIN에 조건을 걸어서 푸는 문제로, 전 날 기온보다 오늘의 기온이 더 높은 id를 찾는 것이었다.

 

SELECT w1.id
FROM weather w1
JOIN weather w2 ON w1.recordDate = w2.recordDate + INTERVAL 1 DAY -- w2의 날짜는 w1의 하루 전
WHERE (w1.temperature - w2.temperature) > 0

날짜 column을 하루 다르게 SELF JOIN해서 전 날과 오늘의 기온을 다른 column으로 만들어 주고 그 column들의 차가 0 이상인 id만 조회하면 된다.