MYSQL RANK - DB CAFE

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

   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;

위 Query의 결과는 다음과 같다.

랭킹의 초기값이 0 이 아닌 20으로 설정이 되어있고, 랭킹의 증감이 +2 로 설정되었기 떄문에 22위 부터 랭킹이 만들어 지는것을 확인할 수 있다.

3. MySQL 의 사용자 정의 변수를 이용하여 동점 랭킹을 만드는 방법

사용자 변수를 이용하여 간단하게 만드는 경우는 동점자도 모두 Query 의 ORDER 에 따라 순차적으로 순위가 만들어지는데, 랭킹의 종류에 따라 동점자는 같은 순위로 표시를 하고 싶을때가 있다. SELECT

   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;

위와 같이 Query를 실행하면 다음과 같은 결과가 나온다.

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만으로 보여주는것이 좋을것이다.