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