mysql - rank, dense_rank, row_number

목차

시작

함수 사용

정리

시작

sql문제를 풀다 보면 순위를 정해야 할 때가 있습니다.

이때 rank, dense_rank, row_number 중 1개를 사용하면 됩니다.

사용 방식은 아래와 같습니다.

select 
	col1, 
	rank() over( [ partition by co2 ] order by col3 [desc] )
    from table
    
---    []는 생략 가능

예를 들어 아래와 같은 테이블 test_result가 있다고 생각해봅시다.

id	name	coding_test_score
1	kim	100
2	kim	90
3	han	80

이 테이블에서 순위를 알기 위해 아래와 같은 쿼리를 실해하면,

select test_result.*, rank() over(order by coding_test_score desc) as rank
from test_result

이와 같은 결과를 얻을 수 있습니다.

id	name	coding_test_score	rank
1	kim	100	1
2	kim	90	2
3	kim	80	3

rank, dense_rank, row_number 차이점

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

만약 coding_test_score가 (60,90,90,100)라고 할 때,

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

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

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

PARTITION BY COL_NAME

이름 별로 순위를 매기고 싶을 떄 사용됩니다.

만약 partition by name을 추가하면

위의 상황에서 kim이 2명이 있습니다.

여기서 1,2위가 정해지고,

han에서 1위가 정해지게 됩니다.

select test_result.*, rank() over(partition by name order by coding_test_score desc) as rank
from test_result

id	name	coding_test_score	rank
1	kim	100	1
2	kim	90	2
3	kim	80	1

where과 사용

rank()함수와 where를 같이 사용하면 에러가 생성됩니다.

이유는 존재하지 않는 칼럼을 다이내믹하게 생성 하는 rank() 함수를 사용하였기 때문입니다.

with as구문 혹은 sub쿼리를 이용하여 사용하면 에러 없이 사용 가능합니다.

정리

rank를 사용한 select문을 가공하기 위해서는 서브 쿼리를 이용해야 합니다.

실수가 많은 부분이니 기억해둡시다.