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

오라클을 사용하다가 MySQL을 사용하는 환경으로 옮겼을 때 여러가지 어려움을 만나게 된다. 오라클에서 지원하는 기능을 MySQL에서 지원하지 않는 경우인데 그 중 하나가 랭크(Rank)와 백분위(Percentile)를 구해야하는 경우다.

---

MySQL 랭크 쿼리

오라클의 경우 테이블에 다음과 같은 데이터가 들어있을 때

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);

rank() 함수를 이용해서 각 Row 들의 랭크를 구할 수 있다.

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

위 쿼리를 실행하면 각 행별로 쿼리 결과 안에서의 랭크를 구해준다.

MySQL에서는 이 쿼리를 실행하면 에러가 발생한다. 대신 다음 쿼리로 rank() 함수를 대신할 수 있다.

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

MySQL에서 제공하는 '사용자 정의 변수(User-defined Variables)'를 이용한 방법으로 rank 라는 변수를 만들어 0으로 초기화하고 정렬해서 가장 큰 데이터를 기준으로 랭크 값을 1씩 증가시키는 방법이다.

---

MySQL 백분위(Percentile) 쿼리

Rank 쿼리를 응용해서 특정 값을 기준으로 백분위를 구하는 쿼리도 짜볼 수 있다.

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