1. 개요
PIVOT은 오라클에서 사용할 수 있는 기능 중 하나로, 단순히 정렬을 바꾸는 기능뿐 만 아니라 고정적으로 보여주어야 하는 항목에 대해 활용할 수 있는 기능 중 하나이다.
특히 서브쿼리를 과도하게 사용하여 가독성과, 집계 함수 다중 사용으로 인한 성능 문제를 개선할 수 있는 방안이기도 하다.
피벗을 사용할 수 있는 방법은 크게 두가지로, 기준이 한가지일 경우(단일 피벗)와 여러가지일 경우(다중 피벗)로 볼 수 있다.
2. 피벗 원리
단일 피벗을 예로 들어 피벗의 작동 원리를 보자.
SELECT ML_TYPE_CD
,ML_CD
,USE_YN
,KO_ML_NM
,EN_ML_NM
,ZH_ML_NM
FROM
(
SELECT ML_TYPE_CD
,ML_CD
,USE_YN
,LANG_CD
)
PIVOT (MAX(ML_NM) AS ML_NM FOR LANG_CD IN (
'KO' AS KO
'EN' AS EN
'ZH' AS ZH
) 1) SELECT하여 최종적으로 조회할 항목들은 FROM과 PIVOT를 모두 참고하며, 이때 피벗화할 대상은 조회 항목과 PIVOT 항목 모두 존재하여야 한다(그래야 피벗한 대상이 최종적으로 조회됨).
2) 피벗화할 항목의 ALIAS는 PIVOT에서 만들어진 ALIAS와 동일해야 하는데, MAX(ML_NM)으로 나타낼 항목의 ALIAS(=NAME)이 PIVOT 테이블의 ALIAS의 접미에 붙는다(즉, 'KO' AS KO의 ALIAS인 KO + NAME = KO_ML_NM, 이 항목이 조회항목과 일치해야한다)
3) PIVOT의 기준인 FOR LANG_CD 항목(LANG_CD)는 FROM 기준 테이블에 존재해야 한다.
4) PIVOT의 ALIAS가 없다면 PIVOT 기준 문자열(IN에서의 'KO', 'EN'..) 싱글쿼터 그대로 조회된다.
5) PIVOT 테이블에서 산출하고자 하는 항목은 반드시 집계함수로만 표현할 수 있으며, 집계함수 내부에서 추가 계산 과정을 넣어야 한다.
6) PIVOT은 from의 기준 테이블을 참고하므로 완전 별개라 할 수는 없겠다. 다만 FROM 테이블의 ALIAS를 PIVOT에서 사용할 수 없고, pivot에 alias를 붙여 최종 내역 조회시 활용할 수 있다.
7) 피벗 사용시 제한된 항목에서 통계를 산출하는 경우이므로, 서브쿼리를 기준테이블로 사용해야 의미가 있고 성능적으로 유리하다.
3. 단일 피벗
- 기준이 하나일 경우(통계 : FEE/10000)
SELECT CNCLS_YMD
,NVL(BA_FEE,0) AS BA_FEE
,NVL(BB_FEE,0) AS BB_FEE
,NVL(CP_FEE,0) AS CP_FEE
,NVL(ST_FEE,0) AS ST_FEE
,NVL(FB_FEE,0) AS FB_FEE
FROM
(
SELECT A.CNCLS_YMD
,A.GDS_CD
,COUNT(*) AS COUNT
,SUM(NVL(A.WON_CNVRSN_FEE,0) + NVL(A.WON_CNVRSN_PRFMNC_AMT,0) AS FEE
FROM TB_DALY_BRKR_PRFMNC A
INNER JOIN TA_USER B
ON A.TKCG_BRKR = B.EMPL_NO
INNER JOIN TB_DEPT C
ON B.DEPT_CD = C.DEPT_CD
WHERE 1=1
AND C.BSN_DEPT_CD = 'Y'
AND A.CNCLS_YMD BETWEEN '20241201' AND '20241231'
AND A.DEL_YN = 'N'
AND A.MDFR_DIST_YN = 'Y'
GROUP BY A.CNCLS_YMD, A.GDS_CD
)
PIVOT ( MAX(FEE/10000) FOR GDS_CD IN (
'BA' AS BA_FEE
,'BB' AS BB_FEE
,'CP' AS CP_FEE
,'ST' AS ST_FEE
,'FB' AS FB_FEE
)상품코드인 GDS_CD를 기준으로 피벗화되어 나타난다.
4. 다중 피벗
- 기준이 두개일 경우(FEE/10000, COUNT(*))
- 소계용 서브테이블을 하나 더 만들어서 상품별 소계와 전체 소계를 모두 산출하며, 피벗화는 별도 진행
SELECT CNCLS_YMD
,NVL(BA_FEE,0) AS BA_FEE
,NVL(BA_COUNT,0) AS BA_COUNT
,NVL(BB_FEE,0) AS BB_FEE
,NVL(BB_COUNT,0) AS BB_COUNT
,NVL(CP_FEE,0) AS CP_FEE
,NVL(CP_COUNT,0) AS CP_COUNT
,NVL(ST_FEE,0) AS ST_FEE
,NVL(ST_COUNT,0) AS ST_COUNT
,NVL(FB_FEE,0) AS FB_FEE
,NVL(FB_COUNT,0) AS FB_COUNT
FROM
(
SELECT SA.CNCLS_YMD
,SA.GDS_CD
,SUM(SA.COUNT) OVER() AS TOT_COUNT
,SA.FEE
,SUM(TRUNC(SA.FEE/10000)) OVER() AS TOT_FEE
FROM
(
SELECT A.CNCLS_YMD
,A.GDS_CD
,COUNT(*) AS COUNT
,SUM(NVL(A.WON_CNVRSN_FEE,0) + NVL(A.WON_CNVRSN_PRFMNC_AMT,0) AS FEE
FROM TB_DALY_BRKR_PRFMNC A
INNER JOIN TA_USER B
ON A.TKCG_BRKR = B.EMPL_NO
INNER JOIN TB_DEPT C
ON B.DEPT_CD = C.DEPT_CD
WHERE 1=1
AND C.BSN_DEPT_CD = 'Y'
AND A.CNCLS_YMD BETWEEN '20241201' AND '20241231'
AND A.DEL_YN = 'N'
AND A.MDFR_DIST_YN = 'Y'
GROUP BY A.CNCLS_YMD, A.GDS_CD
)
PIVOT ( MAX(FEE/10000) AS FEE, MAX(COUNT) AS COUNT FOR GDS_CD IN (
'BA' AS BA
,'BB' AS BB
,'CP' AS CP
,'ST' AS ST
,'FB' AS FB
)
) 5. 유의사항
기본적으로 PIVOT 테이블은 PIVOT에서 보여주는 항목을 제외한 나머지 항목들에 대해 교차, 즉 모든 조건이 일치할 경우에만 보여준다.
쉽게 말하면, PIVOT을 하였는데 최종 결과가 계단식으로, 띄엄띄엄 나오는 경우가 있다.
이 경우는 PIVOT한 데이터의 기준점이, 최종 SELECT해주는 항목에서 일치하는 지점이 띄엄띄엄, 계단식으로 되어있던 것이다.
기준을 하나로 지정하고, 최종 조회 항목에도 하나의 기준만을 세운다면 문제없이 PIVOT 정렬을 수행할 수 있다.
SELECT *
FROM
(
SELECT SA.TKCG_BRKR --최종 보여줄때는 일자를 제거, 피벗의 기준은 브로커 하나로
,SA.USER_NM
,SA.FEE
,ROW_NUMBER() OVE(PARTITION BY SA.TKCG_BRKR ORDER BY SA.CNCLS_YMD DESC) AS DAILY_FEE
FROM
(
SELECT SUBSTR(A.CNCLS_YMD,1,6) AS CNCLS_YMD --피벗할때는 남겨주고
,A.TKCG_BRKR
,B.USER_NM
,SUM(NVL(A.WON_CNVRSN_FEE,0) + NVL(A.WON_CNVRSN_PRFMNC_AMT,0)) AS FEE
FROM TB_DALY_BRKR_PRFMNC
INNER JOIN TA_USER B
ON A.TKCG_BRKR = B.TKCG_BRKR
INNER JOIN TB_DEPT C
ON B.DEPT_CD = C.DEPT_CD
WHERE 1=1
AND C.BSN_DEPT_YN = 'Y'
AND A.CNCLS_YMD BETWEEN '20231201' AND '20241231'
AND A.TKCG_BRKR LIKE '%%'
AND A.DEL_YN = 'N'
AND A.MDFR_DIST_YN = 'Y'
GROUP BY SUBSTR(A.CNCLS_YMD,1,6), A.TKCG_BRKR, B.USER_NM
ORDER BY SUBSTR(A.CNCLS_YMD,1,6) DESC
) SA
)
PIVOT (
MAX(FEE/10000) AS FEE FOR SORT IN ('1' AS ONE, '2' AS TWO, ...)
)