셀프조인

문제

문제를 푼 사람들을 보던 중에 하나의 테이블 스스로를 조인해서 푸는 걸 발견했다.

셀프 조인

SELECT 
    w1.id
FROM 
    Weather w1
JOIN 
    Weather w2
ON 
    DATEDIFF(w1.recordDate, w2.recordDate) = 1
WHERE 
    w1.temperature > w2.temperature;

원래는 셀프조인도 일반적인 JOIN형태처럼 ON a.common_column = b.common_column 형태로 join을 해주어야 한다.

위의 답은 DATEDIFF(w1.recordDate, w2.recordDate) = 1의 절을 통해 w1의 테이블에서는 하루 뒤의 날, w2에는 하루 전날의 데이터들이 남게 됨

일반적으로는 조인할때 두 테이블의 한 컬럼이 같다!는 식으로 하지만 위에서는 반반 나눈셈

위와 같은 방식으로 셀프조인을 하면 간결하게 코드를 짤 수 있다.

그럼 항상 셀프조인이 옳을까?

셀프조인으로도 셀프조인이 아닌 방법으로도 풀 수 있는 문제

select a.machine_id, 
		round(avg(b.timestamp - a.timestamp), 3) as processing_time 
from activity a join activity b
on a.machine_id = b.machine_id and a.process_id = b.process_id and a.activity_type = 'start' and b.activity_type = 'end'
group by 1;

이런식으로 셀프조인을 할 경우, 모든 컬럼이 동일하다고 표시해줘야 하는 불편한 일이 생긴다. 컬럼이 엄청나게 늘어날 경우 비효율적이게 된다.

다른 풀이

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

• start일 경우에는 -1, 아닐경우에는(end)에는 1을 곱해서 합해준다. = 실질적으로 타임스탬프 뺄셈
• 이후 distinct process_id로 나눈다음
• round 사용해서 소숫점 3자리까지 남김

상대적으로 작성에 있어 간결하게 할 수 있다.

아직 언제 self joind을 써야할지 말아야할지 헷갈리는데 헷갈리지 않는 날이 오기를..