mysql 컬럼별 상위 N건 조회하는 방법

mysql을 사용하고 있는 상황에서 타입별 보유하고 있는 score별 순위 2개까지의 값을 가져오는 쿼리가 필요했다. 

예를 들면 강아지 페스티벌에 참여한 강아지들의 스코어 별 상위 3위까지의 강아지를 찾는 쿼리가 필요했다.

 

그럼 값을 한번 추출해보자. 우선 전제가 되는 테이블은 아래와 같다.

# database 생성
create database test character set utf8mb4 collate utf8mb4_general_ci;
use test;

# 테이블 생성
create table dog_festival (
 id int primary key not null auto_increment,
 dog_type varchar(30),
 score int
) 

# 값 추가
insert into dog_festival (dog_type, score) values ('말티즈', 23);
insert into dog_festival (dog_type, score) values ('말티즈', 1451);
insert into dog_festival (dog_type, score) values ('말티즈', 1);
insert into dog_festival (dog_type, score) values ('말티즈', 2);
insert into dog_festival (dog_type, score) values ('말티즈', 98);

insert into dog_festival (dog_type, score) values ('요크셔테리어', 23);
insert into dog_festival (dog_type, score) values ('요크셔테리어', 1451);
insert into dog_festival (dog_type, score) values ('요크셔테리어', 1);
insert into dog_festival (dog_type, score) values ('요크셔테리어', 2);
insert into dog_festival (dog_type, score) values ('요크셔테리어', 99999);

insert into dog_festival (dog_type, score) values ('비숑', 99);
insert into dog_festival (dog_type, score) values ('비숑', 151);
insert into dog_festival (dog_type, score) values ('비숑', 12);
insert into dog_festival (dog_type, score) values ('비숑', 2);
insert into dog_festival (dog_type, score) values ('비숑', 12132);

 

Mysql 5.7 이하

mysql 버전별로 top n 쿼리를 뽑는 방법이 다르다. 우선 5.7이하일 때 뽑는 방법이다.

select id, dog_type, score from (
	select *, @dog_type_rank := IF(@current_dog = dog_type, @dog_type_rank + 1,1 )as dog_type_rank,
	@current_dog := dog_type
	from dog_festival
	order by dog_type, score desc
) ranked
where dog_type_rank <=3

서브 쿼리 부터 살펴보자.

select *, @dog_type_rank := IF(@current_dog = dog_type, @dog_type_rank + 1,1 )as dog_type_rank,
	@current_dog := dog_type
	from dog_festival
	order by dog_type, score desc

1. order by dog_type, score desc 코드를 통해 강아지 타입별 스코어로 정렬을 한다. 

2. dog_type, socre로 정렬된 row에 대해 @current_dog:=dog_type을 사용해서 각각의 row 타입에 현재 dog_type을 저장하도록 한다.

3. @dog_type_rank := IF(@current_dog = dog_type, @dog_type_rank + 1,1 )as dog_type_rank 코드를 사용해서 순위 값을 만들어준다.

 

그리고 바깥쪽 쿼리를 살펴보자.

select id, dog_type, score from (
// subquery
) ranked
where dog_type_rank <=3

subquery에 나온 결과에서 나온 dog_type_rank의 순위중 3등까지만 나오도록 where 조건을 걸어준다. 이렇게 하면 각 타입별 스코어 랭킹 top n건을 뽑을 수 있다.

 

 

Mysql 8.0 이상

mysql 8.0이상부터는 function을 사용해서 값을 뽑을 수 있다.

select id, dog_type, score from (
	select *,rank() over (partition by df.dog_type order by df.dog_type, df.score desc) as dog_type_rank
	from dog_festival as df
) ranked
where ranked.dog_type_rank <=3

rank over partition by를 사용하면 좀 더 편하게 추출이 가능하다.

5.7 query와 동일하게 결과 노출