[MYSQL] RANK/DENSE_RANK/ROW_NUMBER 함수

RANK/DENSE_RANK/ROW_NUMBER 함수는 모두 특정 열의 값에 대해 순위를 매기는 함수다.

 

서식은 다음과 같다

 

RANK() OVER([PARTITION BY COL_NAME_1] ORDER BY COL_NAME_2 [DESC])

 

RANK대신 DENSE_RANK/ROW_NUMBER로 대치해서 사용할 수 있다.

[...]로 된 부분은 생략 가능하다.

 

다른 함수들과 달리 꽤나 괴상하게 생긴 함수인데, 그렇게 어려운 부분은 없으니 겁먹지 않아도 된다.

 

먼저 [PARTITION BY COL_NAME_1] 부분 없이 알아보자.

 

RANK() OVER (ORDER BY COL_NAME_2 DESC)는

COL_NAME_2값을 내림차순으로 순위를 매기는 함수다.

 

즉 다음과 같은 데이터가 있다고 해보자.

 

departmemt_id	name	salary
1	jane	2000
1	sam	3000
2	micheal	4000

<<Salary Table>>

 

여기서 다음과 같은 쿼리를 실행한다.

 

SELECT name, RANK() OVER (ORDER BY salary) AS salary_rank FROM Salary;

 

name	salary_rank
jane	3
sam	2
micheal	1

그러면 위와 같은 결과를 얻을 수 있다.

 

salary 열 값에 따라 내림차순으로 순위를 매기므로

가장 높은 micheal이 1순위고, 가장 낮은 jane이 3순위가 된다.

 

그렇다면 DENSE_RANK/ROW_NUMBER와 RANK의 차이점은 뭘까?

RANK	공동 순위만큼 건너뜀 (ex: 1,2,2,4 ...)
DENSE_RANK	공동 순위를 뛰어넘지 않음 (ex: 1,2,2,3 ...)
ROW_NUMBER	공동 순위를 무시함 (ex: 1,2,3,4 ...)

가령 salary가 (2000,3000,3000,4000) 라고 할 때,

RANK를 사용하면 (4,2,2,1)이 출력되고

DENSE_RANK는 (3,2,2,1)이,

ROW_NUMBER는 (4,3,2,1)이 출력된다.

 

PARTITION BY COL_NAME

departmemt_id	name	salary
1	jane	2000
1	sam	3000
2	micheal	4000

마찬가지로 Salary 테이블에서 salary순으로 출력하고 싶은데,

부서별로 순위를 각각 매기고 싶을 때 PARTITION BY COL_NAME 을 사용한다.

 

위의 요구사항을 위해 다음 쿼리를 작성할 수 있다.

 

SELECT name, RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS salary_rank FROM Salary;

name	salary_rank
jane	2
sam	1
micheal	1

결과로 위를 얻을 수 있다.

 

department_id별로 순위를 따로 매기기 때문에,

department_id가 1인 jane과 sam끼리만 순위를 매기게 되어,

sam이 1, jane이 2를 얻게 된다.

 

주의할 점

salary_rank를 WHERE 문에서 사용하면 '열을 찾을 수 없다'는 오류가 발생한다.

(Unknown column 'salary_rank' in 'where clause')

 

가령

SELECT name, RANK() OVER (ORDER BY salary) AS salary_rank FROM Salary WHERE salary_rank=1;

위와 같이 실행하면 오류가 발생한다.

 

Salary 테이블에는 salary_rank 열이 존재하지 않기 때문에 발생하는데, 이 때는 서브쿼리를 이용해 salary_rank를 먼저 뽑아낸 후,

메인 쿼리에서 salary_rank를 필터링한다.

 

SELECT * FROM (

  SELECT name, RANK() OVER (ORDER BY salary) AS salary_rank FROM Salary

) Salary_rank

WHERE salary_rank = 1;

 

이렇게 하면 Salary_rank라는 뷰는 salary_rank라는 열을 가지고 있기 때문에 정상적으로 출력된다.