카테시안 조인 이해하기

지난 글에서는 셀프조인에 대해 살펴봤습니다.

 

https://sweetquant.tistory.com/322

셀프 조인 이해하기

 

셀프조인에 이어서, 카테시안 조인에 대해 살펴보도록 하겠습니다.

발생하지 않은 실적 데이터를 분석하기 위해서는 임의로 분석 차원(분석 대상 집합)을 만들어줄 필요가 있습니다. 이를 위해 필요한 것이 바로 카테시안 조인입니다.

​

카테시안 조인은 A 집합의 데이터 각각이 B 집합의 모든 데이터와 조인되는 것을 뜻합니다.

SQL BOOSTER에서는 아래 그림을 사용해 카테시안 조인을 설명합니다. CUS_GD 집합 두 건이 ITM_TP 집합 네 건과 모두 조인 처리되어, 3번과 같이 총 8건의 데이터가 만들어집니다.

SQL BOOSTER의 카테시안 조인 설명을 위한 그림

 

카테시안 조인 연습을 위해 아래와 같이 두 개의 테이블을 생성합니다. (앞에 글에서 만든 T_SALE_YM_01 테이블처럼 오라클에서 테이블을 생성합니다. T_SALE_YM_01과 연계해서 테스트를 할 예정입니다.)

​

CREATE TABLE M_ITEM_01
(
   ITEM_ID VARCHAR2(100) NOT NULL
   ,ITEM_NM VARCHAR2(100) NOT NULL
   ,ITEM_CTG_CD VARCHAR2(100) NOT NULL
   ,COLR_CD VARCHAR2(100) NULL
   ,SIZE_MM NUMBER(18,6) NULL
);
ALTER TABLE M_ITEM_01 ADD CONSTRAINT PK_M_ITEM_01 PRIMARY KEY(ITEM_ID);

CREATE TABLE M_YM_01
(
    YM VARCHAR2(6) NOT NULL
);
ALTER TABLE M_YM_01 ADD CONSTRAINT PK_M_YM_01 PRIMARY KEY(YM);

아이템 마스터를 저장하는 M_ITEM_01과 연월 값을 저장하는 M_YM_01 테이블을 생성했습니다. 만들어진 테이블에 데이터를 입력해야 합니다. M_ITEM_01에는 아래 스크립트를 사용해 데이터를 입력합니다.

INSERT INTO M_ITEM_01(ITEM_ID ,ITEM_NM ,ITEM_CTG_CD ,COLR_CD ,SIZE_MM) VALUES('M9B', 'Mobile9B' ,'MOBILE' ,'BLUE' ,158.4);
INSERT INTO M_ITEM_01(ITEM_ID ,ITEM_NM ,ITEM_CTG_CD ,COLR_CD ,SIZE_MM) VALUES('M9R', 'Mobile9R' ,'MOBILE' ,'RED' ,158.4);
INSERT INTO M_ITEM_01(ITEM_ID ,ITEM_NM ,ITEM_CTG_CD ,COLR_CD ,SIZE_MM) VALUES('W3B', 'Watch3B'  ,'SMARTWATCH' ,'BLUE' ,44);
INSERT INTO M_ITEM_01(ITEM_ID ,ITEM_NM ,ITEM_CTG_CD ,COLR_CD ,SIZE_MM) VALUES('G1B', 'Glass1B'  ,'SMARTGLASS' ,'BLUE' ,68);
COMMIT;

M_YM_01에는 아래와 같이 CONNECT BY를 사용해 2019년 1월부터 2021년 12월까지의 데이터를 입력합니다.

INSERT INTO M_YM_01(YM)
SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20190101','YYYYMMDD'),ROWNUM-1),'YYYYMM') YM
FROM   DUAL T1
CONNECT BY TO_CHAR(ADD_MONTHS(TO_DATE('20190101','YYYYMMDD'),ROWNUM-1),'YYYYMM') <= '202112'
COMMIT;

데이터 입력을 완료했으면, 우선은 아래 SQL을 사용해 T_SALE_YM_01의 데이터를 2019년 12월부터 2020년 2월까지 조회해봅니다.

SELECT  TSAL.SALE_YM
        ,TSAL.ITEM_ID
        ,TSAL.SALE_QTY
FROM    T_SALE_YM_01 TSAL
WHERE   TSAL.SALE_YM BETWEEN '201912' AND '202002'
ORDER BY TSAL.SALE_YM ,TSAL.ITEM_ID;
[결과]
SALE_YM ITEM_ID     SALE_QTY 
------- ----------- --------- 
202001  G1B                18
202001  M9B               100
202001  M9R                45
202001  W3B                30
202002  G1B                15
202002  M9B                50
202002  M9R                25
202002  W3B                19

