[MSSQL] 윈도우 함수 ROW_NUMBER() 순차번호 할당

SQL를 다루다 보면 행별로 순차적인 번호를 부여해야 할 상황이 1번쯤은 온다.

RANK()를 활용할 수는 있겠지만, RANK()함수는 번호의 기준이 되는 ORDER BY 절의 데이터가 동일하다면 같은 번호로 할당된다.

그런 점을 보안한 DENSE_RANK() 함수가 있기는 하나 ROW_NUMBER() 함수의 명이 더 의미적으로 적합한 함수인 것 같다.

 

ROW_NUMBER() 함수는 행과 행간의 관계를 정의하는 윈도우 함수이며, ANSI/ISO SQL 표준과 SQLServer, Oracle에서 지원한다.

 

ROW_NUMBER() 란?

ROW_NUMBER() 함수는 결과 집합의 파티션 내 각 행에 순차적인 정수를 할당하는 윈도우 함수입니다.

 

ROW_NUMBER()의 특징

• 행의 번호는 각 파티션에 대해 1번부터 할당합니다.
• 정렬의 중복 값이 있어도 서로 다른 정수를 할당합니다.

 

SYNTAX

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

구문에서 자세히 보아야 하는 점은 2가지 PARTITION BY, ORDER BY이다.

PARTITION BY

PARTITION BY 절은 결과 집합을 파티션으로 나눈다.  ROW_NUMBER() 윈도우 함수는 각 파티션에 별도로 정수를 할당하며, 각 파티션 별로 1번부터 할당한다.

PARTITION BY 절은 옵션 값이며, PARTITION BY를 무시하고 사용한다면 결과 집합의 전체를 한 파티션으로 취급한다.

 

ORDER BY

ORDER BY 절은 결과 집합의 각 파티션 내 행의 논리적 순서를 정의한다.  ROW_NUMBER()함수는 정수를 순차적으로 할당하는 윈도우 함수이기 때문에 ORDER BY절은 필수적으로 필요하다.

 

 

ROW_NUMBER() 예제

아래 ROW_NUMBER() 예제로는 아래의 테이블 스키마를 사용한다.

 

파티션 없는 ROW_NUMBER()

다음 쿼리는 ROW_NUMBER() 함수를 이용해 각 고객 이름 순서대로 일련번호 정수를 할당한다.

SELECT ROW_NUMBER() OVER (
	               ORDER BY first_name
       ) row_num,
      first_name, 
      last_name, 
      city
FROM sales.customers;

 

결과는 아래와 같다.

 

결과의 ROW 순서는 1번부터 할당되었으며 first_name의 ASC 순서대로 할당된 것을 볼 수 있다.

 

파티션 ROW_NUMBER()

다음 쿼리에서는 city별로 고객 이름 순으로 일련번호(정수)를 할당한다.

SELECT first_name, 
       last_name, 
       city,
       ROW_NUMBER() OVER (
                  PARTITION BY city
                  ORDER BY first_name
       ) row_num
 FROM sales.customers
ORDER BY city;

 

결과는 아래와 같다.

PARTITION BY를 사용하여 도시별로 파티션을 나누었고 도시별로 고객의 이름에 일련번호가 부여된 것을 볼 수 있다.

다른 도시 파티션에서는 다시 1번으로 초기화되어 할당된 것을 볼 수 있다.

 

ROW_NUMBER() 응용

 

ROW_NUMBER() 윈도우 함수는 페이징 처리를 할 때 유용하게 사용할 수 있다.

예를 들어, 각 페이지에 10개의 행을 보여주는 테이블이 있어서 페이지 별로 페이징 처리가 필요할 때이다.

 

아래 쿼리에서는 페이지당 10개의 행을 보여준다는 가정하에 2페이지 즉, 11~20번의 일련번호를 가진 고객을 SELECT 하는 예제이다.

WITH cte_customers AS (
    SELECT ROW_NUMBER() OVER(
             ORDER BY 
                first_name, 
                last_name
           ) row_num, 
           customer_id, 
           first_name, 
           last_name
     FROM sales.customers
) SELECT customer_id, 
         first_name, 
         last_name
    FROM cte_customers
   WHERE row_num > 20 
     AND row_num <= 30;

결과는 아래와 같다.