MySQL SELECT 문에서 행 인덱스(순위) 생성하기

MySQL에서 SELECT 문의 결과에 행 인덱스(순위)를 부여하고 싶다면 윈도우 함수인 ROW_NUMBER()를 사용하는 것이 가장 간단하고 효율적인 방법입니다. 이 글에서는 실제 예제를 통해 중복된 값이 있는 데이터에 그룹별 순위를 매기는 방법을 단계별로 살펴보겠습니다.

1. 테이블 생성하기

먼저 예제에 사용할 테이블을 생성합니다.

mysql> create table DemoTable
(
    Name varchar(40)
);
Query OK, 0 rows affected (0.49 sec)

2. 샘플 데이터 삽입하기

INSERT 명령을 사용해 의도적으로 중복된 이름을 포함한 레코드를 삽입합니다.

mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('Chris');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.08 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.08 sec)

3. 전체 데이터 확인하기

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

mysql> select *from DemoTable;

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

+--------+
| Name   |
+--------+
| Chris  |
| Chris  |
| Chris  |
| Robert |
| Robert |
| Adam   |
| Adam   |
| Adam   |
| Adam   |
+--------+
9 rows in set (0.00 sec)

4. ROW_NUMBER()로 행 인덱스(순위) 생성하기

이제 핵심인 쿼리입니다. ROW_NUMBER() OVER (PARTITION BY ...) 구문을 사용하면 동일한 이름끼리 그룹을 나누고(PARTITION), 각 그룹 내에서 1부터 시작하는 순번을 자동으로 부여할 수 있습니다.

mysql> select Name,
row_number() over (partition by Name) as `Rank`
from DemoTable;

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

+--------+------+
| Name   | Rank |
+--------+------+
| Adam   | 1    |
| Adam   | 2    |
| Adam   | 3    |
| Adam   | 4    |
| Chris  | 1    |
| Chris  | 2    |
| Chris  | 3    |
| Robert | 1    |
| Robert | 2    |
+--------+------+
9 rows in set (0.00 sec)

결과를 보면 'Adam'은 1~4까지, 'Chris'는 1~3까지, 'Robert'는 1~2까지 각 이름 그룹 안에서 독립적으로 순위가 매겨진 것을 확인할 수 있습니다.

핵심 개념 정리

ROW_NUMBER(): 결과 집합의 각 행에 1부터 시작하는 연속된 번호를 부여하는 윈도우 함수입니다.
PARTITION BY: 지정한 컬럼 값을 기준으로 행들을 그룹으로 나누어, 각 파티션(그룹)마다 번호가 새로 시작되도록 합니다.
OVER 절: 윈도우 함수가 작동할 범위를 정의합니다.

응용: ORDER BY와 함께 사용하기

PARTITION BY 뒤에 ORDER BY를 추가하면 그룹 내에서 특정 기준에 따라 순위를 정렬할 수 있습니다. 예를 들어 나이나 점수 컬럼이 있다면 다음과 같이 활용할 수 있습니다.

select Name,
row_number() over (partition by Name order by Score desc) as `Rank`
from DemoTable;