[MySQL] 백분위(Percentile), 랭크(Rank) 구하는 쿼리

RDBMS 제품마다 SQL 쿼리 사용법이 약간 다르다. 오라클 사용에 익숙해져 있다가 MySQL을 사용하게 되면서 당황하게 되는 쿼리 중 하나가 랭크(Rank)를 구하는 쿼리다. 

오라클의 경우 다음과 같은 테이블이 있을 때

create table t1(a number);
​
insert into t1 values(3);
insert into t1 values(6);
insert into t1 values(9);
insert into t1 values(2);
insert into t1 values(5);
insert into t1 values(8);
insert into t1 values(1);
insert into t1 values(4);
insert into t1 values(7);

테이블에 들어있는 9개의 Row의 랭크를 구하는 쿼리는 다음과 같다. 

select a, rank() over (order by a desc) as r
from t1;

rank() 함수가 지원된다. 당연하게도 MySQL에서 이 쿼리를 돌리면 에러가 발생한다. MySQL 에서는 Rank 를 다음과 같이 구할 수 있다. 

create table t1(a int);
​
insert into t1 values(3);
insert into t1 values(6);
insert into t1 values(9);
insert into t1 values(2);
insert into t1 values(5);
insert into t1 values(8);
insert into t1 values(1);
insert into t1 values(4);
insert into t1 values(7);​

이런 테이블이 있을 때

select a, @rank:= @rank + 1 ranks
from t1, (select @rank := 0) s
order by a desc

이런 쿼리로 랭크를 구할 수 있다. 

MySQL에서 제공하는 사용자 정의 변수(User-defined Variables)를 이용한 방법으로 rank 라는 변수를 만들어 0으로 초기화, 출력되는 매 Row 마다 @rank := @rank + 1 을 수행해서 1씩 증가시키는 방법이다. 

이를 응용해서 각 Row 의 특정값 기준 백분위를 구하는 쿼리를 짜볼 수도 있다. 

select a, (1 - ranks / totals) * 100 Percentile
from
(
   select a, @rank := @rank + 1 ranks, (select count(*) from t1) totals
   from t1, (select @rank:=0) s
   order by a desc
)s