9장. 분석함수 - 기타 분석함수

9.2.1 순위 분석함수

RANK분석함수

OVER절에 반드시 ORDER BY 를 필수적으로 사용해야함
값이 큰 순으로 1,2,3,,, 위를 결정하고 싶다면 DESC
값이 작은 순으로 1,2,3,,,위를 결정하고 싶다면 ASC

-- RANK 분석함수
SELECT  T1.CUS_ID
		,SUM(T1.ORD_AMT) ORD_AMT
		,RANK() OVER(ORDER BY SUM(T1.ORD_AMT) DESC) RNK -- 높은 주문금액에 따른 순위
FROM    T_ORD T1
GROUP BY T1.CUS_ID

| CUS_ID   | ORD_AMT |  | RNK  |
| -------- | ------- |  | ---- |
| CUS_0009 | 102760  |  | 1    |
| CUS_0089 | 97150   |  | 2    |
| CUS_0020 | 91580   |  | 3    |
| CUS_0078 | 90200   |  | 4    |
| CUS_0067 | 89800   |  | 5    |
| CUS_0056 | 88600   |  | 6    |
...
...
...
| CUS_0015 | 37500   |  | 88   |
| CUS_0024 | 36060   |  | 89   |
| CUS_0071 | 34100   |  | 90   |

DENSE_RANK

-- RANK와 DENSE_RANK의 비교
SELECT  T1.ID ,T1.AMT
		,RANK() OVER(ORDER BY T1.AMT DESC) RANK_RES
		,DENSE_RANK() OVER(ORDER BY T1.AMT DESC) DENSE_RANK_RES
FROM    (
		SELECT  'A' ID ,300 AMT FROM DUAL UNION ALL
		SELECT  'B' ID ,150 AMT FROM DUAL UNION ALL
		SELECT  'C' ID ,150 AMT FROM DUAL UNION ALL
		SELECT  'D' ID ,100 AMT FROM DUAL 
		) T1;
		
| ID   | AMT  | RANK_RES |  | DENSE_RANK_RES |
| ---- | ---- | -------- |  | -------------- |
| A    | 300  | 1        |  | 1              |
| B    | 150  | 2        |  | 2              |
| C    | 150  | 2        |  | 2              |
| D    | 100  | 4        |  | 3              |

-- RANK와 DENSE_RANK 모두 공동 2위가 존재할 수 있다
-- 그러나 DENSE_RANK는 3위를 인정해준다
-- 반대로 RANK는 3위를 인정하지 않는다

9.2.2 ROW_NUMBER (TOP-N쿼리 안티패턴)

RANK, DENSE_RANK와 공통점 : OVER절의 ORDER BY에 정의한 순서대로 줄 번호를 부여함
RANK, DENSE_RANK와 차이점 : 중복된 순위를 내보내지 않음
ROWNUM와 비교했을 때
- 장점) OVER절 PARTITION BY, ORDER BY로 ROWNUM보다 세밀한 줄 번호를 제어함
- 단점) 성능은 ROWNUM보다 떨어짐, 따라서 왠만하면 ROWNUM으로 처리할 것


-- ROW_NUMBER()를 이용한 순위 구하기
SELECT  T1.ID ,T1.AMT
		,RANK() OVER(ORDER BY T1.AMT DESC) RANK_RES
		,ROW_NUMBER() OVER(ORDER BY T1.AMT DESC) ROW_NUM_RES
FROM    (
		SELECT  'A' ID ,300 AMT FROM DUAL UNION ALL
		SELECT  'B' ID ,150 AMT FROM DUAL UNION ALL
		SELECT  'C' ID ,150 AMT FROM DUAL UNION ALL
		SELECT  'D' ID ,100 AMT FROM DUAL 
		) T1;

| ID   | AMT  |  | RANK_RES | ROW_NUM_RES |
| ---- | ---- |  | -------- | ----------- |
| A    | 300  |  | 1        | 1           |
| B    | 150  |  | 2        | 2           |
| C    | 150  |  | 2        | 3           |
| D    | 100  |  | 4        | 4           |

