DataBase/SQL
SQL BOOSTER - 소계 구하기 ROLLUP, UNION ALL, 카테시안 조인,CUBE,GROUPING SETS
now0204
2024. 6. 3. 22:32
1. ROLLUP 이해하기
- 대부분의 분석 리포트는 소계(중간합계)와 전체합계가 필요하다.
- 이를 BI툴 없이 순수 SQL만 사용해야한다면, ROLLUP이 가장 효율적이다.
ROOLUP은 GROUP BY 뒤에 ROLLUP이라고 적어서 사용한다. GROUP BY ROLLUP(A,B,C,D)로 적으면
- GROUP BY된 A+B+C+D별 데이터
- A+B+C별 소계 데이터
- A+B별 소계데이터
- A별 소계데이터
- 전체합계로 나온다.
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM,
T1.CUS_ID,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID
ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID;
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM,
T1.CUS_ID,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY ROLLUP (TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID)
ORDER BY TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID;
전체합계와 소계를 구하는데 매우 편하다.
2. ROLLUP의 컬럼 순서
ROLLUP을 사용할 때 컬럼순서는 매우 중요하다. 컬럼 순서에 따라 다른 소계를 계산한다.
ROLLUP(A,B,C,D)와 ROLLUP(B,A,C,D)는 결과가 다르다. 첫번째 두번째 소계는 같지만 3번째가 다르다.
SELECT T1.ORD_ST, TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM, T1.CUS_ID, SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY ROLLUP(T1.ORD_ST, TO_CHAR(T1.ORD_DT,'YYYYMM'), T1.CUS_ID)
ORDER BY T1.ORD_ST,TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID;
SELECT T1.ORD_ST, TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM, T1.CUS_ID, SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN ('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY ROLLUP(TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.ORD_ST, T1.CUS_ID)
ORDER BY T1.ORD_ST,TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID;
- 컬럼 순서만 바꿔도 ORD_YM과 ORD_ST 소계로 바뀌니 주의하자
3. GROUPING
ROLLUP과 절대 뗄 수 없는 함수가 바로 GROUPING 함수이다.
이 함수는 특정 컬럼의 값이 소계인지 아닌지 구분해준다.
ROLLUP으로 만들어진 소계에서 ROLLUP된 컬럼은 NULL로 표시된다. 따라서 NULL 값인 데이터가 ROLLUP되면 원래 데이터인지 ROLLUP된 건지 알 수 없음으로 이를 표시하기 위해 GROUPING을 사용한다.
SELECT T1.ORD_ST, T1.PAY_TP, COUNT(*) ORD_CNT
FROM T_ORD T1
GROUP BY ROLLUP (T1.ORD_ST,T1.PAY_TP);
그냥 ROLLUP만 사용시 위와 같이 어떤 로우가 소계인지 판단이 어렵다.
SELECT T1.ORD_ST, GROUPING(T1.ORD_ST) GR_ORD_ST,T1.PAY_TP,GROUPING(T1.PAY_TP) GR_PAY_TP, COUNT(*) ORD_CNT
FROM T_ORD T1
GROUP BY ROLLUP (T1.ORD_ST,T1.PAY_TP);
이와 같이 소계에 해당하는 데이터는 1로 처리되었다. GROUPING 결과가 1이면 Total과 같은 텍스트로 치환하면된다.
*CASE문을 사용하자
SELECT CASE WHEN GROUPING(T1.ORD_ST) = 1 THEN 'Total' ELSE T1.ORD_ST END ORD_ST
,CASE WHEN GROUPING(T1.PAY_TP) = 1 THEN 'Total' ELSE T1.PAY_TP END PAY_TP, COUNT(*) ORD_CNT
FROM T_ORD T1
GROUP BY ROLLUP(t1.ORD_ST,T1.PAY_TP)
ORDER BY T1.ORD_ST, T1.PAY_TP;
4. ROLLUP 컬럼선택
특정 컬럼의 소계만 필요하거나, 전체 합계만 필요한 경우도 있다.
이때 ROLLUP의 위치를 옮기거나 소계가 필요한 대상을 괄호로 조정하면된다.
특히 전체 합계만 추가하는 기능은 매우 유용하다.
SELECT CASE WHEN GROUPING(TO_CHAR(T2.ORD_DT,'YYYYMM')) = 1 THEN 'Total'
ELSE TO_CHAR(T2.ORD_DT,'YYYYMM') END ORD_YM
,CASE WHEN GROUPING(T1.RGN_ID) = 1 THEN 'Total' ELSE T1.RGN_ID END RGN_ID
,CASE WHEN GROUPING(T1.CUS_GD) = 1 THEN 'Total' ELSE T1.CUS_GD END CUS_ID
,SUM(T2.ORD_AMT) ORD_AMT
FROM M_CUS T1,T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
AND T2.ORD_DT < TO_DATE('20170401','YYYYMMDD')
AND T1.RGN_ID IN ('A','B')
GROUP BY ROLLUP (TO_CHAR(T2.ORD_DT,'YYYYMM'),T1.RGN_ID,T1.CUS_GD)
ORDER BY TO_CHAR(T2.ORD_DT,'YYYYMM'),T1.RGN_ID,T1.CUS_GD;
GROUP BY TO_CHAR(T2.ORD_DT,'YYYYMM'),ROLLUP(T1.RGN_ID,T1.CUS_GD)
- GROUP BY (A,B,C)
- C ROLLUP -> A+B별소계
- B+C ROLLUP -> A별 소계
- A+B+C ROLLUP -> 전체 합계
- GROUP BY A, ROLLUP(B,C)
- C ROLLUP A+B 소계
- B,C ROLLUP A별 소계
- GROUP BY A,B ROLLUP(C)
- C ROLLUP => A+B별 소계
4.1 ROLL UP 컬럼 묶기
ROLLUP 사용시 여러 컬럼을 하나의 괄호로 묶을 수 있다.
묶인 컬럼은 하나의 단위로 ROLLUP된다.
전체 합계와 일부 컬럼 소계에 유용하다.
SELECT CASE WHEN GROUPING(TO_CHAR(T2.ORD_DT,'YYYYMM')) = 1 THEN 'Total'
ELSE TO_CHAR(T2.ORD_DT,'YYYYMM') END ORD_YM
,CASE WHEN GROUPING(T1.RGN_ID) = 1 THEN 'Total' ELSE T1.RGN_ID END RGN_ID
,CASE WHEN GROUPING(T1.CUS_GD) = 1 THEN 'Total' ELSE T1.CUS_GD END CUS_ID
,SUM(T2.ORD_AMT) ORD_AMT
FROM M_CUS T1,T_ORD T2
WHERE T1.CUS_ID = T2.CUS_ID
AND T2.ORD_DT >= TO_DATE('20170201','YYYYMMDD')
AND T2.ORD_DT < TO_DATE('20170401','YYYYMMDD')
AND T1.RGN_ID IN ('A','B')
GROUP BY ROLLUP((TO_CHAR(T2.ORD_DT,'YYYYMM'),T1.RGN_ID,T1.CUS_GD))
ORDER BY TO_CHAR(T2.ORD_DT,'YYYYMM'),T1.RGN_ID,T1.CUS_GD;
- ROLLUP으로 컬럼을 묶을 때 다음 두 가지를 정확하게 기억하자
1. GROUP BY A,B,C,D와 같이 여러 개 컬럼이 GROUP BY 될 때 전체합계만 필요한경우 모두 묶어서 ROLLUP한다.
2. GROUP BY A,B,C,D,E,F와 같이 여러 개 컬럼 중 앞쪽 3개 컬럼까지의 소계와 전체 합계가 필요하면 DEF를 하나로 묶는다.
* ROLLUP (A,B,C,D) -> A B C 고정 D 모든 값 나온 뒤 ROLLUP(소계), AB 고정 C 모든 조합 나온 뒤 ROLLUP(C,D) ...
고정된 것과 변하는 것, GROUP BY에 따라 모든 조합을 내보낸 뒤에 ROLLUP 한다는 것
5. 다른 방식으로 소계 구하기
5.1 UNION ALL
가장 보편적인 방법 중 하나이다.
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM')ORD_YM, T1.CUS_ID,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM'), T1.CUS_ID
UNION ALL
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM')ORD_YM,'Total' CUS_ID, SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM')
UNION ALL
SELECT 'Total' ORD_YM,'Total' CUS_ID,SUM(T1.ORD_AMT)ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD');- GROUP BY를 조절해가면서 SUM을 호출한다.
- GROUP BY를 조절해가면서 호출
- GROUP BY ORD_DT,CUS_ID-> 컬럼별 값
- GROUP BY ORD_DT
- 그냥 SUM
*이 방법을 사용하면 성능에서 손해를 볼 수 밖에 없음! SELECT 작업이 3번 이루어지고 이어붙인 것
5.2 카테시안-조인으로 대신하기
SELECT CASE WHEN T2.RNO = 1 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
WHEN T2.RNO = 2 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
WHEN T2.RNO = 3 THEN 'Total' END ORD_YM
,CASE WHEN T2.RNO = 1 THEN T1.CUS_ID
WHEN T2.RNO = 2 THEN 'Total'
WHEN T2.RNO = 3 THEN 'Total' END CUS_ID
,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1, (SELECT ROWNUM RNO FROM DUAL CONNECT BY ROWNUM <=3)T2
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY CASE WHEN T2.RNO = 1 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
WHEN T2.RNO = 2 THEN TO_CHAR(T1.ORD_DT,'YYYYMM')
WHEN T2.RNO = 3 THEN 'Total' END
,CASE WHEN T2.RNO = 1 THEN T1.CUS_ID
WHEN T2.RNO = 2 THEN 'Total'
WHEN T2.RNO = 3 THEN 'Total' END;- 조인조건을 주지 않아, 3배 늘어난 ROW 데이터가 나오는데, 이를 여러 조건으로 처리해서 소계를 구했다.
- 성능 저하 문제, 너무 어려운 SQL 등 단점이 있다 아주 특수한 경우가 아니라면 사용을 지양하자
5.3 WITH과 UNION ALL 사용하기
WITH T_RES AS(
SELECT TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM, T1.CUS_ID,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID
)
SELECT T1.ORD_YM,T1.CUS_ID,T1.ORD_AMT
FROM T_RES T1
UNION ALL
SELECT T1.ORD_YM,'Total',SUM(T1.ORD_AMT)
FROM T_RES T1
GROUP BY T1.ORD_YM
UNION ALL
SELECT 'Total', 'Total', SUM(T1.ORD_AMT)
FROM T_RES T1;
*여러 방법이 있지만 일반적으로 ROLLUP을 쓰는 편이 좋다..
6. CUBE
CUBE는 조합 가능한 모든 소계를 만들어 낸다. 사용 방법은 ROLLUP과 같다.
CUBE(A,B,C) -> A+B별소계, A별소계, A+C별 소계, B+C별 소계,B별 소계, C별 소계가 나온다.
생각보다 쓸일이 없다!
보통 고객도 불필요한 소계가 나오는 경우도 있다. CUBE를 사용해야한다면, ROLLUP과 추가적인 UNION ALL로 해결가능한지 생각해보자
7. GROUPING SETS
ROLLUP과 CUBE처럼 소계를 만든다.
SELECT
CASE WHEN GROUPING(TO_CHAR(T1.ORD_DT,'YYYYMM')) =1 THEN 'Total' ELSE TO_CHAR(T1.ORD_DT,'YYYYMM') END ORD_YM
,CASE WHEN GROUPING(T1.CUS_ID) = 1 THEN 'Total' ELSE T1.CUS_ID END CUS_ID
,COUNT(*) ORD_CNT
,SUM(T1.ORD_AMT) ORD_AMT
FROM T_ORD T1
WHERE T1.CUS_ID IN('CUS_0001','CUS_0002')
AND T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND T1.ORD_DT < TO_DATE('20170501','YYYYMMDD')
GROUP BY GROUPING SETS(
(TO_CHAR(T1.ORD_DT,'YYYYMM'),T1.CUS_ID) --기본 GROUP BY
,(TO_CHAR(T1.ORD_DT,'YYYYMM')) -- 주문년월별 소계
,(T1.CUS_ID) -- 고객 ID별 소계
,() -- 전체 소계
);