[Oracle] 순위 결정을 위한 ROW_NUMBER, RANK, DENSE_RANK 함수 사용법 비교

목록을 표시할 때 기존의 SEQ가 아닌 목록 반환 결과에 따른 별개의 목록번호를 반환하고자 하는 경우가 있다.

순차적으로 증가하는 순번을 말하는거라면 정렬 기준만 명확하다면 구성에 큰 무리가 없는데, 만약 순위를 나타내는 번호를 만들어내고 싶다면 이야기가 조금 달라진다.


운동 경기마다도 각자의 룰이 있을텐데 동점인 팀이 두 팀일 때, 둘 다 1등으로 처리하고 그 다음 순위를 2등으로 하는 경기도 있겠지만 1등이 두 팀일 때 그 다음 순위는 3위부터 시작하는 게임도 있을거다.

오늘은 oracle에서 순위를 반환할 수 있는 함수에 대해 알아보고 각 쓰임을 비교해보고자 한다.

[Oracle] 순위 결정을 위한 ROW_NUMBER, RANK, DENSE_RANK 함수 사용법 비교

순위 결정을 위한 함수에는 ROW_NUMBER, RANK, DENSE_RANK가 있는데 공통적으로 반드시 정렬 방식을 지정해야하며, 중복된 데이터 등이 있어서 추가적으로 그룹핑이 필요한 대상은 GROUP BY와 비슷한 역할을 하는 PARTITION BY 라는 명령어를 통해 묶을 수 있다.

1) ROW_NUMBER

가장 익숙한 함수
이 함수는 순위를 결정한다기 보다는 차례로 순번을 매기는데 적합하다. 표현식 또는 특정 필드에 대한 정렬값을 unique한 숫자형태로 반환하는 함수로 겹치는 숫자없이 순번을 매길 때 사용한다.

ROW_NUMBER은 동일한 값이 있는 경우에도 순차적으로 번호를 매기므로, 동일한 값이 발생할 수 있는 필드보다는 구분될 수 있는 필드나 표현식 등을 정렬조건에 추가해주면 좋다.



ROW_NUMBER () OVER( { DIMENSION dimension_id | HIERARCHY hierarchy_id } ORDER BY 정렬조건1 [, 정렬조건2]... [ WITHIN { PARENT | LEVEL | ANCESTOR AT { DIMENSION LEVEL dim_lvl_id | HIERARCHY LEVEL hier_level_id } } ] )

함수 입력방식은 위와 같으며 볼드처리한 대상이 필수 입력값이다.
근데 oracle docs에서 제시하는 함수 사용방법 외에도 종종 보는 방식이 있어서 추가로 소개한다.


ROW_NUMBER () OVER( PARTITION BY 그룹핑 조건 ORDER BY 정렬조건)

PARTITION BY를 이용해서 정렬 기준을 더 세분화하기도 한다. 예를 들어 성별, 나이 등을 추가적인 기준으로 잡고 정렬하고자 할 때 PARTITION BY를 활용하면 좋다.

반응형

 

2) RANK

RANK () OVER ( query_partition_clause ORDER BY order_by_clause)

3) DENSE_RANK

DENSE_RANK () OVER ( query_partition_clause ORDER BY order_by_clause)



RANK 함수와 DENSE_RANK는 이용 방식도 비슷하고 집합 함수와 분석 함수 두 방식으로 사용된다. 이름에서 느낌이 오듯이 우리가 생각하는 순위 방식이다.
사용 방식은 위와 다르지 않으나 두 함수의 큰 차이가 있다.

동일한 점수를 가진 대상이 있을 때, 처리하는 방식!

RANK : 동일한 점수를 가진 대상이 있으면 동일한 등수로 처리하고, 그 다음 순위는 전체를 기준으로 매겨진 등수로 표시한다.
ex) 1, 2, 2, 2, 5, 6 ....
2등이 3명일 때, 그 다음 순위는 전체 기준으로 5번째이므로 5위이다.

DENSE_RANK : 동일한 점수를 가진 대상이 있으면 동일한 등수로 처리하고, 그 다음 순위는 바로 앞 등수의 다음 숫자부터 매긴다.
ex) 1, 2, 2, 2, 3, 4 ....
2등이 3명일 때, 그 다음 순위는 2등에 이어 3등으로 매긴다.




각자 쓰고자하는 쿼리 형태에 따라 잘 선택하여 사용하시길


참고자료 - ORACLE DOCS
https://docs.oracle.com/cd/E11882_01/olap.112/e23381/olap_functions022.htm#OLAXS333

ROW_NUMBER

https://docs.oracle.com/database/121/SQLRF/functions155.htm#SQLRF00690

RANK

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm

DENSE_RANK