SQL

[SQL]카테시안조인(CARTESIAN JOIN)

한기리 2021. 2. 7. 10:10
728x90
320x100

 CARTESIAN-JOIN

- CARTESIAN-JOIN 이해하기

 

CARTESIAN-JOIN은 조인 조건이 없는 조인을 뜻한다.

 

 

 

예를 들어, FROM절에 A와 B테이블이 있다고 가정한다. A에는 2건, B에는 4건 있을 때 조인 조건 없이 카테시안 조인이 이루어지면서 A * B가 되어 총 8(2*4) 건이 된다.

 

 

예제) 고객등급(M_CUS.CUS_GD)과 아이템유형(M_ITM.ITM_TP)의 조합 가능한 모든 데이터

SELECT T1.CUS_GD, T2.ITM_TP 
  FROM ( SELECT DISTINCT A.CUS_GD FROM M_CUS A ) T1 
      ,( SELECT DISTINCT A.ITM_TP FROM M_ITM A ) T2 
 ORDER BY T1.CUS_GD, T2.ITM_TP;

  1. CUS_GD의 종류를 가져온 데이터 집합
  2. ITM_TP의 종류를 가져온 데이터 집합
  3. 1번과 2번의 조인 조건이 없다. 카테시안-조인을 수행한다.
    • 1번 집합의 CUS_GD A는 2번 집합의 4건과 조인되어 4건이 만들어진다.
    • 1번 집합의 CUS_GD B도 2번 집합의 4건과 조인되어 4건이 만들어진다.
    • 최종 8건의 결과 건수가 만들어진다.
  • 카테시안-조인은 주로 BI 환경에서 분석 차원 집합을 만들거나 시스템 오픈 전에 대량의 테스트 데이터를 만들기 위해 일회성으로 사용될 수 있다.

- CARTESIAN-JOIN의 위험성

 

실수로 카테시안-조인이 발생하면 시스템 장애가 일어날 수 있기 때문에 매우 위험한 조인이다.

 

 

예제) 조인 조건의 누락

SELECT COUNT(*) FROM T_ORD T1 ,T_ORD_DET T2;

 

T_ORD에는 3,047건, T_ORD_DET에는 3,224건의 데이터가 있다. 두 테이블 간에 조인 조건 없이 카테시안-조인이 수행된다면, 결과 건수는 3,047 * 3,224 = 9,823,528건의 조인 결과가 나오게 된다.

 

 

 

약 9백8십만 건에 달하는 데이터를 사용자 화면에 출력하다 보면 WAS에 장애가 발생하거나 사용자 컴퓨터가 과부하로 멈추는 일이 발생할 것이다. 또는 INSERT 문에서 위와 같은 실수를 한다면 데이터베이스에는 엄청난 부하가 발생하게 된다.

 

 

예제) 조인 조건의 별칭 실수

SELECT COUNT(*) 
  FROM T_ORD T1 ,T_ORD_DET T2 
 WHERE T1.ORD_SEQ = T1.ORD_SEQ;

 

위 같은 경우는 조인 조건에서 T1.ORD_SEQ = T2.ORD_SEQ가 되어야하지만 T1.ORD_SEQ만 사용된 상황이다. 이는 T_ORD의 모든 데이터를 나오게 하는 필터 조건이며, 결국 조인 조건이 없는 카테시안-조인이다.

 

 

- 분석마스터 만들기

 

카테시안-조인은 분석 마스터를 만들 때 유용하다.

 

 

예제) 특정 고객 두 명의 2월, 3월, 4월의 월별 주문 건수

