[펌글] [ORACLE] RANK, ROW_NUMBER, DENSE_RANK

- 출처 : http://planmaster.tistory.com/150 -

이 세개의 함수는 오라클에서 그룹으로 묶고, 그것에 대한 순위를 정하는 함수이다.

기본입력된 데이타가 5명의 학생에 대한 성적정보가 들어있고, 1등이 2명이라면 등수가 어찌될까?
결과는 다음과 같다.

ROW_NUMBER는 정렬된 순서로 유일값의 번호를 부여한다.
RANK는 동점자에 대해서는 같은 번호를 부여하고 다음 사람에게는 동점자의 수만큼의 번호를 지난 다음번호를 부여한다.
DENSE_RANK는 동점자에 대해서는 같은 번호를 부여하고, 동점자에게 부여된 다음 번호를 부여한다.

* ROW_NUMBER() OVER (PARTITION BY)예제

SELECT * FROM
(
   SELECT empno, job, ename, sal,
                ROW_NUMBER() OVER( PARTITION BY job ORDER BY sal )  CNT
   FROM emp
)

- PARTITION BY job : job 칼럼의 값이 같은 모든 ROW들 내에서 ROW_NUMBER() 를 먹인다.
- ORDER BY sal : ROW_NUMBER()를 먹일 때에, job 칼럼의 값이 같은 모든 ROW들을
                          sal ASC로 정렬한 값을 기준으로 먹인다.

- 이렇게 작업한 후에 바깥쪽 쿼리에서 CNT = 1 같은 조건을 사용하여,
  원하는 결과값만 (ex : 각 job의 1위들만) 추려낼 수 있다.

* SUM() OVER(PARTITION BY) 예제

WITH T AS
     (  SELECT 'a' PRDNO ,1 SEQ ,10 MONEY FROM DUAL UNION ALL
        SELECT 'a', 2, 100 FROM DUAL UNION ALL
        SELECT 'a', 3, 20 FROM DUAL UNION ALL
        SELECT 'a', 4, 30 FROM DUAL UNION ALL
        SELECT 'b', 1, 50 FROM DUAL UNION ALL
        SELECT 'b', 2, 0 FROM DUAL UNION ALL
        SELECT 'b', 3, 0 FROM DUAL UNION ALL
        SELECT 'b', 4, 30 FROM DUAL
     ) -- 임시테이블 작성
SELECT PRDNO, SEQ, MONEY
-- 아래와 같이 ORDER BY 를 사용하지 않으면 각 그룹 내에서 ROW들이 동일한 SUM 값을 리턴
,SUM(MONEY) OVER(PARTITION BY PRDNO) SUM_MONEY 
-- 아래와 같이 ORDER BY 를 사용하면 ORDER BY의 기준칼럼의 순서대로 ROW가 증가됨에 따라 누적 SUM을 리턴
,SUM(MONEY) OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
-- 아래와 같이 SUM(1)을 대입하고 ORDER BY를 사용하면  ROW_NUMBER() 를 사용한 것 같은 효과를 낸다.
--,SUM(1) OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
--,ROW_NUMBER() OVER(PARTITION BY PRDNO ORDER BY SEQ) SUM_MONEY
--,MAX(MONEY) OVER(PARTITION BY PRDNO) SUM_MONEY
-- 아래와 같이 MAX의 경우에도 ORDER BY를 사용하면 누적의 성격을 지니게 된다.
--,MAX(MONEY) OVER(PARTITION BY PRDNO ORDER BY SEQ DESC) SUM_MONEY
FROM T