셀프 조인 이해하기

안녕하세요. 오늘은 셀프 조인(Self Join)에 대해 정말 간단하게 설명해볼까 합니다.

준비중인 다음 글을 위해 간단히 개념을 설명하려 합니다.

​

이 글에 포함된 SQL은 오라클 기준으로 작성되었습니다. 예제는 이전 글에서 만든 T_SALE_YM_01 테이블을 사용합니다. 연습을 위해서는 이전 글을 참고해 테이블과 데이터를 생성해주세요.

https://sweetquant.tistory.com/320

데이터 분석 - 오라클의 CORR 집계함수

 

 

자기 자신과 조인하는 것을 셀프 조인(Self Join)이라고 합니다. INNER JOIN이나 OUTER JOIN처럼 조인 문법이나 방법으로 존재하는 것이 아니라, 조인 대상에 따라 셀프 조인을 특정해서 부릅니다. 다시 말해, 'TAB1 JOIN TAB1'과 같이 TAB1끼리 조인하는 것을 셀프 조인이라고 합니다.

​

앞에서 만든 T_SALE_YM_01 테이블을 사용한 예제를 살펴보겠습니다. 아래 예제는 T_SALE_YM_01을 조회하면서, 3개월전 판매 수량을 가져오고, 현재와 3개월전 판매 수량을 비교해 판매가 얼만큼 증감했는지 조회하는 SQL입니다.

SELECT  T1.ITEM_ID
        ,T1.SALE_YM ,T1.SALE_QTY
        ,T2.SALE_YM BF3_YM ,T2.SALE_QTY BF3_QTY
        ,ROUND(T1.SALE_QTY / T2.SALE_QTY * 100,2) BF3_NW_SALE_RT
FROM    T_SALE_YM_01 T1
        LEFT OUTER JOIN T_SALE_YM_01 T2
           ON (T2.ITEM_ID = T1.ITEM_ID
               AND T2.SALE_YM = TO_CHAR(ADD_MONTHS(TO_DATE(T1.SALE_YM||'01','YYYYMMDD'),-3),'YYYYMM'))
WHERE   T1.ITEM_ID = 'M9B'
ORDER BY T1.SALE_YM;

​

위 SQL을 살펴보면, FROM 절의 LEFT OUTER JOIN을 중심으로 왼쪽과 오른쪽에 모두 T_SALE_YM_01 테이블을 사용하고 있습니다. 그러므로 셀프 조인이 됩니다. 위 SQL은 ANSI 기준 문법으로 작성했는데요, 아래와 같이 (+)만 추가하면 손쉽게 오라클 기준의 아우터 조인으로 변경할 수 있습니다.

SELECT  T1.ITEM_ID
        ,T1.SALE_YM ,T1.SALE_QTY
        ,T2.SALE_YM BF3_YM ,T2.SALE_QTY BF3_QTY
        ,ROUND(T1.SALE_QTY / T2.SALE_QTY * 100,2) BF3_NW_SALE_RT
FROM    T_SALE_YM_01 T1
        ,T_SALE_YM_01 T2
WHERE   T1.ITEM_ID = 'M9B'
AND     T2.ITEM_ID(+) = T1.ITEM_ID
AND     T2.SALE_YM(+) = TO_CHAR(ADD_MONTHS(TO_DATE(T1.SALE_YM||'01','YYYYMMDD'),-3),'YYYYMM')
ORDER BY T1.SALE_YM;

 

위 SQL의 결과를 살펴보면 아래와 같습니다.

T_SALE_YM_01(T1)                   T_SALE_YM_01(T2)      T1과 T2의 값을 계산
-----------------------------      ----------------      --------------
ITEM_ID     SALE_YM SALE_QTY       BF3_YM BF3_QTY        BF3_NW_SALE_RT
----------- ------- ---------      ------ ---------      -------------- 
M9B         202001        100      <null>    <null>              <null>
M9B         202002         50      <null>    <null>              <null>
M9B         202003         90      <null>    <null>              <null>
M9B         202004         10      202001       100                  10
M9B         202005         90      202002        50                 180
M9B         202006        100      202003        90              111.11
M9B         202007         90      202004        10                 900
M9B         202008         50      202005        90               55.56
M9B         202009         10      202006       100                  10
M9B         202010         20      202007        90               22.22
M9B         202011         30      202008        50                  60
M9B         202012         20      202009        10                 200

