[SQL고급] rank함수 - 랭킹(순위) 구하기 (MySQL 8버전)

예를 들어 id, amount(금액)에 관련된 데이터가 있다고 치자.

 

이때 금액이 많은 순대로 순위를 매기고 싶다면? 

여러가지 방법이 있다. 특히 mysql8 버전이라면 rank함수를 이용할 수 있으니 이걸 기억하자!

 

1. 사용자정의 변수 이용

SELECT id, amount,(@rank := @rank   + 1 ) AS ranking
FROM ex_card AS a,(select @rank  := 0 ) AS b
ORDER BY a.amount DESC;

사실상 순위를 직접 뽑아낸다기보다 rownum을 계산해서 그걸 순위인것처럼 사용하는걸로 볼 수 있다.

그래서 보통 변수명을 rownum으로 사용하는 경우가 많다.

그리고 하나의 row가 생길때마다 랭킹이 하나씩 더해지는 방식이기 때문에

정렬을 하고 같은 경우이더라도 다른 순위를 가진다 (amount 2000이 같을 경우 9,10 랭킹) 

프로그램 변수처럼 @변수명을 생성해서 값을 유지할 수 있다.

다만 @변수명은 모든 connect에 유효한 값이 아니고 해당 connect session에서만 유효하다.

참고로, 여기문제에서 4. 입양시각 구하기2 문제에서 시간의 범위를 구할때 사용했었다.

 

2. rank 함수 이용 - rank()

select id, amount, rank() over (order by amount desc) as ranking 
from ex_card

rank() over (order by 정렬할기준) 으로 하나의 컬럼을 생성할 수 있다.

지금은 금액이 많은 순서대로 랭킹을 부여했다. 

 

아까와 다르게 금액이 같은 경우일 경우 같은 순위를 가진다.  (amount 2000이 같을 경우 둘다 랭킹 9)

 

그리고 또하나, 같은 경우가 생기고 난 다음에는 다음순위는 건너뛴다. 

그래서 랭킹 4가 두개가 나타나서 그 다음엔 5가 아니라 6이 나타나게 된다. 

 

2. rank 함수 이용 - dense_rank()

select id, amount, dense_rank() over (order by amount desc) as ranking 
from ex_card

그냥 rank()와 같은점과 다른 점을 살펴볼 수 있다.

 

그냥 rank()처럼 금액이 같은 경우일 경우 같은 순위를 가진다.  (amount 2000이 같을 경우 둘다 랭킹 8)

 

하지만, 같은 경우가 생기고 난 다음에는 다음순위는 이어간다

그래서 랭킹 4가 두개가 나타났지만, 그 다음엔 5가 나타나게 된다. 

 

 

그럼, 금액이 많은 순대로 순위를 매기고, 랭킹이 5인 금액을 구하라면? 

 

사실 이거때문에 내가 너무 많이 뻘짓(?)을 했다ㅠㅠㅠㅠㅠㅠ

나는 그냥 직관적으로 아래처럼 where 조건을 걸으면 된다고 생각했는데, 'ranking'이란 컬럼이 없다며 오류를 뱉었다.

select id, amount, rank() over (order by amount desc) as 'ranking' 
from ex_card 
where ranking =5

# Error Code: 1054. Unknown column 'ranking' in 'where clause'

생각해보니 위에처럼 중복된 값이 있어서 랭킹5가 없을수도 있겠지만, (랭킹6으로 바꿔봐도 안되는걸 보니 아닌듯) 

아예 where절이 스캔하는 테이블엔 ranking이라는 컬럼이 사실 없는 것이다! 

그냥 이제 위에 쿼리로 인해서 ranking이라는 컬럼을 가진 테이블이 생겨났다고 보면 된다.

 

그래서 이걸 해결하기 위해서는 서브쿼리를 사용해야한다. 

위의 쿼리를 from절안에 다시 넣어서 ranked라는 테이블을 만들고, 그 안에서 where 조건을 걸면 된다. 

Select * 
FROM (
	select id, amount, rank() over (order by amount desc) as 'ranking' 
	from ex_card 
    ) ranked
where ranked.ranking =5

아, 그렇다. 이렇게 수행한 결과 처음에 예상했던거처럼 중복된 값이 있어서 랭킹5가 없기 떄문에 row가 0개이다. 

대신 랭킹5이하의 경우( where ranked.ranking <5 ) 에 대해서 쿼리조건을 바꿔보면 아래와 같다. 

즉, 랭킹5이하의 경우니까 결국 amount가 많은 순으로 5개가 될 것이다. 

배운점 

• 랭킹? -> 항상 같은 값이 있을수도 있다는걸 생각하기, 그래서 같을때 어떻게 처리해주는지 조건다시 보기
• 랭킹? -> mysql버전확인하고 8이상이면 rank()함수 사용하기

 

 

참고

https://honinbo-world.tistory.com/83

MySQL rank 함수

https://codingdog.tistory.com/entry/sql-rank-over-%ED%95%A8%EC%88%98-%EC%95%8C%EB%A9%B4-%EC%86%90%EC%89%BD%EA%B2%8C-%EB%9E%AD%ED%82%B9%EC%9D%84-%EB%A7%A4%EA%B8%B8-%EC%88%98-%EC%9E%88%EB%8B%A4

sql rank over 함수 : 알면 손쉽게 랭킹을 매길 수 있다.