MySQL에는 Rank() 함수가 없다.
기본적으로 MySQL 에는 Oracle이나 MS-SQL같은 RDBMS 에서 지원하는 Rank() 함수가 없다.
그러나 Query를 이용해서 다른 DB들의 Rank() 함수과 같은 효과를 만들 수 있다.
기본 Table 구조
Table의 내용이 다음과 같이 입력되어 있다고 하자.
해당 Table 의 ranking_score 를 이용해 ranking_score가 큰 순서대로 ranking을 만드는 쿼리를 몇 가지 방법으로 만들어보자.
1. COUNT 를 이용해서 만드는 방법
SELECT
ranking_no, ranking_score, ranking_name,
( SELECT COUNT(*) + 1 FROM ranking WHERE ranking_score > b.ranking_score ) AS rank
FROM
ranking_score AS b
ORDER BY
rank ASC
위 Query의 결과는 다음과 같다.
그러나 위와 같은 Query는 Table 안의 데이터가 많아질수록 속도가 저하될 수 밖에 없다.
모든 Row 별로 "SELECT COUNT(*) + 1 FROM ranking WHERE ranking_score > b.ranking_score" Query가 실행되기 때문이다.
또, 동점자의 경우 같은 순위로 표시할거라면 위와 같이 해도 문제가 없지만 동점자도 순위를 매겨야 한다면 위와 같은 Query로는 구현하기 힘들다.
2. MySQL 의 사용자 정의 변수를 이용하여 만드는 방법
SELECT
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 1 ) AS rank
FROM
ranking AS a,
( SELECT @rank := 0 ) AS b
ORDER BY
a.ranking_score DESC;
위 Query의 결과는 다음과 같다.
COUNT를 이용해 만든 결과와 다르게 rank 가 모두 순서대로 등록이 되어 있는것을 확인할 수 있다.
MySQL 에도 프로그램의 변수와 같은 개념으로 "@변수명" 을 이용해 값을 유지할 수 있다.
다만 "@변수명" 은 모든 connect 에 유효한 값이 아니고, 해당 connect session 에서만 유효한 값이다.
자세한건 "http://dev.mysql.com/doc/refman/5.7/en/user-variables.html"를 참고해보자.
Query에 대해서 간단히 설명을 하자면 다음과 같다.
(1) ( SELECT @rank := 0 ) AS b
이 부분은 rank라는 변수에 값을 0 으로 초기화를 해주는 부분이다.
보통 프로그램에서는 $rank = 0 처럼 사용을 하는데, mysql 에서 "=" 는 같다는 의미로 사용이 되고 있기 때문에 값을 넣을때 ":=" 를 이용해서 값을 넣어준다. ( 프로시져에서는 "=" 를 사용한다. )
위 예제 Query에서는 Ranking 의 시작이 1부터이기 때문에 초기값을 0 으로 잡아주었지만 데이터가 많아서 페이징 처리를 해야 한다면 초기값이 0 이 아닌 다른 값이 되어야 할수도 있다.
그럴경우 적절한 값을 넣어주면 된다.
(2) ( @rank := @rank + 1 ) AS rank
이 부분이 각각의 Row 에서 rank 를 +1 씩 해주는 부분이다.
만약 랭킹을 +1 단위가 아닌 + 2 단위로 하고 싶다면 "( @rank := @rank + 2 ) AS rank" 처럼 바꿔주면 된다.
랭킹의 초기값을 20, 랭킹의 증가값을 +2 로 만들어보자.
SELECT위 Query의 결과는 다음과 같다.
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 2 ) AS rank
FROM
ranking AS a,
( SELECT @rank := 20 ) AS b
ORDER BY
a.ranking_score DESC;
랭킹의 초기값이 0 이 아닌 20으로 설정이 되어있고, 랭킹의 증감이 +2 로 설정되었기 떄문에 22위 부터 랭킹이 만들어 지는것을 확인할 수 있다.
3. MySQL 의 사용자 정의 변수를 이용하여 동점 랭킹을 만드는 방법
사용자 변수를 이용하여 간단하게 만드는 경우는 동점자도 모두 Query 의 ORDER 에 따라 순차적으로 순위가 만들어지는데, 랭킹의 종류에 따라 동점자는 같은 순위로 표시를 하고 싶을때가 있다.
rank 와 real_rank 의 값을 비교해보자.
real_rank 에서는 COUNT를 이용한 랭킹 Query 처럼 동점자를 표시해주고 있다.
언뜻 봐서는 Query가 복잡해 보일 수 있지만 그다지 복잡한 내용은 아니다.
이 부분이 복잡해 보일 수 있는 부분이지만 크게 두개로 나뉜다고 보면 된다.
- IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank )
이 부분은 MySQL의 IF문을 이해한다면 금방 이해할 수 있다.
즉 "@last"의 값과 현재 row의 ranking_score값을 비교해서 값이 같거나 작은경우는 "@real_rank"를 +1 하지 않고 그 전 단계의 값을 반환한다.
- IF 문을 통해서 반환된 값이 "@real_rank"에 저장된다. 다음번 row에서 ranking_score의 값이 증가하지 않았다면 저장된 값을 그대로 반환할 것이다.
다음번 IF 문을 위해서 "@last" 변수에 최종 ranking_score 값을 저장해 두는 부분이다.
위와 같은 Query를 이용한다면 랭킹을 좀 더 효율적으로 원하는대로 구할 수 있을 것이다.
아무리 Ranking Query를 효율적으로 만들어도 매번 위와 같이 Query를 날려서 가져오도록 서비스를 구성한다면 사용자가 많아질수록 기하급수적으로 효율이 안좋아 질 수 밖에 없다.
실시간으로 랭킹정보를 보여줘야 한다면 어쩔 수 없겠지만, 가능하다면 매번 랭킹을 계산하기 보다는 계산이 완료된 정보를 다른 Ranking 결과 Table에 저장하여 SELECT만으로 보여주는것이 좋을것이다.
SELECT위와 같이 Query를 실행하면 다음과 같은 결과가 나온다.
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 1 ) AS rank,
( @real_rank := IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank ) ) AS real_rank,
( @last := ranking_score )
FROM
ranking AS a ,
( SELECT @rank := 0, @last := 0, @real_rank := 1 ) AS b
ORDER BY
a.ranking_score DESC;
rank 와 real_rank 의 값을 비교해보자.
real_rank 에서는 COUNT를 이용한 랭킹 Query 처럼 동점자를 표시해주고 있다.
언뜻 봐서는 Query가 복잡해 보일 수 있지만 그다지 복잡한 내용은 아니다.
(1) ( @real_rank := IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank ) ) AS real_rank
이 부분이 복잡해 보일 수 있는 부분이지만 크게 두개로 나뉜다고 보면 된다.
- IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank )
이 부분은 MySQL의 IF문을 이해한다면 금방 이해할 수 있다.
IF ( 조건, 조건이 TRUE일경우, 조건이 FALSE일 경우)위 내용대로 Query를 설명한다면 만약 "@last"의 값이 ranking_score보다 큰 경우 "@real_rank:=@real_rank+1" 을, 그렇지 않다면 "@real_rank" 를 반환한다.
즉 "@last"의 값과 현재 row의 ranking_score값을 비교해서 값이 같거나 작은경우는 "@real_rank"를 +1 하지 않고 그 전 단계의 값을 반환한다.
- IF 문을 통해서 반환된 값이 "@real_rank"에 저장된다. 다음번 row에서 ranking_score의 값이 증가하지 않았다면 저장된 값을 그대로 반환할 것이다.
(2) ( @last := ranking_score )
다음번 IF 문을 위해서 "@last" 변수에 최종 ranking_score 값을 저장해 두는 부분이다.
위와 같은 Query를 이용한다면 랭킹을 좀 더 효율적으로 원하는대로 구할 수 있을 것이다.
매번 랭킹을 구하지 말고 어딘가에 저장을 해두자.
아무리 Ranking Query를 효율적으로 만들어도 매번 위와 같이 Query를 날려서 가져오도록 서비스를 구성한다면 사용자가 많아질수록 기하급수적으로 효율이 안좋아 질 수 밖에 없다.
실시간으로 랭킹정보를 보여줘야 한다면 어쩔 수 없겠지만, 가능하다면 매번 랭킹을 계산하기 보다는 계산이 완료된 정보를 다른 Ranking 결과 Table에 저장하여 SELECT만으로 보여주는것이 좋을것이다.
안녕하세요 쿼리에 궁금한게 있는데 질문 가능한가요?
답글삭제MySQL 8.0 업데이트 이후 RANK() 함수가 추가되었습니다 확인해주세요.
답글삭제