oracle 분석함수 RANK, DENSE_RANK, ROW_NUMBER 차이..

오라클함수중에..  랭킹을 메기는 함수를 비교..

굳이 설명 필요없이..  결과를 보면 알수 있다..

부서코드 '30'의 연봉랭킹을 구하는 SQL

with emp  as (

select '7782' EMPNO, 'CLARK' ENAME  , 2450 SAL  ,'10' DEPTNO from dual union all

select '7839', 'KING'    , 5000   ,'10' from dual union all

select '7934', 'MILLER'  , 1300   ,'10' from dual union all

select '7566', 'JONES'   , 2975   ,'20' from dual union all

select '7902', 'FORD'    , 3000   ,'20' from dual union all

select '7876', 'ADAMS'   , 1100   ,'20' from dual union all

select '7369', 'SMITH'   , 800    ,'20' from dual union all

select '7788', 'SCOTT'   , 3000   ,'20' from dual union all

select '7521', 'WARD'    , 1250   ,'30' from dual union all

select '7844', 'TURNER'  , 1500   ,'30' from dual union all

select '7499', 'ALLEN'   , 1600   ,'30' from dual union all

select '7900', 'JAMES'   , 950    ,'30' from dual union all

select '7698', 'BLAKE'   , 2850   ,'30' from dual union all

select '7654', 'MARTIN'  , 1250   ,'30' from dual 

)

SELECT  EMPNO

        , ENAME

        , A.DEPTNO

        , RANK() OVER ( ORDER BY SAL DESC)         RNK

        , DENSE_RANK() OVER ( ORDER BY SAL DESC)   D_RNK

        , ROW_NUMBER() OVER ( ORDER BY SAL DESC)   R_RNK

FROM    EMP A

WHERE   DEPTNO = '30'

결과는...

EMPNO   ENAME   DEPTNO  RNK     D_RNK   R_RNK

7698         BLAKE 30    1           1           1

7499         ALLEN 30    2           2           2

7844         TURNER 30    3           3           3

7654         MARTIN 30    4           4           4

7521         WARD 30    4           4           5

7900         JAMES 30    6           5           6

PARTITION BY 를 이용해서 부서별의 연봉랭킹을 구하는 SQL

SELECT  EMPNO

        , ENAME

        , DEPTNO

        , RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)         RNK

        , DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)   D_RNK

        , ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC)   R_RNK

FROM    EMP

결과는...

EMPNO   ENAME   DEPTNO  RNK     D_RNK   R_RNK

7839         KING         10         1 1 1

7782         CLARK 10         2 2 2

7934         MILLER 10         3 3 3

7902         FORD 20         1 1 1

7788         SCOTT 20         1 1 2

7566         JONES 20         3 2 3

7876         ADAMS 20         4 3 4

7369         SMITH 20         5 4 5

7698         BLAKE 30         1 1 1

7499         ALLEN 30         2 2 2

7844         TURNER 30         3 3 3

7521         WARD 30         4 4 4

7654         MARTIN 30         4 4 5

7900         JAMES 30         6 5 6