결과를 보면 2019년 12월 데이터가 존재하지 않습니다. T_SALE_YM_01에는 2020년 데이터만 존재하기 때문입니다. 판매가 없는 2019년 12월 데이터도 0으로 보여주고 싶다면, 아래와 같이 M_YM_01과의 아우터 조인을 고려해야 합니다.

SELECT  T1.YM
        ,TSAL.ITEM_ID
        ,TSAL.SALE_QTY
FROM    M_YM_01 T1
        ,T_SALE_YM_01 TSAL
WHERE   T1.YM BETWEEN '201912' AND '202002'
AND     TSAL.SALE_YM(+) = T1.YM
ORDER BY T1.YM ,TSAL.ITEM_ID;
[결과]
YM     ITEM_ID       SALE_QTY 
------ ------------- --------- 
201912 <null>           <null>
202001 G1B                  18
202001 M9B                 100
202001 M9R                  45
202001 W3B                  30
202002 G1B                  15
202002 M9B                  50
202002 M9R                  25
202002 W3B                  19

결과를 보면, 2019년 12월 데이터가 조회되긴 했지만 ITEM_ID와 SALE_QTY가 NULL 값으로 한 건만 조회가 되었습니다. ITEM_ID 값을 T_SALE_YM_01에서 가져와야 하는데, 2019년 12월에 데이터가 없기 때문에 조회되지 않습니다. 그러므로, M_YM_01과 M_ITEM_01을 카테시안 조인으로 연월, 아이템별 데이터 집합을 생성한 후에 아우터 조인을 처리해야 합니다. 연월, 아이템별 데이터 집합을 만드는 카테시안 조인 SQL을 살펴보면 아래와 같습니다.

SELECT  T1.YM
        ,T2.ITEM_ID
FROM    M_YM_01 T1
        ,M_ITEM_01 T2
WHERE   T1.YM BETWEEN '201912' AND '202002'
ORDER BY T1.YM ,T2.ITEM_ID;
[결과]
YM     ITEM_ID      
------ ------------- 
201912 G1B          
201912 M9B          
201912 M9R          
201912 W3B          
202001 G1B          
202001 M9B          
202001 M9R          
202001 W3B          
202002 G1B          
202002 M9B          
202002 M9R          
202002 W3B

결과를 보며, 2019년 12월도 아이템별 데이터가 만들어진 것을 알 수 있습니다. SQL을 살펴보면 두 테이블을 연결하는 조인 조건이 없습니다. 이처럼 카테시안 조인을 위해서는 두 테이블을 연결하는 조인 조건을 주지 않습니다. 일반적으로 ANSI 구문을 사용할 때는, 카테시안 조인 처리를 위해 아래와 같이 CROSS JOIN을 사용합니다.

SELECT  T1.YM
        ,T2.ITEM_ID
FROM    M_YM_01 T1
        CROSS JOIN M_ITEM_01 T2
WHERE   T1.YM BETWEEN '201912' AND '202002'
ORDER BY T1.YM ,T2.ITEM_ID;

마지막으로 2019년 12월부터 2020년 2월까지 연월별, 아이템별 판매 데이터를 조회하기 위해서는 아래와 같이 SQL을 사용하면 됩니다. 카테시안 조인으로 만들어낸 연월별, 아이템별 데이터 집합에 아우터 조인을 사용해 실적 데이터를 연결해주면 됩니다.

SELECT  T0.*
        ,NVL(T1.SALE_QTY,0) SALE_QTY
FROM    (
		SELECT  T1.YM
		        ,T2.ITEM_ID
		FROM    M_YM_01 T1
		        ,M_ITEM_01 T2
		WHERE   T1.YM BETWEEN '201912' AND '202002'
		ORDER BY T1.YM ,T2.ITEM_ID
		) T0
		,T_SALE_YM_01 T1
WHERE   T1.SALE_YM(+) = T0.YM
AND     T1.ITEM_ID(+) = T0.ITEM_ID
ORDER BY T0.YM ,T0.ITEM_ID;
[결과]
YM     ITEM_ID      SALE_QTY 
------ ------------ --------- 
201912 G1B                  0
201912 M9B                  0
201912 M9R                  0
201912 W3B                  0
202001 G1B                 18
202001 M9B                100
202001 M9R                 45
202001 W3B                 30
202002 G1B                 15
202002 M9B                 50
202002 M9R                 25
202002 W3B                 19

오늘은 여기까지입니다. 존재하지 않는 실적 데이터를 결과에 포함하기 위해서는 이처럼 카테시안 조인으로 분석 마스터가 될 수 있는 차원을 만들수 있어야 합니다.

​

위와 같이 데이터를 마음대로 분석해볼 수 있는 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=