MySQL에서 각 그룹별 상위 2개 행을 선택하는 방법

MySQL에서 각 그룹별로 상위 2개의 행을 선택해야 하는 경우가 종종 있습니다. 이럴 때는 WHERE 조건과 서브쿼리를 함께 활용하면 손쉽게 해결할 수 있습니다. 이 글에서는 실제 예제를 통해 단계별로 방법을 살펴보겠습니다.

1. 테이블 생성하기

먼저 예제에 사용할 테이블을 생성합니다. 아래 쿼리는 이름(Name)과 총 점수(TotalScores) 두 개의 컬럼을 가진 테이블을 만듭니다.

mysql> create table selectTop2FromEachGroup
    -> (
    -> Name varchar(20),
    -> TotalScores int
    -> );
Query OK, 0 rows affected (0.80 sec)

2. 샘플 데이터 삽입하기

INSERT 명령어를 사용해 테이블에 레코드를 추가합니다. John과 Carol이라는 두 명의 학생이 각각 3개의 점수를 가지도록 데이터를 구성했습니다.

mysql> insert into selectTop2FromEachGroup values('John',32);
Query OK, 1 row affected (0.38 sec)
mysql> insert into selectTop2FromEachGroup values('John',33);
Query OK, 1 row affected (0.21 sec)
mysql> insert into selectTop2FromEachGroup values('John',34);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',35);
Query OK, 1 row affected (0.17 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',36);
Query OK, 1 row affected (0.14 sec)
mysql> insert into selectTop2FromEachGroup values('Carol',37);
Query OK, 1 row affected (0.15 sec)

3. 전체 데이터 확인하기

SELECT 문으로 테이블의 모든 레코드를 조회해 보겠습니다.

mysql> select *from selectTop2FromEachGroup;

실행 결과는 다음과 같습니다.

+-------+-------------+
| Name  | TotalScores |
+-------+-------------+
| John  |          32 |
| John  |          33 |
| John  |          34 |
| Carol |          35 |
| Carol |          36 |
| Carol |          37 |
+-------+-------------+
6 rows in set (0.00 sec)

4. 각 그룹별 상위 2개 행 선택하기

이제 핵심인 쿼리입니다. WHERE 조건과 상관 서브쿼리(Correlated Subquery)를 사용하여 각 이름 그룹별로 가장 높은 점수 2개만 추출할 수 있습니다.

mysql> select *from selectTop2FromEachGroup tbl
    -> where
    -> (
    ->    SELECT COUNT(*)
    ->    FROM selectTop2FromEachGroup tbl1
    ->    WHERE tbl1.Name = tbl.Name AND
    ->    tbl1.TotalScores >= tbl.TotalScores
    -> ) <= 2 ;

실행 결과

+-------+-------------+
| Name  | TotalScores |
+-------+-------------+
| John  |          33 |
| John  |          34 |
| Carol |          36 |
| Carol |          37 |
+-------+-------------+
4 rows in set (0.06 sec)

쿼리 동작 원리

이 쿼리가 어떻게 작동하는지 살펴보겠습니다.

외부 쿼리의 각 행(예: John, 32)에 대해 서브쿼리가 실행됩니다.
서브쿼리는 같은 이름을 가진 행 중에서 현재 행의 점수보다 크거나 같은 점수를 가진 행의 개수를 계산합니다.
예를 들어 John의 34점 행의 경우, 자기 자신(34)보다 크거나 같은 점수는 34 하나뿐이므로 COUNT 값이 1이 되고, 조건(<= 2)을 만족하여 결과에 포함됩니다.
반면 John의 32점 행은 32, 33, 34 세 개의 행이 자신보다 크거나 같으므로 COUNT 값이 3이 되어 결과에서 제외됩니다.

결과적으로 각 그룹에서 가장 높은 점수 2개씩만 남게 됩니다. 만약 상위 N개를 선택하고 싶다면 조건의 숫자 2를 원하는 값으로 변경하면 됩니다.

참고: MySQL 8.0 이상이라면 윈도우 함수 활용 가능

MySQL 8.0부터는 윈도우 함수를 지원하므로 ROW_NUMBER()를 사용하는 방법도 있습니다. 대용량 데이터에서는 윈도우 함수가 더 나은 성능을 보이는 경우가 많습니다.

SELECT Name, TotalScores
FROM (
   SELECT Name, TotalScores,
          ROW_NUMBER() OVER (PARTITION BY Name ORDER BY TotalScores DESC) AS rn
   FROM selectTop2FromEachGroup
) ranked
WHERE rn <= 2;

두 방법 모두 동일한 결과를 반환하지만, 데이터 양과 MySQL 버전에 따라 적절한 방식을 선택하시면 됩니다.