ROWS랑 RANGE로 윈도우 프레임 세팅하기

윈도우 함수 쓸 때 이런 질문이 생겨: "현재 행 기준으로 윈도우 안에 몇 개의 행이 계산에 들어가?" 이건 윈도우 프레임에 따라 달라져.

윈도우 프레임은 윈도우 함수 결과를 계산할 때 쓰는 행의 범위야. 이 범위는 현재 행을 기준으로 하고, ROWS나 RANGE로 추가 조건을 줄 수 있어.

간단한 예시: 누적 합을 구할 때 이렇게 할 수 있어:

• 현재 행만 포함하기.
• 현재 행이랑 그 위에 있는 모든 행 포함하기.
• 현재 행이랑 위/아래로 정해진 개수의 행만 포함하기.

이렇게 어떤 행들이 윈도우 프레임에 들어갈지 ROWS랑 RANGE가 결정해.

ROWS 사용하기

ROWS는 행의 실제 위치 기준으로 윈도우 프레임을 정해. 즉, 위에서 아래로 행을 순서대로 세는 거지, 행 안의 값이 뭔지는 상관없어.

문법

윈도우_함수 OVER (
    ORDER BY 컬럼
    ROWS BETWEEN 시작 AND 끝
)

주요 표현들:

• CURRENT ROW — 현재 행.
• 숫자 PRECEDING — 현재 행 위에 있는 정해진 개수의 행.
• 숫자 FOLLOWING — 현재 행 아래에 있는 정해진 개수의 행.
• UNBOUNDED PRECEDING — 윈도우의 맨 처음부터.
• UNBOUNDED FOLLOWING — 윈도우의 맨 끝까지.

예시: 현재 행과 위 2개 행까지 누적 합 구하기

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

설명:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 현재 행이랑 그 위에 2개 행을 포함한다는 뜻이야.
• 누적 합은 이 세 개 행만 가지고 계산돼.

결과:

예시: 고정된 행 개수로 "슬라이딩 윈도우" 분석

문제: 현재 행과 아래 2개 행까지 평균 급여를 구해보자.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

결과:

RANGE 사용하기

RANGE는 행의 위치가 아니라 값 기준으로 윈도우 프레임을 만든다. 즉, ORDER BY 컬럼 값이 지정한 범위 안에 들어가면 그 행이 프레임에 포함돼.

문법

윈도우_함수 OVER (
    ORDER BY 컬럼
    RANGE BETWEEN 시작 AND 끝
)

예시: 값의 범위로 누적 합 구하기

문제: 현재 행의 급여와 ±2000 차이 나는 행들만 누적 합에 포함시켜보자.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

설명:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING는 salary가 현재 행 기준 ±2000 범위에 있는 행만 포함한다는 뜻이야.

결과:

ROWS랑 RANGE 비교

• ROWS는 실제 행 개수로 동작해. 값이 뭔지는 신경 안 써.
• RANGE는 ORDER BY 컬럼의 값으로 정한 논리적 범위로 동작해.

비교 예시를 들어볼게. 테이블 sales에 이런 데이터가 있다고 하자:

쿼리 비교:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

결과:

여기선 각 행이 실제로 등장하는 순서대로 합산돼.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

결과:

여기선 1이랑 2가 합쳐졌어, 둘 다 amount = 100이니까. RANGE는 amount 컬럼에서 중복 값을 다 포함시켜.

실전 문제 예시

1. 수익 증가 계산하기

문제: 이전 행과 비교해서 수익이 얼마나 변했는지 구해보자.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. 현재 행을 그룹 평균과 비교하기

문제: 각 부서에서 직원 급여가 부서 평균과 얼마나 차이 나는지 계산해보자.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

ROWS랑 RANGE 쓸 때 실수하는 부분

행 순서(ORDER BY)를 잘못 지정함: 정렬 기준을 안 주면 PostgreSQL이 에러 내. 현재 행이 뭔지 못 찾으니까.

ROWS랑 RANGE를 한 쿼리에서 섞어씀: 데이터에 따라 방식을 골라야 해. ROWS는 행 개수로, RANGE는 값의 범위로 쓸 때 적합해.

RANGE에서 중복 값 빠뜨림: RANGE는 중복 값까지 다 포함하니까, 결과가 생각보다 많이 달라질 수 있어. 이 점 꼭 기억해!