-- 세밀한 제어가 가능함 ( OVER절의 PARTITION BY, ORDER BY를 활용 )
-- 3월, 4월 주문에 대해, 월별로 주문금액 Top-3 고객 구하기
SELECT  T0.ORD_YM ,T0.CUS_ID ,T0.ORD_AMT ,T0.BY_YM_RANK
FROM    (
		SELECT  TO_CHAR(T1.ORD_DT,'YYYYMM') ORD_YM ,T1.CUS_ID ,SUM(T1.ORD_AMT) ORD_AMT
				,ROW_NUMBER()
					OVER(PARTITION BY TO_CHAR(T1.ORD_DT,'YYYYMM') ORDER BY SUM(T1.ORD_AMT) DESC) BY_YM_RANK
		FROM    T_ORD T1
		WHERE   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
		) T0
WHERE   T0.BY_YM_RANK <= 3 -- 일종의 TOP-N 패턴에서 ROWNUM 역할
ORDER BY T0.ORD_YM ,T0.BY_YM_RANK;
		
| ORD_YM | CUS_ID   | ORD_AMT |  | BY_YM_RANK |
| ------ | -------- | ------- |  | ---------- |
| 201703 | CUS_0020 | 13350   |  | 1          |
| 201703 | CUS_0040 | 11400   |  | 2          |
| 201703 | CUS_0089 | 9650    |  | 3          |
| 201704 | CUS_0056 | 13300   |  | 1          |
| 201704 | CUS_0041 | 10900   |  | 2          |
| 201704 | CUS_0061 | 10900   |  | 3          |

-- ROW_NUMBER()를 이용한 데이터 선택
-- 고객별로 마지막 주문만 조회
-- 아래 WHERE절을 제거하면 '고객별 주문을 조회'
SELECT  T2.*
FROM    (
		SELECT  T1.*
				,ROW_NUMBER() OVER(PARTITION BY T1.CUS_ID ORDER BY T1.ORD_DT DESC ,T1.ORD_SEQ DESC) ORD_RNK
		FROM    T_ORD T1
		) T2
WHERE   T2.ORD_RNK = 1;

9.2.3 LAG, LEAD

-- LEAD와 LAG의 사용 예제
SELECT  T1.CUS_ID 
,SUM(T1.ORD_AMT) ORD_AMT
		,ROW_NUMBER() OVER(ORDER BY SUM(T1.ORD_AMT) DESC) RNK
		,LAG(T1.CUS_ID,1) OVER(ORDER BY SUM(T1.ORD_AMT) DESC) LAG_1
		,LEAD(T1.CUS_ID,1) OVER(ORDER BY SUM(T1.ORD_AMT) DESC) LEAD_1
FROM    T_ORD T1
WHERE   T1.ORD_DT >= TO_DATE('20170301','YYYYMMDD')
AND     T1.ORD_DT < TO_DATE('20170401','YYYYMMDD')
AND     T1.CUS_ID IN ('CUS_0020','CUS_0021','CUS_0022','CUS_0023')
GROUP BY T1.CUS_ID
ORDER BY SUM(T1.ORD_AMT) DESC -- 생략가능
;

| CUS_ID   | ORD_AMT | RNK  |  | LAG_1    | LEAD_1   |
| -------- | ------- | ---- |  | -------- | -------- |
| CUS_0020 | 13350   | 1    |  |          | CUS_0022 |
| CUS_0022 | 7250    | 2    |  | CUS_0020 | CUS_0023 |
| CUS_0023 | 4900    | 3    |  | CUS_0022 | CUS_0021 |
| CUS_0021 | 3100    | 4    |  | CUS_0023 |          |

OVER(ORDER BY SUM(T1.ORD_AMT) DESC)
- LAG, DESC 자신보다 주문금액이 높은 이전 데이터 를 조회
- LEAD, DESC 자신보다 주문금액이 낮은 이후 데이터 를 조회