[DB] 윈도우 함수

일반 집계 vs 윈도우 함수

• 가장 큰 차이 : 행이 유지되는가
• 원본 데이터를 유지하면서 집계값을 함께 볼 수 있다.

-- 일반 집계 : 행이 줄어듦
SELECT region, SUM(amount)
FROM orders
GROUP BY region;

결과:
┌────────┬────────┐
│ region │  sum   │
├────────┼────────┤
│ 서울   │ 500000 │
│ 부산   │ 300000 │
└────────┴────────┘

-- 윈도우 함수 : 행이 유지됨
SELECT
    order_id,
    region,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS region_total
FROM orders;

결과:
┌──────────┬────────┬────────┬──────────────┐
│ order_id │ region │ amount │ region_total │
├──────────┼────────┼────────┼──────────────┤
│ 1        │ 서울   │ 200000 │ 500000       │
│ 2        │ 서울   │ 300000 │ 500000       │
│ 3        │ 부산   │ 300000 │ 300000       │
└──────────┴────────┴────────┴──────────────┘

 

 

기본 문법

• PARTITION BY는 그룹을 나누고, ORDER BY는 그룹 안에서 순서를 정한다.

함수() OVER (
    PARTITION BY 그룹 기준 컬럼   -- GROUP BY 같은 역할
    ORDER BY 정렬 기준 컬럼       -- 순서가 필요한 함수에 사용
)

 

 

ROW_NUMBER - 순번 매기기

• 그룹 안에서 순번을 매긴다. 중복 없이 1부터 순서대로 부여된다.

SELECT
    customer_id,
    order_id,
    ordered_at,
    ROW_NUMBER() OVER (
    	PARTITION BY customer_id
        ORDER BY ordered_at
    ) AS order_seq
FROM orders;

 

결과:
┌─────────────┬──────────┬────────────┬───────────┐
│ customer_id │ order_id │ ordered_at │ order_seq │
├─────────────┼──────────┼────────────┼───────────┤
│ C1          │ 1        │ 2024-01-01 │ 1         │
│ C1          │ 5        │ 2024-01-15 │ 2         │
│ C2          │ 2        │ 2024-01-03 │ 1         │
└─────────────┴──────────┴────────────┴───────────┘

 

• 고객의 첫 번째 주문만 추출

WITH ranked AS (
    SELECT *,
    	ROW_NUMBER() OVER (
        	PARTITION BY customer_id
            ORDER BY ordered_at
        ) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn = 1;

 

 

RANK / DENSE_RANK - 순위 매기기

• RANK는 동점 시 다음 순위를 건너뛰고, DENSE_RANK는 건너뛰지 않는다.

SELECT
    customer_id,
    SUM(amount) AS total,
    RANK()          OVER (ORDER BY SUM(amount) DESC)
    DENSE_RANK()    OVER (ORDER BY SUM(amount) DESC)
FROM orders
GROUP BY customer_id;

 

결과:
┌─────────────┬────────┬─────┬───────────┐
│ customer_id │ total  │ rnk │ dense_rnk │
├─────────────┼────────┼─────┼───────────┤
│ C1          │ 500000 │ 1   │ 1         │
│ C2          │ 300000 │ 2   │ 2         │
│ C3          │ 300000 │ 2   │ 2         │  ← 동점
│ C4          │ 100000 │ 4   │ 3         │  ← RANK는 4, DENSE_RANK는 3
└─────────────┴────────┴─────┴───────────┘

 

 

LAG / LEAD - 이전 / 다음 행 참조

• 시계열 데이터에서 이전 값이나 다음 값 참조할 때 쓴다.
• LEAD는 반대로 다음 행을 참조한다. 예를 들어 "다음 주문까지 며칠이 걸렸는가"를 계산할 때 쓴다.

-- 전월 대비 매출 증감
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month)              AS prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY month)    AS diff
 FROM monthly_revenue;

 

결과:
┌───────┬─────────┬──────────────┬────────┐
│ month │ revenue │ prev_revenue │  diff  │
├───────┼─────────┼──────────────┼────────┤
│ 01    │ 100000  │ NULL         │ NULL   │
│ 02    │ 120000  │ 100000       │  20000 │
│ 03    │ 110000  │ 120000       │ -10000 │
└───────┴─────────┴──────────────┴────────┘

 

 

SUM / AVG OVER - 누적 집계

• ORDER BY를 함께 쓰면 누적 합계나 이동 평균을 구할 수 있다.

-- 누적 매출
SELECT
    ordered_at,
    amount,
    SUM(amount) OVER (ORDER BY ordered_at) AS cumulative_sum
FROM orders;

 

결과:
┌────────────┬────────┬────────────────┐
│ ordered_at │ amount │ cumulative_sum │
├────────────┼────────┼────────────────┤
│ 2024-01-01 │ 100000 │ 100000         │
│ 2024-01-02 │ 200000 │ 300000         │
│ 2024-01-03 │ 150000 │ 450000         │
└────────────┴────────┴────────────────┘

 

• 3일 이동 평균

SELECT
    ordered_at,
    amount,
    AVG(amount) OVER (
    	ORDER BY ordered_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3d
FROM orders;

 

 

NTILE - 분위수 나누기

• 데이터를 N개의 그룹으로 균등하게 나눈다.

-- 고객을 매출 기준으로 4분위로 나누기
SELECT
    customer_id,
    total_amount,
    NTILE(4) OVER (ORDER BY total_amount DESC) AS quartile
FROM customer_stats;
-- quartile 1 = 상위 25% (VIP)
-- quartile 4 = 하위 25%

 

 

예제

• 고객별 첫 번째 구매 날짜를 구하시오.

WITH ranked AS (
    SELECT *,
    	ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS rn
    FROM orders
)
SELECT customer_id, ordered_at AS first_order_date
FROM ranked WHERE rn = 1;

 

• 전월 대비 매출 증감률을 구하시오.

SELECT
    month,
    revenue,
    ROUND(
    	(revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month) * 100, 2
    ) AS growth_rate
FROM monthly_revenue;

 

• 상위 10% 고객을 추출하시오.

WITH ranked AS (
    SELECT customer_id, total_amount,
    	NTILE(10) OVER (ORDER BY total_amount DESC) AS decile
    FROM customer_stats
)
SELECT * FROM ranked WHERE decile = 1;

 

• 3일 이동 평균을 구하시오.

SELECT
    date,
    revenue,
    AVG(revenue) OVER (
    	ORDER BY date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM daily_revenue;

 

 

정리

• 윈도우 함수의 핵심은 행을 유지하면서 집계한다는 것이다.

함수	용도
ROW_NUMBER	그룹 내 순번 (중복 없음)
RANK	순위 (동점 시 다음 순위 건너뜀)
DENSE_RANK	순위 (동점 시 다음 순위 안 건너뜀)
LAG	이전 행 값 참조
LEAD	다음 행 값 참조
SUM/AVG OVER	누적 합계/이동 평균
NTILE	N분위로 균등 분할