SELECT T1.CUS_ID, T1.CUS_NM, T2.ORD_YM, T2.ORD_CNT 
  FROM M_CUS T1 
      ,( SELECT A.CUS_ID ,TO_CHAR(A.ORD_DT, 'YYYYMM') ORD_YM 
               ,COUNT(*) ORD_CNT 
           FROM T_ORD A 
          WHERE A.CUS_ID IN ('CUS_0003', 'CUS_0004') 
          AND A.ORD_DT >= TO_DATE('20170201', 'YYYYMMDD') 
          AND A.ORD_DT < TO_DATE('20170501', 'YYYYMMDD') 
         GROUP BY A.CUS_ID ,TO_CHAR(A.ORD_DT, 'YYYYMM') 
         ) T2 
  WHERE T1.CUS_ID IN ('CUS_0003', 'CUS_0004') 
  AND T1.CUS_ID = T2.CUS_ID(+) 
 ORDER BY T1.CUS_ID, T2.ORD_YM;

 

위와 같이 고객별로 주문이 존재하는 월만 조회된다. 'CUS_0003'고객은 2월 주문이 없고, 'CUS_0004'고객은 4월 주문이 없다.

 

 

예제) 특정 고객 두 명의 2월, 3월, 4월의 월별 주문 건수 - 주문이 없는 월도 0으로 나오게 처리

SELECT T0.CUS_ID, T0.CUS_NM, T0.BASE_YM, NVL(T2.ORD_CNT, 0) ORD_CNT 
  FROM ( SELECT T1.CUS_ID ,T1.CUS_NM ,T4.BASE_YM 
           FROM M_CUS T1 
               ,(SELECT TO_CHAR(ADD_MONTHS(TO_DATE('20170201', 'YYYYMMDD'), ROWNUM-1), 'YYYYMM') BASE_YM 
                   FROM DUAL CONNECT BY ROWNUM <= 3 
                   ) T4 
           WHERE T1.CUS_ID IN ('CUS_0003', 'CUS_0004')
           ) T0 
       ,( SELECT A.CUS_ID ,TO_CHAR(A.ORD_DT, 'YYYYMM') ORD_YM ,COUNT(*) ORD_CNT 
            FROM T_ORD A 
           WHERE A.CUS_ID IN ('CUS_0003', 'CUS_0004') 
           AND A.ORD_DT >= TO_DATE('20170201', 'YYYYMMDD') 
           AND A.ORD_DT < TO_DATE('20170501', 'YYYYMMDD') 
          GROUP BY A.CUS_ID ,TO_CHAR(A.ORD_DT, 'YYYYMM')
          ) T2 
  WHERE T0.CUS_ID = T2.CUS_ID(+) 
  AND T0.BASE_YM = T2.ORD_YM(+) 
 ORDER BY T0.CUS_ID, T0.BASE_YM;

'CUS_0003' 고객은 2월 주문이, 'CUS_0004' 고객은 4월 주문이 0건인 것을 확인할 수 있다.

  1. M_CUS에서 두 명의 고객만 선택
  2. CONNECT BY ROWNUM을 이용 3개월 치의 데이터를 생성
  3. 1번과 2번을 카테시안-조인 처리, 고객별로 3개월씩 총 6건의 마스터가 만들어진다.
  4. T_ORD에서 특정 고객들의 3개월간의 주문에 대해, 고객 + 주문년월별로 건수를 집계
  5. 3번을 기준 데이터 집합으로 4번과 아우터-조인 수행

예제) 고객등급, 아이템유형별 주문수량

SELECT A.CUS_GD, D.ITM_TP ,SUM(C.ORD_QTY) ORD_QTY 
  FROM M_CUS A ,T_ORD B ,T_ORD_DET C ,M_ITM D 
 WHERE A.CUS_ID = B.CUS_ID 
 AND C.ORD_SEQ = B.ORD_SEQ 
 AND D.ITM_ID = C.ITM_ID 
 AND B.ORD_ST = 'COMP' 
GROUP BY A.CUS_GD, D.ITM_TP 
ORDER BY A.CUS_GD, D.ITM_TP;

 

