[Oracle] ROW_NUMBER, RANK, DENSE_RANK 함수 순위 매기기 :: 마이자몽

사원들의 급여를 많이 받는 순서대로 순위를 출력하시오.

ORACLE SCOTT 계정의 EMPLOYEES 테이블을 이용해서 출력한 결과입니다.

순서를 매기는 방법에는 여러가지 방법이 있습니다. 분석함수를 사용하지 않는다면 아래처럼 구할것 입니다.

 

SELECT 
    EMPNO
    ,ENAME 
    ,SAL
    ,DEPTNO
    ,ROWNUM AS RNK
FROM
  (
    SELECT 
      * 
    FROM 
      EMP 
    ORDER BY SAL DESC
  );

SELECT 절은 ORDER BY 이전에 실행되기 때문에 SUBQUERY를 이용해서 순위를 매길수 있습니다. 전체 순위중 일부를 출력하기 위해서는 TOP-N쿼리나 ROW LIMIT CLAUSE를 사용해서 출력이 가능합니다. 자세한 내용은 아래 링크를 참조!

[Oracle] 오라클 페이징 쿼리 쉽게 만들기 Row Limiting Clause 사용 :: 마이자몽

 

그럼 분석함수를 이용하면 어떨까요?

SELECT
    EMPNO
    ,ENAME
    ,SAL
    ,DEPTNO
    ,ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RNK
FROM EMP;

ROW_NUMBER함수를 사용해서 쉽게 구할 수 있습니다.

 

분석함수에는 순위를 매기기 위해 사용되는 함수가 3가지 있습니다. ROW_NUMBER(), RANK(), DENSE_RANK()  세개의 함수를 비교해서 어떤 차이가 있는지 알아봅시다.

 

 

ROW_NUMBER, RANK, DENSE_RANK 비교

위의 예제에서 ROW_NUMBER를 사용했을때, 중복에 대한 순위 처리는 없었습니다. 그냥 순서대로만 나열했습니다. 그런데 동일 값에 대해서 같은 순서로 출력하고 싶을때는 어떻게 해야할까요? 3가지 함수를 동시에 사용해서 비교해보겠습니다.

 

 

SELECT
    EMPNO
    ,ENAME
    ,SAL
    ,DEPTNO
    ,ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RN_RANK
    ,RANK() OVER(ORDER BY SAL DESC) AS RANK
    ,DENSE_RANK() OVER(ORDER BY SAL DESC) AS D_RANK
FROM EMP;

9, 10 ,11번 행을 확인해보면 각각 결과가 다른 것을 확인 할 수 있습니다.

 

 

 

ROW_NUMBER 함수

ROW_NUMBER 함수는 동일 값에 상관없이 순차적인 번호로 순위를 부여합니다.

 

RANK 함수

RANK 함수는 동일값에 대해서 같은 순위를 부여하고 다음 순위는 누적 순위로 출력합니다.

 

DENSE_RANK 함수

 DENSE_RANK 함수는 동일값에 대해서 같은 순위를 부여하고 다음 순위는 누적 시키지 않고 그대로 순차를 지킵니다.

 

 

 

PARTITION ORDER BY

SELECT
    EMPNO
    ,ENAME
    ,SAL
    ,DEPTNO
    ,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RNK
FROM EMP;

분석함수를 사용하면, 각 그룹별로 순위를 매기는 작업을 간단한 쿼리로 출력이 가능합니다.

[Oracle] OVER ... PARTITION BY 분석절 ANALYTIC FUNCTION :: 마이자몽

 

 

RANK 예측 WITHIN GROUP

SELECT
    RANK(2975) WITHIN GROUP (ORDER BY SAL DESC) AS RNK
    ,DENSE_RANK(2975) WITHIN GROUP (ORDER BY SAL DESC) AS D_RNK
FROM EMP;

RANK와 DENSE_RANK함수에 인자를 넣어주면 해당 값이 범위에 있다면 몇등인가?를 확인할 수 있습니다.