T1의 값들에 3개월 전 판매수량(T2)이 연결된 것을 알 수 있습니다. 이때, 2020년 1월의 3개월전인 2019년 데이터는 T_SALE_YM에 존재하지 않으므로 NULL로서 결과가 나온 것을 확인할 수 있습니다.

​

이처럼 자기 자신과 이루어지는 조인을 흔히 셀프 조인이라고 부릅니다.

위 SQL은 아래와 같이 LEAD 분석함수로도 해결 할 수 있습니다.

SELECT  T1.ITEM_ID
        ,T1.SALE_YM ,T1.SALE_QTY
        ,LEAD(T1.SALE_YM,3) OVER(ORDER BY T1.SALE_YM DESC) BF3_YM
        ,LEAD(T1.SALE_QTY,3) OVER(ORDER BY T1.SALE_YM DESC) BF3_QTY
        ,ROUND(T1.SALE_QTY / LEAD(T1.SALE_QTY,3) OVER(ORDER BY T1.SALE_YM DESC) * 100,2) BF3_NW_SALE_RT
FROM    T_SALE_YM_01 T1
WHERE   T1.ITEM_ID = 'M9B'
ORDER BY T1.SALE_YM;

​

위 SQL의 결과 역시, 셀프 조인을 사용한 SQL과 완전히 같습니다. 하지만, 분석함수의 치명적인 문제는 조회 결과만이 분석대상으로 포함되므로, 아래와 같이 특정 월 데이터를 조회하면 원하는 결과를 얻을 수 없습니다.

SELECT  T1.ITEM_ID
        ,T1.SALE_YM ,T1.SALE_QTY
        ,LEAD(T1.SALE_YM,3) OVER(ORDER BY T1.SALE_YM DESC) BF3_YM
        ,LEAD(T1.SALE_QTY,3) OVER(ORDER BY T1.SALE_YM DESC) BF3_QTY
        ,ROUND(T1.SALE_QTY / LEAD(T1.SALE_QTY,3) OVER(ORDER BY T1.SALE_YM DESC) * 100,2) BF3_NW_SALE_RT
FROM    T_SALE_YM_01 T1
WHERE   T1.ITEM_ID = 'M9B'
AND     T1.SALE_YM = '202012'
ORDER BY T1.SALE_YM;

ITEM_ID     SALE_YM SALE_QTY  BF3_YM BF3_QTY   BF3_NW_SALE_RT
----------- ------- --------- ------ --------- -------------- 
M9B         202012         20 <null>    <null>         <null>

 

결과를 보면, 2020년 12월의 3개월전 데이터인 2020년 9월 데이터가 NULL로 채워진 것을 알 수 있습니다. 'SQL BOOSTER'나 '평생 필요한 데이터 분석' 책에서 설명한 것처럼, 분석함수는 기본적으로 조회된 결과에 대해서만 분석을 수행합니다. 그러므로 2020년 9월 데이터는 위 SQL에서 조회된 결과가 아니기 때문에 분석함수의 분석 대상이 될 수 없습니다. 위와 같은 경우는 불가피하게 앞에서 설명한 셀프 조인 방식으로 처리하는 것이 좋습니다.

​

이상입니다. 오늘은 정말 짧게 살펴봤습니다. ^^ 다음 글을 설명하기 위해 간단히 셀프 조인을 설명드렸습니다.

감사합니다.

 

위와 같이 데이터를 마음대로 분석해볼 수 있는 SQL을 공부하고 싶다면 아래 책을 참고해주세요~!

※ 책 소개: https://sweetquant.tistory.com/243

※ 책 미리보기: https://sweetquant.tistory.com/257

※ 완전판 E-Book

  ▶ 유페이퍼: https://www.upaper.net/ryu1hwan/1142997

  ▶ 알라딘: https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=273701425

  ▶ Yes24: http://www.yes24.com/Product/Goods/102264444?OzSrank=1

  ▶ 교보문고: https://digital.kyobobook.co.kr/digital/ebook/ebookDetail.ink?selectedLargeCategory=001&barcode=4801167630019&orderClick=LAG&Kc=