아이템유형(ITM_TP)에는 'CLOTHES, COOK, ELEC, PC' 네 가지 종류가 있지만, 결과에는 PC 주문 수량이 없다.

 

 

예제) 고객등급, 아이템유형별 주문수량 - 주문이 없는 아이템유형도 나오도록 처리

SELECT T0.CUS_GD, T0.ITM_TP, NVL(T3.ORD_QTY,0) ORD_QTY
  FROM ( SELECT T1.CUS_GD, T2.ITM_TP
           FROM ( SELECT A.BAS_CD CUS_GD FROM C_BAS_CD A WHERE A.BAS_CD_DV = 'CUS_GD' ) T1
               ,( SELECT A.BAS_CD ITM_TP FROM C_BAS_CD A WHERE A.BAS_CD_DV = 'ITM_TP') T2
        ) T0
      ,( SELECT A.CUS_GD, D.ITM_TP
               ,SUM(C.ORD_QTY) ORD_QTY
           FROM M_CUS A
               ,T_ORD B
               ,T_ORD_DET C
               ,M_ITM D
          WHERE A.CUS_ID = B.CUS_ID
          AND   C.ORD_SEQ = B.ORD_SEQ
          AND   D.ITM_ID = C.ITM_ID
          AND   B.ORD_ST = 'COMP'
         GROUP BY A.CUS_GD, D.ITM_TP
        ) T3
 WHERE T0.CUS_GD = T3.CUS_GD(+)
 AND   T0.ITM_TP = T3.ITM_TP(+)
ORDER BY T0.CUS_GD, T0.ITM_TP;

 

 

카테시안-조인을 이용해 고객등급과 아이템유형의 조합 가능한 모든 데이터를 만든 후, 아우터-조인을 사용하였다.

 

 

- 테스트 데이터 만들기

 

 

대량의 테스트 데이터를 만들기 위해 카테시안-조인을 활용할 수 있다.

 

 

 

예제) 테스트 주문데이터를 만들기 위한 SQL

 

SELECT ROWNUM ORD_NO, T1.CUS_ID, T2.ORD_ST, T3.PAY_TP, T4.ORD_DT
  FROM M_CUS T1
      ,( SELECT 'WAIT' ORD_ST FROM DUAL UNION ALL
         SELECT 'COMP' ORD_ST FROM DUAL ) T2
      ,( SELECT 'CARD' PAY_TP FROM DUAL UNION ALL
         SELECT 'BANK' PAY_TP FROM DUAL UNION ALL
         SELECT NULL PAY_TP FROM DUAL ) T3
      ,( SELECT TO_DATE('20170101', 'YYYYMMDD') + (ROWNUM-1) ORD_DT
           FROM DUAL
         CONNECT BY ROWNUM <= 365 ) T4;

 

 

조합 가능한 모든 데이터를 만들기 위해 카테시안-조인을 이용했다.

 

 

예제) 의미 없는 숫자 집합

SELECT ROWNUM RNO FROM DUAL A CONNECT BY ROWNUM <= 10;

부하 테스트나 성능 테스트를 위해 많은 데이터가 필요할 때 다음과 같은 의미 없는 숫자와 카테시안-조인을 하여 추가할 수 있다.

 

예제) 데이터 값별로 분포도 조정

SELECT 'WAIT' ORD_ST FROM DUAL CONNECT BY ROWNUM <= 2 UNION ALL
SELECT 'COMP' ORD_ST FROM DUAL CONNECT BY ROWNUM <= 3

주문상태 중 'WAIT'가 전체 주문의 2/5, 'COMP'가 3/5를 차지하도록 테스트 데이터를 구성할 때 사용할 수 있다.

728x90
320x100

'SQL' 카테고리의 다른 글

[SQL] 아우터조인(OUTER-JOIN)  (0) 2021.01.29

'SQL'의 다른글

  • 현재글[SQL]카테시안조인(CARTESIAN JOIN)

관련글

댓글

티스토리툴바