Analytic SQL 2. 순위(Rank) 함수

Analytic 함수에서 순위로 작동되는 함수에 대해서 알아보자. 일반적인 함수와 더불어 어떠한 방식으로 사용되는지도 아래의 표를 통해 간단히 살펴본다.

 

설명	종류
일반적인 순위	rank, dense_rank, row_number
0~1 사이의 정규화 순위	cume_dist(누적 분포), percent_dist
분위	ntile

 

등 제공하는 여러 함수가 있지만 가장 보편적으로 사용되는 일반적인 순위(rank, dense_rank, row_number)에 대해서 먼저 살펴보도록 하자.

 

세 가지의 일반적인 순위 함수의 로직은 기본적으로 동일하게 작동하나 공동 순위를 정함에 있어서 다른 차이를 보인다는 특징이 있음

 

rank

우리가 생각하는 일반적인 등수를 반환. 이때, 공동 순위가 존재할 경우 다음 순위는 공동 순위 개수만큼 밀려서 정해진다.

가령 2등이 2명일 경우 그 이후 순위는 4등으로 지정(1,2,2,4등 반환)

아래는 SQL 구문 사용 예시

select rank() over(order by money desc) as rank
from a_table;
-- a_table에서 money를 기준으로 rank 함수를 내림차순으로 처리

 

dense_rank

일반적 등수 반환. 이 때 기존 rank와 다르게 공동 순위가 존재할 경우에도 다음 순위는 바로 이어져서 정해진다는 특징

가령 2등이 2명이어도 그 이후 순위는 3위부터 지정(1,2,2,3등 반환)

아래는 SQL 구문 사용 예시

select dense_rank() over(order by money desc) as dense_rank
from a_table;
-- a_table에서 money를 기준으로 dense_rank 함수를 내림차순으로 처리

 

row_number

일반적 등수 반환. 하지만 상기 두 함수와 다르게 공동 순위가 존재한다 하더라도 반드시 unique한 순위를 정해줌

가령 실제 2등이 2명이어도 내부 로직(랜덤)에 따라 1,2,3,4등이 지정됨(1,2,3,4등 반환)

아래는 SQL 구문 사용 예시

select row_number() over(order by money desc) as row_number
from a_table;
-- a_table에서 money를 기준으로 row_number 함수를 내림차순으로 처리

 

업무적으로는 고유한 값을 정해주는 것이 가장 많기 때문에 생각보다 row_number 함수를 사용하는 경우가 많다고 한다.

또한 order by에 별다른 값을 주지 않을 경우 기본적으로 오름차순을 기준으로 정렬됨

 

기존 partition과 응용하여 다음의 코드를 읽고 이해한다면 다음으로 넘어가도 무방할 듯싶다.

select rank() over(partition by grade order by money desc) as rank
,dense_rank() over(partition by grade order by money desc) as dense_rank
,row_number() over(partition by grade order by money desc) as row_number
from a_table
-- 1. 각각의 학생들이 학년별로 나누어지고
-- 2. 학년별로 소지한 돈을 기준으로 내림차순 정렬

 

partition을 나누었다면 해당 파티션 각각을 독립된 테이블들로 보고 정렬 코드를 짜는 것이 유리해 보인다.

 

여기서 가장 중요한 점 한 가지 - Null의 처리

만약 Null이 존재하는 컬럼에서 순위 함수를 처리하게 되면 무조건적으로 1순위를 반환(Nulls first)한다. 이는 대체적으로 의도하지 않은 결과를 내뿜기 때문에 다음과 같은 절을 추가할 수 있다.

select rank() over(order by money desc nulls last) as rank
from a_table;
-- a_table에서 money를 기준으로 rank 함수를 내림차순으로 처리
-- 만약 null이 존재한다면 마지막으로 처리

 

Null의 다른 처리로 coalesce 함수를 이용하여 0으로 대체 후 정렬하는 방법도 있지만, 상황에 따라 0도 사용하는 경우도 충분히 존재하기 때문에 적절한 상황에서 적절한 키워드를 사용해야 한다.

select rank() over(order by coalaesce(money, 0) desc) as rank
from a_table;
-- a_table에서 money를 기준으로 rank 함수를 내림차순으로 처리
-- null을 0으로 처리한 후 내림차순 정렬하면 자연스레 후순위로 넘어감

 

상기의 코드도 의도에 따라 정상 작동하겠지만, 요청에 따라 돈이 0원이 사람도 필요하다면 상기의 코드는 사용하면 안 된다!   

 

아무런 세팅을 하지 않았다면 정렬은 오름차순, null의 처리는 1순위로 됨을 기억하면서 세팅을 하자.

---

as절을 이용하여 만든 select 절을 이용하고 싶으면 서브 쿼리를 사용하는 듯하다.

서브 쿼리도 중요한 내용이니 나중에 기회가 되면 공부하도록 하자!

아래의 코드는 학년별로 소지한 돈이 가장 많은 학생 한 명을 추출해내는 쿼리문. 이때, 순위의 중복은 존재하지 않는다.

select *
from(
	select *
    	,row_number() over(partition by grade order by money desc) as mon_desc
    from a_table
) a from mon_desc = 1;