SQL 윈도우 함수 구조

SQL에서 윈도우 함수는 행(row) 단위로 연산을 수행하면서도 그룹화 없이 전체 데이터에 대한 계산을 수행할 수 있는 기능

 

기본 구조

윈도우_함수() OVER (
    PARTITION BY 컬럼
    ORDER BY 컬럼
    ROWS 또는 RANGE
)

 

• PARTITION BY → 그룹을 나누는 기준
• ORDER BY → 정렬 기준
• ROWS 또는 RANGE → 윈도우(슬라이딩 범위)를 설정하는 옵션

---

기본 예제

SELECT
    customer_id
    , visited_on
    , amount
    , SUM(amount) OVER (ORDER BY visited_on) AS rolling_sum
FROM Customer

• SUM(amount) OVER (...)  → 각 행을 기준으로 이전 행의 값들을 누적해서 합산
• PARTITION BY 없이 전체 데이터를 기준으로 정렬

결과

customer_id	visited_on	amount	rolling_sum
1	2024-01-01	10	10
2	2024-01-02	15	25
3	2024-01-03	20	45
4	2024-01-04	25	70

• 이전 행의 값까지 포함하여 합산(누적 합계) 계산됨
• 판다스의 cumsum() 과 유사한 기능

---

PARTITION BY를 사용하면 각 그룹별로 연산 수행

SELECT
    customer_id
    , category
    , amount
    , SUM(amount) OVER (PARTITION BY category ORDER BY customer_id) AS category_rolling_sum
FROM Customer

• 각 category 별로 그룹을 나누고 (PARTITION BY category), 그룹 내부에서 customer_id 기준으로 정렬 후 SUM() 계산

customer_id	category	amount	category_rolling_sum
1	A	10	10
2	A	15	25
3	A	20	45
4	B	30	30
5	B	35	65

• category가 다른 경우, rolling sum이 별도로 계산됨
• 판다스의 groupby() 와 유사한 기능

---

ROWS vs RANGE 윈도우 크기 설정

• ORDER BY를 사용하면 기본적으로 모든 이전 행을 포함한 누적 계산(누적합, 누적 평균 등)이 수행
• 하지만, ROWS 또는 RANGE를 추가하면 특정 범위 내에서만 연산을 수행

ROWS BETWEEN → 행(row) 개수 기준

SELECT
    visited_on
    , amount
    , SUM(amount) OVER (
        ORDER BY visited_on
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_sum_7days
FROM Customer

• 현재 행을 기준으로 최근 6개의 행을 포함하여 합계를 계산 (총 7개 행)
• 즉, "마지막 7개 행:을 기준으로 이동 합계를 계산하는 것
• 판다스의 rolling(7).sum() 과 유사한 기능

visited_on	amount	rolling_sum_7days
2024-01-01	10	10
2024-01-02	15	25
2024-01-03	20	45
2024-01-04	25	70
2024-01-05	30	100
2024-01-06	35	135
2024-01-07	40	175
2024-01-08	20	185

• 각 행을 기준으로 최근 7개의 데이터를 포함하여 이동 합계를 계산함

---

RANGE BETWEEN → 날짜 (date) 기준

SELECT
    visited_on
    , amount
    , SUM(amount) OVER (
    	ORDER BY visited_on
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_sum_7days
FROM Customer

• 현재 형을 기준으로 최근 6일 동안의 데이터를 포함하여 합계를 계산
• 날짜(visited_on) 기준이므로, 데이터가 없는 날짜가 있어도 정확하게 7일 기간 내 데이터를 합산
• 판다스의 rolling("7D").sum() 과 동일한 기능
• INTERVAL 은 SQL에서 날짜 및 시간 연산을 수행할 때 사용하는 키워드
• INTERVAL 'n 단위' → n 은 숫자, 단위는 시간 또는 날짜 단위(DAY, HOUR, MONTH, YEAR 등)

visited_on	amount	rolling_sum_7days
2024-01-01	10	10
2024-01-02	15	25
2024-01-05	20	45
2024-01-07	25	70
2024-01-08	30	90
2024-01-10	35	110

• 날짜(visited_on) 기준으로 최근 7일 동안의 amount 합계를 계산
• 데이터가 없는 날짜가 있어도 정확하게 7일 기간 내 데이터만 고려

---

정리 

윈도우 함수 구성 요소	설명
PARTITION BY	그룹별 연산 수행 (groupby() 역할)
ORDER BY	정렬 기준 설정 (윈도우의 순서 결정)
ROWS BETWEEN	행 개수(row) 기준으로 윈도우 크기 지정
RANGE BETWEEN	날짜 또는 값 범위 기준으로 윈도우 크기 지정

• ROWS 는 행(row) 기준, RANGE는 값 또는 날짜 기준

추가

키워드	설명
N PRECEDING	현재 행을 기준으로 N개 이전 행 포함
M FOLLOWING	현재 행을 기준으로 M개 이후 행 포함
CURRENT ROW	현재 행만 포함
UNBOUNDED PRECEDING	데이터셋의 첫 번째 행부터 포함
UNBOUNDED FOLLOWING	데이터셋의 마지막 행까지 포