[Oracle] 오라클 RANK, DENSE_RANK 순위 함수 사용법 (over, partition by)

오라클에서 성적, 급여, 매출 등 순위를 구하기 위해서 순위 함수(RANK, DENSE_RANK)를 사용하면 된다. 순위 함수의 순위는 OVER 함수 내부의 ORDER BY 컬럼 값으로 결정된다.

 

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

 

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

 

 

예제_쿼리_1(테이블포함).txt

0.00MB

 

RANK() 함수는 2순위가 2명 이므로 3순위를 건너뛰고 2순위 다음은 4순위로 표시된다. 

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

 

순위가 겹치지 않는다면 RANK나 DENSE_RANK는 큰 차이가 없다.

 

 

중복 순위 값 없애기

 

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

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

 

 

그룹별 순위 구하기

 

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

 

아래는 부서별 급여 순위를 조회한 예제이다.

 

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

 

 

예제_쿼리_2(테이블포함).txt

0.00MB

 

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

 

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

 

아래는 부서별 최고 급여, 최소 급여를 추가로 표시하는 예제이다.

 

SELECT DEPT 
     , ENAME 
     , SAL 
     , MIN(SAL) KEEP(DENSE_RANK FIRST ORDER BY SAL) OVER(PARTITION BY DEPT) SAL_MIN 
     , MAX(SAL) KEEP(DENSE_RANK LAST ORDER BY SAL) OVER(PARTITION BY DEPT)  SAL_MAX 
  FROM EMP 
 ORDER BY DEPT, SAL DESC

 

 

예제_쿼리_3(테이블포함).txt

0.00MB