RANGE BETWEEN으로 날짜 기반 이동 집계 구현하기

RANGE BETWEEN INTERVAL의 핵심 개념

SQL 윈도우 함수에서 RANGE BETWEEN은 값의 범위를 기준으로 윈도우 프레임을 정의합니다. 특히 시계열 데이터 분석에서 날짜나 시간 기반으로 집계할 때 매우 유용한 기능입니다.

RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW

이 구문은 현재 행의 날짜를 기준으로 6일 전부터 현재까지의 모든 행을 윈도우 프레임에 포함시킵니다.

SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS rolling_7day_total

작동 원리

1. ORDER BY order_date: 날짜 순으로 정렬
2. INTERVAL '6 days' PRECEDING: 현재 행의 날짜에서 6일을 뺀 날짜
3. CURRENT ROW: 현재 행의 날짜
4. 결과: 6일 전 ≤ order_date ≤ 현재 날짜인 모든 행이 포함

실전 예시

• *- 샘플 데이터* order_date | amount *-----------+--------* 2024-01-01 | 100 2024-01-03 | 200 2024-01-05 | 150 2024-01-08 | 300 2024-01-10 | 250

INTERVAL '6 days' PRECEDING 적용 시:

• - 2024-01-01 행: 2023-12-26 ~ 2024-01-01 범위- → 100 (자기 자신만)- 2024-01-03 행: 2023-12-28 ~ 2024-01-03 범위- → 100 + 200 = 300- 2024-01-05 행: 2023-12-30 ~ 2024-01-05 범위- → 100 + 200 + 150 = 450- 2024-01-08 행: 2024-01-02 ~ 2024-01-08 범위- → 200 + 150 + 300 = 650 (1월 1일은 제외)- 2024-01-10 행: 2024-01-04 ~ 2024-01-10 범위- → 150 + 300 + 250 = 700 (1월 1일, 3일 제외)

ROWS vs RANGE: 핵심 차이점

ROWS BETWEEN (행 개수 기준)

• - 이전 2개 행 + 현재 행 = 총 3개 행

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

• 물리적인 행 개수로 계산
• 값과 무관하게 단순히 N개 행을 포함

RANGE BETWEEN (값 범위 기준)

• - 날짜 값이 6일 이내인 모든 행

RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW

• 논리적인 값 범위로 계산
• 동일한 날짜에 여러 주문이 있어도 모두 포함
• 날짜 간격이 크면 포함되는 행 개수가 가변적

실무 활용 사례

1. 7일 이동 평균 계산

SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS avg_7day
FROM orders;

2. 최근 30일 누적 매출

SELECT 
    order_date,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS revenue_30day
FROM orders;

3. 주간 트렌드 분석

SELECT 
    order_date,
    COUNT(*) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS orders_7day,
    SUM(amount) OVER (
        ORDER BY order_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS revenue_7day
FROM orders;

주의사항

1. 날짜 타입 필수

• ORDER BY 컬럼이 DATE 또는 TIMESTAMP 타입이어야 함
• 숫자형에는 INTERVAL 사용 불가

2. 동일 날짜 처리

• 같은 날짜의 모든 행이 함께 포함됨
• ROWS BETWEEN과 달리 순서가 중요하지 않음

3. 성능 고려사항

• 대량 데이터에서는 인덱스 필요

CREATE INDEX idx_order_date ON orders(order_date);

마무리

RANGE BETWEEN INTERVAL은 시계열 데이터의 이동 집계를 구현할 때 가장 직관적이고 정확한 방법입니다. 특히 날짜 간격이 일정하지 않은 실무 데이터에서 ROWS BETWEEN보다 훨씬 유용합니다.