SQL 윈도우 함수 한눈에 알아보기 Rank(), Dense Rank(), Row_Number()

윈도우 함수란?

윈도우 함수는 SQL에서 제공하는 강력한 도구로서, 데이터셋 내의 '윈도우' 라고 부르는 행 집합에 대한 다양한 계산을 수행할 수 있게 해줍니다. 윈도우 함수는 대개 집계 함수와 유사하게 동작하지만, 전체 쿼리에 대한 결과를 출력하는 대신에 각 행이 속하는 윈도우에 대한 결과를 출력합니다.

윈도우 함수는 OVER 절을 사용하여 윈도우를 정의합니다. 이 OVER 절 내에서는 PARTITION BY, ORDER BY, 그리고 ROWS 또는 RANGE 키워드를 사용하여 윈도우를 어떻게 분할하고 정렬하고 범위를 정의할지를 명시할 수 있습니다.

윈도우 함수의 세 가지 종류

RANK(), DENSE_RANK(), ROW_NUMBER() 모두 SQL의 윈도우 함수입니다. 이 세 함수는 윈도우 내에서 각 행에 순서를 부여하는데 사용되지만, 동일한 값을 가진 행들을 처리하는 방식에 차이가 있습니다.

아래 예제를 통해 이들의 차이를 살펴봅시다. 다음과 같은 Students 테이블을 가정해봅시다.

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(30),
    score INT
);

INSERT INTO Students (id, name, score)
VALUES 
    (1, 'Alice', 85),
    (2, 'Bob', 90),
    (3, 'Charlie', 90),
    (4, 'Dave', 70),
    (5, 'Eva', 80);

 

1. RANK()

RANK() 함수는 동일한 값을 가진 행에 대해 동일한 순위를 부여하고, 이후 순위는 건너뜁니다.

SELECT
    name,
    score,
    RANK() OVER (ORDER BY score DESC) rank
FROM
    Students;

name    | score | rank
----------------------
Bob     | 90    | 1
Charlie | 90    | 1
Alice   | 85    | 3
Eva     | 80    | 4
Dave    | 70    | 5

Bob과 Charlie는 같은 점수를 가지고 있어서 순위 1을 공유하고, 그 다음 순위는 3이 됩니다.

 

2. DENSE_RANK()

DENSE_RANK() 함수는 RANK()와 비슷하지만 순위를 건너뛰지 않습니다.

SELECT
    name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) dense_rank
FROM
    Students;

name    | score | dense_rank
----------------------------
Bob     | 90    | 1
Charlie | 90    | 1
Alice   | 85    | 2
Eva     | 80    | 3
Dave    | 70    | 4

 

3. ROW_NUMBER()

ROW_NUMBER() 함수는 모든 행마다 순위를 부여합니다. 동일한 값을 가진 행이라도 서로 다른 숫자가 부여됩니다.

SELECT
    name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) row_number
FROM
    Students;

name    | score | row_number
----------------------------
Bob     | 90    | 1
Charlie | 90    | 2
Alice   | 85    | 3
Eva     | 80    | 4
Dave    | 70    | 5

Bob과 Charlie는 같은 점수를 가지고 있지만, 각각 다른 순위인 1, 2를 받습니다. ROW_NUMBER() 함수는 SQL 쿼리가 실행될 때마다 고유한 순위를 각 행에 부여합니다. 하지만 ROW_NUMBER() 함수를 사용할 때, 동일한 값을 가진 여러 행에 대해서는 어떤 기준으로 순위를 부여할지 명시적으로 지정하지 않는 한, 그 순서는 보장되지 않습니다.

윈도우 명세 또는 윈도우 정의

윈도우 명세의 구성 요소들을 조합하여 윈도우 함수가 어떻게 데이터에 적용될지를 세부적으로 명시할 수 있습니다.

 

• PARTITION BY: 윈도우를 서로 겹치지 않는 부분집합, 즉 파티션으로 나누는 방식을 정의합니다.
• ORDER BY: 각각의 파티션 내에서 행의 순서를 정의합니다.
• ROWS 또는 RANGE: 윈도우의 "프레임" (Frame)을 정의합니다. 프레임은 현재 행에 대한 윈도우 내에서 계산이 수행될 행의 범위를 명시합니다.

예제

PARTITION BY, ORDER BY, 그리고 ROWS 또는 RANGE를 예제를 사용하여 보여드리겠습니다.

 

다음과 같은 Orders 테이블을 가정해봅시다

CREATE TABLE Orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    sales INT
);

INSERT INTO Orders (id, customer_id, order_date, sales)
VALUES 
    (1, 1, '2023-05-01', 100),
    (2, 1, '2023-05-03', 200),
    (3, 1, '2023-05-07', 150),
    (4, 2, '2023-05-01', 300),
    (5, 2, '2023-05-05', 350),
    (6, 2, '2023-05-10', 400);

