SELF JOIN 셀프 조인 (MYSQL)

테이블을 자기 자신과 조인하는 셀프 조인에 대해 알아보겠습니다. 

SELF JOIN은 한 테이블 내에서 칼럼 간, 개별 값 간 대조가 필요할 때 사용할 수 있습니다. 

 

문법은 JOIN/쉼표로 연결하고 (필수) 별칭을 붙여줍니다.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

 

 

아래 두 가지 예시로 SELF JOIN의 활용을 살펴보겠습니다. 

 

LeetCode |  197. Rising Temperature

https://leetcode.com/problems/rising-temperature/

 

하나의 테이블 내의 날짜 값과 온도 값에 대한 비교가 필요하기 때문에 self join 을 사용해서 이어줍니다.

from weather w1 join weather w2

 

문제를 풀기 위해 필요한 조건은 아래와 같습니다.

recordDate 날짜 간 대조 datediff(w2.recorddate, w1.recorddate) = 1
temperature 온도 간 대조  w2.temperature > w1.temperature

 

최종 쿼리는 아래와 같습니다.

select w2.id
from weather w1, weather w2
where datediff(w2.recorddate, w1.recorddate) = 1 and w2.temperature > w1.temperature

 

 

LeetCode |  1661. Average Time of Process per Machine

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

 

우선 제 풀이는

activity_type 이 start 이면 음수값 아니면 양수값을 출력해서, 그것을 다 더하고, process_id 개수만큼 나눠줫 평균을 구하는 방식으로 진행했습니다. 

select machine_id, 
       round(sum(if(activity_type = 'start', -timestamp, timestamp))/count(distinct process_id),3) 
       as processing_time 
from activity
group by 1 ;

 

다른 풀이는 둘러보니 SELF JOIN 을 활용해 간단한 함수로 풀이가 가능했습니다. 

아래 코드는 보면, a1 은 start, a2 는 end 를 조건으로 가진 테이블로 activity 테이블을 둘로 나눠 조인한 방식입니다.

테이블이 두 개가 생성됐기 때문에 간단하게 a2.timestamp - a1.timestamp 의 평균을 구할 수 있습니다. 

 

이때 JOIN 조건을 유의해서 machine_id, timestamp 모두에 대해 연결해줘야 제대로 매칭된 데이터로 출력됩니다. 

두 테이블을 조인한 모든 값에 대해 출력하면 아래 테이블처럼 보입니다. 

SELECT a1.machine_id, round(avg(a2.timestamp-a1.timestamp), 3) as processing_time 
FROM Activity a1 join Activity a2 
    ON a1.machine_id=a2.machine_id and a1.process_id=a2.process_id
WHERE a1.activity_type='start' AND a2.activity_type='end'
GROUP BY 1


| machine_id | process_id | activity_type | timestamp | machine_id | process_id | activity_type | timestamp |
| ---------- | ---------- | ------------- | --------- | ---------- | ---------- | ------------- | --------- |
| 0          | 0          | start         | 0.712     | 0          | 0          | end           | 1.52      |
| 1          | 0          | start         | 0.55      | 1          | 0          | end           | 1.55      |
| 2          | 0          | start         | 4.1       | 2          | 0          | end           | 4.512     |...