ROW_NUMBER 함수란?
ROW_NUMBER 함수는 SELECT 문 결과 집합에 대해 1~의 연번을 매기기 위한 함수이다.
이 포스팅에서는 SQL의 ROW_NUMBER 함수 사용법을 자세히 설명한다.
ROW_NUMBER 구문
먼저 ROW_NUMBER 함수의 구문을 살펴보자.
ROW_NUMBER() OVER ( [ PARTITION BY [파티션 컬럼], [파티션 컬럼], ...] ORDER BY [정렬 컬럼], [정렬 컬럼], ... )파티션 컬럼 (PARTITION BY)
지정한 경우 결과 집합을 파티션 컬럼으로 분할하고, 각 파티션마다 1 ~의 연번을 부여한다.
파티션 컬럼을 지정하지 않으면 결과 집합 전체에 1 ~의 연번을 부여한다.
정렬 컬럼 (ORDER BY)
파티션 내 행에 연번을 부여할 순서를 지정한다. 정렬 컬럼 지정은 필수이며 생략할 수 없다.
정렬 컬럼을 오름차순으로 정렬하려면 ORDER BY < COLUMN_NAME> ASC, 내림차순으로 정렬하려면 ORDER BY <COLUMN_NAME> DESC와 같이 지정한다.
ROW_NUMBER 사용법
ROW_NUMBER를 사용하여 SQL 결과 집합에 연번을 부여하는 샘플 코드를 살펴보자.
간단한 예제부터 조금 복잡한 예제까지 단계별로 살펴보겠습니다.
1. 연번을 흔들어주기
먼저 간단한 결과 집합 전체에 연번을 부여하는 샘플 코드를 살펴보자.
다음 SQL은 ROW_NUMBER 함수로 users 테이블의 'Age' 컬럼의 오름차순(ASC)으로 연번을 부여하고 있습니다.
SELECT
ROW_NUMBER() OVER(ORDER BY Age ASC) Num
, Name
, Age
FROM users
실행 결과
SQL을 실행하면 다음 표와 같이 'Age'의 오름차순으로 연번(Num)이 부여된다.
| Num | Name | Age |
| 1 | 홍길동 | 10 |
| 2 | 김순배 | 20 |
| 3 | 김영철 | 30 |
| 4 | 최동수 | 40 |
2. 정렬 컬럼을 여러 개 지정하기
연번을 매기는 순서(정렬 컬럼)를 여러 개 지정할 경우, 쉼표로 구분하여 컬럼을 지정한다.
다음 SQL은 users 테이블의 'Age' 열의 오름차순(ASC), 'Name' 열의 내림차순(DESC)으로 연번을 부여하고 있다.
SELECT
ROW_NUMBER() OVER(ORDER BY Age ASC, Name DESC) Num
, Name
, Age
FROM users
실행결과
| Num | Name | Age |
| 1 | 홍길동 | 10 |
| 2 | 김순배 | 20 |
| 3 | 김영철 | 30 |
| 4 | 최동수 | 40 |
| 5 | 최동철 | 40 |
3. 파티션별로 연번을 부여하기
파티션 컬럼을 지정하면 그룹별로 연번을 부여할 수 있다.
다음 SQL은 users 테이블의 'Age' 테이블의 'Age' 열의 오름차순(ASC)으로 연번을 부여하고 있다.
SELECT
Name
, Age
, ROW_NUMBER() OVER(PARTITION BY Pa ORDER BY Age ASC) Num
FROM users
실행결과
| Num | Name | City | Age |
| 1 | 홍길동 | 서울 | 10 |
| 2 | 최순배 | 서울 | 20 |
| 1 | 김영철 | 부산 | 30 |
| 2 | 최동수 | 부산 | 40 |
| 1 | 최동철 | 인천 | 50 |
Rank 함수와의 차이점
ROW_NUMBER 외에 RANK 함수로 SQL 결과 집합에 대해 연번을 매기는 방법이 있으며, 기능은 비슷합니다.
차이점으로는 ....
ROW_NUMBER는 같은 파티션 내에서 다른 행과 중복되지 않는 연번을 부여한다. 반면 RANK 함수는 같은 값(같은 순위)의 행에는 같은 연번(순위)을 부여한다.
실제로 SQL을 작성하여 그 차이를 확인해 보자.
아래 예제는 사용자별 Score(점수) 순위를 ROW_NUMBER와 RANK로 각각 구하는 예제이다.
SELECT
Name
, Score
, ROW_NUMBER() OVER(ORDER BY Score DESC) Rank
, ROW_NUMBER() OVER(ORDER BY Score DESC) Num
FROM scores
실행결과
| Name | Score | Rank | Num |
| 홍길동 | 100 | 1 | 1 |
| 최순배 | 90 | 2 | 2 |
| 김영철 | 90 | 2 | 3 |
| 최동수 | 80 | 4 | 4 |
| 최동철 | 80 | 4 | 5 |
ROW_NUMBER가 사용할 수 있는 데이터베이스
관계형 데이터베이스(RDB)에는 Oracle, SQL Server와 같은 유료 제품부터 MySQL, PostgreSQL과 같은 무료 제품까지 다양한 종류가 있으며, RDB마다 사용할 수 있는 구문과 함수에 차이가 있습니다.
아래 표는 ROW_NUMBER 함수를 사용할 수 있는 RDB 목록이며, ROW_NUBER는 'MySQL'을 제외한 주요 RDB 제품에서 사용할 수 있다.
- Oracle
- QL Server
- PostgreSQL
MySQL에서는 ROW_NUMBER를 사용할 수 없습니다.
MySQL에서는 ROW_NUMBER 함수를 사용할 수 없다.
ROW_NUMBER 함수와 같은 것을 MySQL에서 구현하려면 '사용자 변수'를 사용한다.
MySQL의 '사용자 변수'란?
'사용자 변수'란 MySQL의 세션 상에서 정의하는 변수로, 명령문 내에서 '사용자 정의 변수'에 값을 저장하고 나중에 다른 명령문에서 '사용자 변수'의 값을 참조할 수 있다.
여러 SQL 간에 값을 주고받거나, 후술할 ROW_NUMBER 함수처럼 연번을 매길 때 사용한다.
MySQL의 세션 상에서 정의하는 변수이기 때문에 다른 세션, 사용자는 참조할 수 없다.
'사용자 정의 변수'는 세션을 닫으면 자동으로 해제된다.
예제 코드
다음 SQL은 users 테이블의 나이(age)를 오름차순으로 연번을 매기는 예제입니다.
포인트는 첫 번째 행에서 사용자 정의 변수 @rownum을 선언하고 SELECT 문에서 @rownum을 한 줄씩 더하면서 결과를 가져오는 것입니다.
또한, 나이(age)의 오름차순으로 연번을 매기기 위해 ORDER BY Age로 레코드를 정렬하고 있습니다.
SET @rownum=0;
SELECT
@rownum:=@rownum+1 as ROW_NUMBER
, Name
, Age
FROM users
ORDER BY Age
실행결과
| Num | Name | Age |
| 1 | 홍길동 | 10 |
| 2 | 최순배 | 20 |
| 3 | 김영철 | 30 |
| 4 | 최동수 | 40 |
| 5 | 최동철 | 40 |
마치며
SQL의 ROW_NUMBER 함수로 SELECT 문의 결과 집합에 대해 1~의 연번을 매기는 방법에 대해서 알아보았습니다.
ROW_NUMBER 함수는 예를 들어 웹 사이트에서 20개씩 데이터를 표시하는 경우나 SELECT 문에서 가져온 결과에 순위(연번)를 매기고 싶을 때 등 다양한 장면에서 활용할 수 있습니다. 이번 기회에 꼭 기억해 두시기 바랍니다.