seon99il

Database

SQL PARTITION BY 파헤치기: 윈도우 프레임의 숨은 동작 원리

3분 소요

SQL로 집계와 분석을 할 때 윈도우 함수는 종종 GROUP BY와 함께 등장합니다. 특히 PARTITION BY는 특정 컬럼을 기준으로 데이터를 나누고 파티션별로 계산을 수행할 수 있게 해 주며, 일반적인 GROUP BY와는 근본적으로 다르게 동작합니다.

스키마: sales 테이블

sales 테이블에는 매출 정보가 들어 있습니다.

-- sales Table

+--+----+----------+------+
|id|type|month     |amount|
+--+----+----------+------+
|1 |A   |2025-01-01|100   |
|2 |A   |2025-02-01|150   |
|3 |A   |2025-03-01|120   |
|4 |A   |2025-04-01|180   |
|5 |B   |2025-01-01|90    |
|6 |B   |2025-02-01|130   |
|7 |B   |2025-03-01|200   |
|8 |B   |2025-04-01|170   |
+--+----+----------+------+
  • id: 행의 고유 식별자
  • type: 상품 유형
  • month: 매출 월
  • amount: 매출액

PARTITION BY

PARTITION BY는 윈도우 함수에서 컬럼을 기준으로 데이터를 나누어 파티션별로 계산을 수행할 때 사용합니다.

동작 방식

SELECT
    s.month,
    SUM(s.amount) OVER (PARTITION BY month) AS total_amount
FROM sales AS s;

-- Result
+----------+------------+
|month     |total_amount|
+----------+------------+
|2025-01-01|190         |
|2025-01-01|190         |
|2025-02-01|280         |
|2025-02-01|280         |
|2025-03-01|320         |
|2025-03-01|320         |
|2025-04-01|350         |
|2025-04-01|350         |
+----------+------------+
  • PARTITION BY month는 month 기준으로 데이터를 나누고, 각 파티션 안에서 합계를 반복해서 보여 줍니다.
  • 중요: 모든 행이 그대로 유지되며 그룹화는 일어나지 않습니다.

반면 GROUP BY는 그룹당 하나의 행으로 합쳐 버리므로 개별 행의 세부 정보가 사라집니다.

-- Group By Result
| month      | total_amount |
| ---------- | ------------ |
| 2025-01-01 | 190          |
| 2025-02-01 | 280          |
| 2025-03-01 | 320          |
| 2025-04-01 | 350          |

문법과 윈도우 프레임

명시적으로 드러나지는 않지만, PARTITION BY는 ROWS/RANGE 윈도우 프레임과 함께 사용할 수 있습니다.

<window_function> OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    { ROWS | RANGE | GROUPS } BETWEEN <frame_start> AND <frame_end>
)
  • PARTITION BY: 그룹화 단위
  • ORDER BY: 프레임 내 정렬 기준
  • ROWS / RANGE / GROUPS: 프레임 유형
  • frame_start / frame_end: 프레임 경계

프레임 옵션

  • UNBOUNDED PRECEDING: 파티션의 첫 번째 행부터
  • n PRECEDING: 현재 행으로부터 n개 앞의 행
  • CURRENT ROW: 현재 행
  • n FOLLOWING: 현재 행으로부터 n개 뒤의 행
  • UNBOUNDED FOLLOWING: 파티션의 마지막 행까지

PARTITION BY: 윈도우 프레임

윈도우 함수에서는 프레임을 정의하여 계산이 수행될 행의 범위를 지정할 수 있습니다. 프레임은 ROWS, RANGE, GROUPS 중 하나이며, 계산은 파티션과 정렬 기준에 따라 수행됩니다.

SELECT s.month,
       SUM(s.amount) OVER (PARTITION BY month
                            RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )  AS total_amount
FROM sales AS s

이 쿼리는 각 month 프레임의 첫 번째 행부터 현재 행까지의 합계를 total_amount로 계산합니다.

다만 미묘한 부분이 있습니다. sales 테이블을 month로 나누고 첫 행부터 현재 행까지 더한다면 다음과 같은 결과를 기대할 수 있습니다.

-- sales table
+--+----+----------+------+
|id|type|month     |amount|
+--+----+----------+------+
|1 |A   |2025-01-01|100   |
...
|5 |B   |2025-01-01|90    |
|8 |B   |2025-04-01|170   |
+--+----+----------+------+

-- Query
SELECT s.month,
       SUM(s.amount) OVER (PARTITION BY month
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS total_amount
FROM sales AS s

-- Result
+----------+------------+
|month     |total_amount|
+----------+------------+
|2025-01-01|100         |
|2025-01-01|190         |
+----------+------------+

PARTITION BY: ROWS와 RANGE

  • ROWS는 행 단위로 계산하므로, 같은 월이어도 100과 190이 따로 계산됩니다.
  • RANGE는 윈도우 프레임에서 같은 값을 가진 행들을 하나의 단위로 취급합니다. 예를 들어 100과 90이 하나로 간주되어, 두 행 모두 total_amount = 190이 됩니다.

예제: LAG 구현하기

LAG 함수는 이전 행의 컬럼 값을 가져옵니다.

LAG(<column>, <offset>, <default_value>)
OVER (PARTITION BY <partition_column> ORDER BY <order_column>)

윈도우 프레임을 사용하면 LAG 없이도 같은 결과를 얻을 수 있습니다.

SELECT
    id,
    type,
    month,
    amount,
    SUM(amount) OVER (
        PARTITION BY type
        ORDER BY month
        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) AS prev_amount
FROM sales;

-- Result
+--+----+----------+------+-----------+
|id|type|month     |amount|prev_amount|
+--+----+----------+------+-----------+
|1 |A   |2025-01-01|100   |null       |
|2 |A   |2025-02-01|150   |100        |
|3 |A   |2025-03-01|120   |150        |
|4 |A   |2025-04-01|180   |120        |
|5 |B   |2025-01-01|90    |null       |
|6 |B   |2025-02-01|130   |90         |
|7 |B   |2025-03-01|200   |130        |
|8 |B   |2025-04-01|170   |200        |
+--+----+----------+------+-----------+
  • 윈도우 프레임이 바로 앞의 1개 행만 참조하도록 설정되어 있습니다 (ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING).
  • 첫 번째 행은 이전 행이 없으므로 NULL이 반환됩니다.
  • 계산은 파티션(type)별로 독립적으로 수행됩니다.

이 방식을 활용하면 윈도우 프레임으로 다양한 행 단위 계산을 하도록 쿼리를 자유롭게 구성할 수 있습니다.

이전 글

전체 글 보기 →

댓글