Oracle (3) - 순위처리를 해보자 (RANK, DENSE_RANK, ROW_NUMBER)

쿼리를 작업할 때 성적 및 순위 처리할 일들이 있습니다. 해당 데이터를 RANK 함수를 이용하여 순위처리 하는 방법을 정리하려고 합니다.

 

예시 테이블

STNT_NO	GRADE	KOREAN	MATH	ENGLISH	TOTAL
2017111	3	100	90	80	270
2018112	2	90	90	90	270
2019113	3	50	50	60	160
2020114	1	60	70	80	210

 

RANK, DENSE_RANK, ROW_NUMBER

SELECT STNT_NO
     , TOTAL
     , RANK() OVER (ORDER BY TOTAL DESC)        AS RANK
     , DENSE_RANK() OVER (ORDER BY TOTAL DESC)  AS DENSE_RANK
     , ROW_NUMBER() OVER (ORDER BY TOTAL DESC)  AS ROW_NUMBER
  FROM RECORD 
 ORDER BY TOTAL DESC

세 함수의 가장 큰 차이점은 순위처리 방식에 대하여 차이가 있습니다.

STNT_NO	TOTAL	RANK	DENSE_RANK	ROW_NUMBER
2017111	270	1	1	1
2018112	270	1	1	2
2020114	210	3	2	3
2019113	160	4	3	4

위 결과를 보면 세 함수의 차이점을 이렇게 정리할 수 있습니다.

• RANK 함수는 중복 개수만큼 다음 순위 값을 증가합니다.
• DENSE_RANK 함수는 중복 순위가 존재해도 순차적으로 다음 순위 값을 표시합니다.
• ROW_NUMBER 함수는 중복이 있어도 전체에 대하여 순차적으로 순위 값을 표시합니다.

순위가 겹치지 않는 경우에는 세 함수의 결과는 동일합니다.

 

사용예시

순위 중복 제거 방법

SELECT STNT_NO
     , GRADE
     , TOTAL
     , RANK() OVER (ORDER BY TOTAL DESC, GRADE DESC) AS RANK
FROM RECORD
ORDER BY TOTAL DESC, GRADE DESC;

Rank Over 구문 안에 1개만 넣는 경우 첫 번째 예제처럼 같은 순위가 나올 수 있습니다. 이것을 해결하기 위해서 Rank Over 구문 중 Order By 안에다가 여러 기준을 넣는 경우 중복을 해결할 수 있습니다. (실행속도는 다소 느려짐)

STNT_NO	GRADE	TOTAL	RANK
2017111	3	270	1
2018112	2	270	2
2020114	1	210	3
2019113	3	160	4

 

그룹별 순위 처리

SELECT STNT_NO
     , GRADE
     , TOTAL
     , RANK() OVER (PARTITION BY GRADE ORDER BY TOTAL DESC)        AS RANK
  FROM RECORD 
 ORDER BY GRADE DESC, TOTAL DESC

Group By를 이용하여 데이터를 각각 묶는 것과 같이 PARTITION BY를 이용하여 그룹별로 순위를 표시할 수 있습니다. 해당 예제에서는 GRADE로 각 학년의 순위를 표시할 수 있도록 쿼리문을 작성하였습니다.

STNT_NO	GRADE	TOTAL	RANK
2017111	3	270	1
2019113	3	160	2
2018112	2	270	1
2020114	1	210	1

 

 

그룹별 최값, 최댓값 구하기

SELECT STNT_NO
     , GRADE
     , TOTAL
     , MIN(TOTAL) KEEP(DENSE_RANK FIRST ORDER BY TOTAL) OVER (PARTITION BY GRADE)        AS GRADE_TOTAL_MIN
     , MAX(TOTAL) KEEP(DENSE_RANK LAST ORDER BY TOTAL) OVER (PARTITION BY GRADE)         AS GRADE_TOTAL_MAX
     , RANK() OVER (PARTITION BY GRADE ORDER BY TOTAL DESC)        AS RANK
  FROM RECORD 
 ORDER BY GRADE DESC, TOTAL DESC

 

KEEP() 함수 안에 FIRST, LAST 키워드를 활용하여 대상이 되는 그룹 안에 최솟값, 최댓값을 얻을 수 있습니다. 해당 예제는 DENSE_RANK 함수만 사용할 수 있습니다.

STNT_NO	GRADE	TOTAL	GRADE_TOTAL_MIN	GRADE_TOTAL_MAX	RANK
2017111	3	270	160	270	1
2019113	3	160	160	270	2
2018112	2	270	270	270	1
2020114	1	210	210	210	1

 

 

해당 함수들을 이용하여 순위처리 뿐만이 아닌 다양한 방법으로 사용하기 때문에 어떻게 사용되는지 방법을 익히면 많은 도움이 됩니다.

 

참고한 게시글

https://gent.tistory.com/266

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