본문 바로가기
카테고리 없음

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

by 월급쟁이 자유인 2018. 12. 12.
반응형

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


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


1
2
3
4
5
6
7
8
9
10
11
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);
cs


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


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


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


1
2
3
4
5
6
7
8
9
10
11
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);​
cs


이런 테이블이 있을 때


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


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


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


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


1
2
3
4
5
6
7
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
cs


반응형

태그

, ,

댓글0