이 테이블에는 각 고객의 주문 정보가 들어 있습니다.

 

1. PARTITION BY

PARTITION BY를 사용하면, 각 고객별로 총 매출을 계산할 수 있습니다.

SELECT
    customer_id,
    order_date,
    sales,
    SUM(sales) OVER (PARTITION BY customer_id) as total_sales_per_customer
FROM
    Orders;

 customer_id | order_date | sales | total_sales_per_customer 
-------------|------------|-------|-------------------------
           1 | 2023-05-01 |   100 |                     450
           1 | 2023-05-03 |   200 |                     450
           1 | 2023-05-07 |   150 |                     450
           2 | 2023-05-01 |   300 |                    1050
           2 | 2023-05-05 |   350 |                    1050
           2 | 2023-05-10 |   400 |                    1050

 

2. ORDER BY

ORDER BY를 사용하면, 각 고객의 주문을 날짜별로 정렬하면서 누적 매출을 계산할 수 있습니다.

SELECT
    customer_id,
    order_date,
    sales,
    SUM(sales) OVER (PARTITION BY customer_id ORDER BY order_date) as cumulative_sales_per_customer
FROM
    Orders;

 customer_id | order_date | sales | cumulative_sales_per_customer
-------------|------------|-------|-------------------------------
           1 | 2023-05-01 |   100 |                            100
           1 | 2023-05-03 |   200 |                            300
           1 | 2023-05-07 |   150 |                            450
           2 | 2023-05-01 |   300 |                            300
           2 | 2023-05-05 |   350 |                            650
           2 | 2023-05-10 |   400 |                           1050

 

3. ROWS

ROWS를 사용하면, 현재 행을 포함하여 고객의 가장 최근 2건의 주문에서의 매출 합계를 계산할 수 있습니다.

SELECT
    customer_id,
    order_date,
    sales,
    SUM(sales) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) as sales_last_two_orders
FROM
    Orders;

 customer_id | order_date | sales | sales_last_two_orders
-------------|------------|-------|----------------------
           1 | 2023-05-01 |   100 |                  100
           1 | 2023-05-03 |   200 |                  300
           1 | 2023-05-07 |   150 |                  350
           2 | 2023-05-01 |   300 |                  300
           2 | 2023-05-05 |   350 |                  650
           2 | 2023-05-10 |   400 |                  750

'ROWS BETWEEN 1 PRECEDING AND CURRENT ROW'
이 표현은 현재 행과 그 전 행을 윈도우 프레임에 포함시키는 것을 의미합니다. 여기서 '1 PRECEDING'은 바로 앞의 행을 의미하고, 'CURRENT ROW'는 현재 처리 중인 행을 의미합니다. 따라서, 이 표현을 사용하면 현재 행과 바로 앞의 행만을 대상으로 연산을 수행합니다.

 

4. RANGE

RANGE를 사용하면, 고객의 각 주문에 대해 동일하거나 이전의 날짜에서의 누적 매출을 계산할 수 있습니다.

SELECT
    customer_id,
    order_date,
    sales,
    SUM(sales) OVER (PARTITION BY customer_id ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales_to_date
FROM
    Orders;

 customer_id | order_date | sales | cumulative_sales_to_date
-------------|------------|-------|-------------------------
           1 | 2023-05-01 |   100 |                       100
           1 | 2023-05-03 |   200 |                       300
           1 | 2023-05-07 |   150 |                       450
           2 | 2023-05-01 |   300 |                       300
           2 | 2023-05-05 |   350 |                       650
           2 | 2023-05-10 |   400 |                      1050

'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'
이 표현은 현재 행부터 파티션의 처음까지 모든 행을 윈도우 프레임에 포함시키는 것을 의미합니다. 'UNBOUNDED PRECEDING'는 파티션의 첫 행을 의미하고, 'CURRENT ROW'는 현재 처리 중인 행을 의미합니다. 따라서, 이 표현을 사용하면 현재 행부터 파티션의 처음까지의 모든 행을 대상으로 연산을 수행합니다.

 

참고로 ROWS와 RANGE는 비슷해 보이지만 약간의 차이가 있습니다. ROWS는 물리적인 행의 위치를 기준으로 프레임을 정의하고, RANGE는 행의 값 자체를 기준으로 프레임을 정의합니다. 따라서 ORDER BY가 지정된 경우, RANGE는 ORDER BY의 결과에 따라 행의 값이 같은 경우 동일한 프레임으로 취급할 수 있습니다. 즉, ROWS는 순서에 따라 행을 포함하고, RANGE는 값에 따라 행을 포함합니다. 이것이 ROWS와 RANGE의 가장 큰 차이점입니다.