<div id="readability-page-1" class="page"><div>            <p data-ke-size="size16">mysql을 사용하고 있는 상황에서 타입별 보유하고 있는 score별 순위 2개까지의 값을 가져오는 쿼리가 필요했다.&nbsp;</p>
<p data-ke-size="size16">예를 들면 강아지 페스티벌에 참여한 강아지들의 스코어 별 상위 3위까지의 강아지를 찾는 쿼리가 필요했다.</p>

<p data-ke-size="size16">그럼 값을 한번 추출해보자. 우선 전제가 되는 테이블은 아래와 같다.</p>
<pre id="code_1667219526263" data-ke-language="sql" data-ke-type="codeblock"><code># 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);</code></pre>

<h3 data-ke-size="size23">Mysql 5.7 이하</h3>
<p data-ke-size="size16">mysql 버전별로 top n 쿼리를 뽑는 방법이 다르다. 우선 5.7이하일 때 뽑는 방법이다.</p>
<pre id="code_1667219673143" data-ke-language="sql" data-ke-type="codeblock"><code>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 &lt;=3</code></pre>
<figure data-origin-width="708" data-origin-height="566" data-ke-mobilestyle="alignCenter"><span data-url="https://blog.kakaocdn.net/dn/cIaVfR/btrP6gYRg5R/hvoicvSiZPWGpwck699pE1/img.png" data-lightbox="lightbox" data-alt=""><img src="https://blog.kakaocdn.net/dn/cIaVfR/btrP6gYRg5R/hvoicvSiZPWGpwck699pE1/img.png" data-ke-mobilestyle="alignCenter"></span></figure>
<p data-ke-size="size16">서브 쿼리 부터 살펴보자.</p>
<pre id="code_1667219977967" data-ke-language="sql" data-ke-type="codeblock"><code>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</code></pre>
<p data-ke-size="size16">1. order by dog_type, score desc 코드를 통해 강아지 타입별 스코어로 정렬을 한다.&nbsp;</p>
<p data-ke-size="size16"><span>2. dog_type, socre로 정렬된 row에 대해 @current_dog:=dog_type을 사용해서 각각의 row 타입에 현재 dog_type을 저장하도록 한다.</span></p>
<p data-ke-size="size16"><span>3. @dog_type_rank := IF(@current_dog = dog_type, @dog_type_rank + 1,1 )as dog_type_rank 코드를 사용해서 순위 값을 만들어준다.</span></p>

<p data-ke-size="size16">그리고 바깥쪽 쿼리를 살펴보자.</p>
<pre id="code_1667220076579" data-ke-language="sql" data-ke-type="codeblock"><code>select id, dog_type, score from (
// subquery
) ranked
where dog_type_rank &lt;=3</code></pre>
<p data-ke-size="size16">subquery에 나온 결과에서 나온 dog_type_rank의 순위중 3등까지만 나오도록 where 조건을 걸어준다. 이렇게 하면 각 타입별 스코어 랭킹 top n건을 뽑을 수 있다.</p>


<h3 data-ke-size="size23">Mysql 8.0 이상</h3>
<p data-ke-size="size16">mysql 8.0이상부터는 function을 사용해서 값을 뽑을 수 있다.</p>
<pre id="code_1667220946403" data-ke-language="sql" data-ke-type="codeblock"><code>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 &lt;=3</code></pre>
<p data-ke-size="size16">rank over partition by를 사용하면 좀 더 편하게 추출이 가능하다.</p>
<figure data-origin-width="706" data-origin-height="588" data-ke-mobilestyle="alignCenter"><span data-url="https://blog.kakaocdn.net/dn/IbMT8/btrP6g5DtrW/qWBRK2r1f5v9GfTiVrZ53k/img.png" data-lightbox="lightbox" data-alt="5.7 query와 동일하게 결과 노출"><img src="https://blog.kakaocdn.net/dn/IbMT8/btrP6g5DtrW/qWBRK2r1f5v9GfTiVrZ53k/img.png" data-ke-mobilestyle="alignCenter"></span><figcaption>5.7 query와 동일하게 결과 노출</figcaption></figure>            
</div></div>

wedul

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를 사용하면 좀 더 편하게 추출이 가능하다.

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

댓글