[Oracle] 순위를 반환하는 함수 (RANK, DENSE_RANK, ROW_NUMBER) [펌]

[Oracle] 순위를 반환하는 함수 (RANK, DENSE_RANK, ROW_NUMBER) [펌]

 

 

테이블에서 특정 값을 기준으로 순위를 매겨서 보고 싶을 때 ORDER BY 절을 사용합니다. 동일한 점수가 나온 경우 순위를 동일하게 하거나 또는 점수가 동일하더라도 순위를 다르게 매겨야 하는 경우가 있습니다. Oracle에서는 순위를 나타날 때 편하게 사용할 수 있도록 RANK, DENSE_RANK, ROW_NUMBER 함수를 제공합니다.

 

이번 글에서는 쿼리박스(QueryBox) 프로그램을 사용해서 Oracle의 Rank, DENSE_RANK, ROW_NUMBER 함수의 차이점과 특징에 대해 알아보겠습니다.

순위 함수

Oracle에서 제공하는 순위 함수는 아래와 같습니다. 해당 함수에 대한 설명보다는 실습을 통해 이해하는 것이 더 쉬울 수 있습니다.

RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환한다.  
DENSE_RANK : 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환한다.
ROW_NUMBER : 중복 관계없이 순차적으로 순위를 반환한다.

RANK 함수

RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 해당 개수만큼 건너뛰고 반환합니다. RANK 함수를 사용해서 조회한 결과를 보면, SAL(급여) 값이 동일한 경우에는 순위 값이 '2'로 중복 순위를 부여합니다. 중복 순위 다음에는 순위 값이 '4'입니다. 앞에서 1등 1명, 2등 2명이 존재하기 때문에 다음 순위의 값은 '4'입니다.

SELECT 컬럼1, 컬럼2, 컬럼3, RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명 FROM 테이블;

 

Oracle RANK 함수 사용 예제

DENSE_RANK 함수

DENSE_RANK 함수는 동일한 값이면 중복 순위를 부여하고, 다음 순위는 중복 순위와 상관없이 순차적으로 반환합니다. DENSE_RANK 함수를 사용해서 조회한 결과를 보면, SAL(급여) 값이 동일한 경우에는 순위 값이 '2'로 중복 순위를 부여합니다. 중복 순위 다음에는 순위 값이 '3'입니다. 앞에서 1등 1명, 2등 2명이 존재하지만, 다음 순위의 값은 '3'입니다. 이 점이 RANK 함수와 차이점입니다. RANK 함수에서는 순위 값을 '3'이 아니라 '4'를 부여했었습니다.

SELECT 컬럼1, 컬럼2, 컬럼3, DENSE_RANK() OVER (ORDER BY 기준_컬럼 DESC) AS 별명 FROM 테이블;

Oracle DENSE_RANK 함수 사용 예제

ROW_NUMBER 함수

ROW_NUMBER 함수는 중복 관계없이 순차적으로 순위를 반환합니다. 그렇기 때문에 동일한 점수여도 중복 순위가 없습니다. 아래 이미지에서 'SCOTT', 'FORD'는 SAL(급여) 값이 동일하더라도 순위 값이 '2', '3'으로 출력 되는 것을 확인할 수 있습니다.

지금까지 순위를 결정하는 컬럼을 1개만 사용했는데, 순위를 결정하는 다른 컬럼을 사용해도 됩니다. 기존 "ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RANK" 문장을 "ROW_NUMBER() OVER (ORDER BY SAL DESC, HIRE_DATE DESC) AS RANK" 으로 변경합니다. 변경하고 다시 조회하면 먼저 SAL(급여)로 내림차순하고, 급여가 동일한 경우 HIRE_DATE(고용날짜)를 기준으로 다시 차등 순위를 매길 수 있습니다.

SELECT 컬럼1, 컬럼2, 컬럼3, ROW_NUMBER() OVER (ORDER BY 기준_컬럼 DESC) AS RANK FROM 테이블;

Oracle ROW_NUMBER 함수 사용 예제

그룹별 순위

Rank 함수(또는 DENSE_RANK, ROW_NUMBER)를 이용해서 그룹별 순위를 매길 수 있습니다. 예를 들어, 부서별 직원들 급여를 순위를 매길 때 아래 쿼리를 사용하면 됩니다. 어떤 순위 함수를 쓰는지에 따라 순위 결과 값은 다르게 나옵니다. 

-- EMP 테이블에서 부서별 직원들 급여 순위 SELECT DEPTNO, EMPNO, ENAME, SAL, RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK FROM SCOTT.EMP;

Oracle 그룹별 순위 함수 사용 예제

 

 

출처 : m.blog.naver.com/gglee0127/221318160003