RANK와 GROUP BY
RANK와 GROUP BY가 같이 사용된 SQL에서는 GROUP BY가 모두 처리된 후에 RANK가 사용됩니다. 다시 말해 분석함수는 분석함수를 제외한 SQL의 결과가 먼저 처리된 후, 해당 결과에 분석함수가 적용됩니다.
아래는 2019년 4월의 종목별 거래금액을 억원 단위로 구한 SQL입니다. GROUP BY와 SUM을 사용합니다.
# 19년4월 종목별 거래금액(억)
SELECT T1.STK_CD
,MAX(T1.STK_NM) STK_NM
,ROUND(SUM(T2.VOL * T2.C_PRC)/1e8,1) VOL_AMT
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD = T1.STK_CD)
WHERE T1.STK_NM IN ('NAVER','카카오','엔씨소프트')
AND T2.DT >= STR_TO_DATE('20190401','%Y%m%d')
AND T2.DT < STR_TO_DATE('20190501','%Y%m%d')
GROUP BY T1.STK_CD;
# 결과
STK_CD STK_NM VOL_AMT
======== ================= =========
035420 NAVER 15804.0
035720 카카오 10861.4
036570 엔씨소프트 7817.2 위의 SQL에 RANK를 추가합니다. 위의 결과에 대해 RANK가 처리됩니다. 아래와 같습니다.
# 19년4월 종목별 거래금액(억) – RANK 추가
SELECT T1.STK_CD
,MAX(T1.STK_NM) STK_NM
,ROUND(SUM(T2.VOL * T2.C_PRC)/1e8,1) VOL_AMT
,RANK() OVER(ORDER BY SUM(T2.VOL * T2.C_PRC)/1e8 DESC) VOL_AMT_RNK
FROM STOCK T1
INNER JOIN HISTORY_DT T2
ON (T2.STK_CD = T1.STK_CD)
WHERE T1.STK_NM IN ('NAVER','카카오','엔씨소프트')
AND T2.DT >= STR_TO_DATE('20190401','%Y%m%d')
AND T2.DT < STR_TO_DATE('20190501','%Y%m%d')
GROUP BY T1.STK_CD;
# 결과
STK_CD STK_NM VOL_AMT VOL_AMT_RNK
======== ================= ========= =============
035420 NAVER 15804.0 1
035720 카카오 10861.4 2
036570 엔씨소프트 7817.2 3
RANK와 GROUP BY - 잘 못 사용한 경우
GROUP BY 를 사용할 때, SELECT 절에는 GROUP BY에 정의한 컬럼(항목)과 집계함수를 처리한 컬럼만 사용할 수 있습니다. GROUP BY가 포함된 분석함수도 마찬가지입니다. 분석함수의 OVER 절 안에도 GROUP BY에서 정의한 컬럼(항목) 또는 집계함수를 처리한 컬럼만 사용할 수 있습니다.
아래는 제대로 RANK의 ORDER BY를 사용한 예입니다. VOL과 C_PRC를 SUM 처리해서 사용했습니다.
# 19년4월 종목별 거래금액(억) – RANK 추가
SELECT T1.STK_CD ,MAX(T1.STK_NM) STK_NM
,ROUND(SUM(T2.VOL * T2.C_PRC)/1e8,1) VOL_AMT
,RANK() OVER(ORDER BY SUM(T2.VOL * T2.C_PRC)/1e8 DESC) VOL_AMT_RNK
FROM STOCK T1
INNER JOIN HISTORY_DT T2 ON (T2.STK_CD = T1.STK_CD)
WHERE T1.STK_NM IN ('NAVER','카카오','엔씨소프트')
AND T2.DT >= STR_TO_DATE('20190401','%Y%m%d')
AND T2.DT < STR_TO_DATE('20190501','%Y%m%d')
GROUP BY T1.STK_CD;
# 결과
STK_CD STK_NM VOL_AMT VOL_AMT_RNK
======== ================= ========= =============
035420 NAVER 15804.0 1
035720 카카오 10861.4 2
036570 엔씨소프트 7817.2 3 거래금액(VOL_AMT)에 맞게 RANK가 제대로 부여된 것을 확인할 수 있습니다. 이번에는 잘 못 사용한 예입니다. RANK의 ORDER BY에서 집계함수를 처리하지 않고 VOL 과 C_PRC를 사용했습니다.
# 19년4월 종목별 거래금액(억) – RANK 추가 / 잘 못 사용한 경우
# 다른 DBMS에서는 에러가 난다.
SELECT T1.STK_CD ,MAX(T1.STK_NM) STK_NM
,ROUND(SUM(T2.VOL * T2.C_PRC)/1e8,1) VOL_AMT
,RANK() OVER(ORDER BY T2.VOL * T2.C_PRC/1e8 DESC) VOL_AMT_RNK
FROM STOCK T1
INNER JOIN HISTORY_DT T2 ON (T2.STK_CD = T1.STK_CD)
WHERE T1.STK_NM IN ('NAVER','카카오','엔씨소프트')
AND T2.DT >= STR_TO_DATE('20190401','%Y%m%d')
AND T2.DT < STR_TO_DATE('20190501','%Y%m%d')
GROUP BY T1.STK_CD;
# 결과
STK_CD STK_NM VOL_AMT VOL_AMT_RNK
======== ================= ========= =============
035420 NAVER 15804.0 1
036570 엔씨소프트 7817.2 2
035720 카카오 10861.4 3
결과를 보면 거래금액이 더 많은 카카오가 엔씨소프트보다 RANK가 낮은 것을 확인할 수 있습니다. 위와 같은 SQL은 다른 대부분 DBMS에서는 에러를 발생시키고 실행조차 되지 않습니다.