[Oracle] 오라클 RANK, DENSE_RANK(순위함수)

오라클에서 성적, 급여등 순위를 구하기 위해 순위 함수를 사용하면 된다.

순위 함수의 순위는 OVER 함수 내부의 ORDER BY 컬럼 값으로 결정된다.

• RANK() : 중복 순위 개수만큼 다음 순위 값을 증가시킴
• DENSE_RANK(): 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시함

SELECT ENAME,
       SAL,
       RANK() OVER(ORDER BY SAL DESC) RANK,
       DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK
  FROM EMP
 ORDER BY SAL DESC;

 

RANK는 2순위가 2명이므로 3순위를 건너뛰고 2순위 다음은 4순위로 표시

DENSE_RANK 함수는 2순위가 2명 이여도 다음 순위는 3순위로 표시

순위가 겹치지 않는다면 두 개 차이는 큰 차이가 없다.

 

• 중복 순위 값 없애기

순위가 중복되지 않도록 하기 위해서는 OVER함수 내부의 ORDER BY 컬럼을 추가하여 세부적인 순위를 정하도록 하면 된다.

SELECT ENAME,
       SAL,
       COMM,
       RANK() OVER(ORDER BY SAL DESC, COMM DESC) RANK
  FROM EMP
 ORDER BY SAL DESC,COMM DESC;

급여 외에 보너스 컬럼을 추가하여 급여 순위를 조회하면 중복 순위 없이 순차적인 순위가 표시된다.

 

 

• 그룹별 순위 구하기

조회된 결과에서 그룹별로 순위를 부여해야 하는 경우에는 PARTITION BY절을 추가하면 해당 그룹 내에 순위가 표시된다.

select deptno, ename, sal, rank() over(partition by deptno order by sal desc)rank
 from emp
 order by deptno, sal desc;

 

 

• 그룹별 최소값, 최대값 구하기

KEEP() 함수와 FIRST, LAST 키워드를 활용하면 그룹 내에 최소값, 최대값을 쉽게 구할 수 있다. DENSE_RANK함수만 사용 가능하다.

아래는 부서별 최고 급여, 최소 급여를 표시하는 예제

select deptno,
        ename,
        sal,
        min(sal) keep(dense_rank first order by sal) over(partition by deptno) sal_min,
        max(sal) keep(dense_rank last order by sal) over(partition by deptno) sal_max
   from emp
  order by deptno, sal desc;