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

• target:  RANK, DENSE_RANK 함수 사용법 (feat. OVER, PARTITION BY)

 

---

• method: 

1. RANK / DENSE_RANK

오라클에서 성적, 급여, 매출 등 순위를 구하기 위해서 순위 함수(RANK, DENSE_RANK)를 사용

순위 함수의 순위는 OVER ㅎ마수 내부의 ORDER BY 컬럼 값으로 결정

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
 
/*
ENAME  |  SAL  |  RANK  |  DENSE_RANK
KING   | 5000  |  1     |  1
SCOTT  | 3000  |  2     |  2
FORD   | 3000  |  2     |  2
JONES  | 2975  |  4     |  3
BLAKE  | 2850  |  5     |  4
CLARK  | 2450  |  6     |  5
ALLEN  | 1600  |  7     |  6
TURNER | 1500  |  8     |  7
*/

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

※ DENSE_RANK() 함수는 2순위가 2명이어도, 다음 순위는 3순위로 표시

---

2. 중복 순위 값 제거

순위가 중복되지 않도록 하기 위해서 OVER 함수 내부에 ORDER BY 컬럼을 추가하여 세부적인 순위를 설정

SELECT ENAME
     , SAL
     , COMM
     , RANK() OVER (ORDER BY SAL DESC, COMM DESC)  RANK
  FROM EMP
 ORDER BY SAL DESC
        , COMM DESC
 
/*
ENAME  |  SAL  |  COMM  |  RANK
KING   | 5000  |  0     |  1
SCOTT  | 3000  |  100   |  2
FORD   | 3000  |  0     |  3
JONES  | 2975  |  0     |  4
BLAKE  | 2850  |  50    |  5
CLARK  | 2450  |  50    |  6
ALLEN  | 1600  |  300   |  7
TURNER | 1500  |  0     |  8
*/

---

3. 그룹별 순위 구하기

그룹별 순위 부여 시, 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
 
/*
DEPT       | ENAME  |  SAL  |  COMM  |  RANK
ACCOUNTING | KING   | 5000  |  0     |  1
ACCOUNTING | CLARK  | 2450  |  50    |  2
RESEARCH   | SCOTT  | 3000  |  100   |  1
RESEARCH   | FORD   | 3000  |  0     |  2
RESEARCH   | JONES  | 2975  |  0     |  3
SALES      | BLAKE  | 2850  |  0     |  1
SALES      | ALLEN  | 1600  |  300   |  2
SALES      | TURNER | 1500  |  0     |  3
*/

---

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

KEEP() 함수와 FIRST/LAST 키워드를 통해 DENSE_RANK 함수에서 추출

SELECT DEPT
     , ENAME
     , SAL
     , MIN(SAL) DEEP(DENSE_RANK FIRST ORDER BY SAL) OVER(PARTITION BY DEPT) SAL_MIN
     , MAX(SAL) DEEP(DENSE_RANK LAST ORDER BY SAL) OVER(PARTITION BY DEPT) SAL_MAX
  FROM EMP
 ORDER BY DEPT
        , SAL DESC
 
/*
DEPT       | ENAME  |  SAL  |  SAL_MIN  |  SAL_MAX
ACCOUNTING | KING   | 5000  |  2450     |  5000
ACCOUNTING | CLARK  | 2450  |  2450     |  5000
RESEARCH   | FORD   | 3000  |  2975     |  3000
RESEARCH   | SCOTT  | 3000  |  2975     |  3000
RESEARCH   | JONES  | 2975  |  2975     |  3000
SALES      | BLAKE  | 2850  |  1500     |  2850
SALES      | ALLEN  | 1600  |  1500     |  2850
SALES      | TURNER | 1500  |  1500     |  2850
*/

---

• source:

https://gent.tistory.com/266

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