윈도우 함수 — OVER·PARTITION·ROW_NUMBER

GROUP BY 는 행을 합쳐 통계를 냅니다. 하지만 "각 행의 데이터는 그대로 두고 옆 그룹 통계나 순위를 한 컬럼으로 붙이고 싶다" 면? 그게 윈도우 함수입니다. SQL 의 가장 강력한 도구 중 하나 — 11편은 매일 만나는 6개를 정리합니다.

OVER() — 윈도우 함수의 핵심

-- 일반 집계 — 행이 줄어듦
SELECT category_id, COUNT(*) FROM products GROUP BY category_id;

-- 윈도우 — 모든 행이 살아남음 + 그룹 통계가 한 컬럼으로
SELECT id, name, category_id,
       COUNT(*) OVER (PARTITION BY category_id) AS category_count
FROM   products;
-- 결과: 모든 상품 + 각자 속한 카테고리의 총 개수

ROW_NUMBER · RANK · DENSE_RANK — 순위 3종

-- 카테고리별 가격 순위
SELECT name, category_id, price,
       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn,
       RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rk,
       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS drk
FROM   products;

-- 동점 처리 차이 (가격 10000, 10000, 8000, 5000)
-- ROW_NUMBER : 1, 2, 3, 4   ← 동점도 다른 번호
-- RANK       : 1, 1, 3, 4   ← 동점은 같은 순위, 다음은 건너뜀
-- DENSE_RANK : 1, 1, 2, 3   ← 동점은 같은 순위, 다음은 안 건너뜀

실전 — 카테고리별 1위만 뽑기

-- "각 카테고리에서 가격 1위 상품"
WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM   products
)
SELECT * FROM ranked WHERE rn = 1;

가장 자주 쓰는 패턴입니다. JOIN + GROUP BY 로 만들면 복잡해지는데, ROW_NUMBER + PARTITION 으로 깔끔하게.

LAG · LEAD — 이전/다음 행과 비교

-- 일별 매출의 전일 대비 증감
SELECT day,
       revenue,
       LAG(revenue, 1)  OVER (ORDER BY day) AS prev,
       revenue - LAG(revenue, 1) OVER (ORDER BY day) AS diff,
       LEAD(revenue, 1) OVER (ORDER BY day) AS next
FROM   daily_revenue
ORDER BY day;

-- 사용자별 이전 주문 시간차
SELECT user_id, created_at,
       LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_at,
       created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS gap
FROM   orders;

SUM/AVG OVER — 누적·이동 평균

-- 누적 매출
SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day) AS running_total
FROM   daily_revenue;

-- 7일 이동 평균
SELECT day, revenue,
       AVG(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_7d_avg
FROM   daily_revenue;

FIRST_VALUE · LAST_VALUE · NTH_VALUE

-- 각 카테고리의 최고 가격을 모든 행에 붙이기
SELECT name, price, category_id,
       FIRST_VALUE(price) OVER (
         PARTITION BY category_id ORDER BY price DESC
       ) AS category_top_price,
       price * 100.0 / FIRST_VALUE(price) OVER (
         PARTITION BY category_id ORDER BY price DESC
       ) AS pct_of_top
FROM   products;

frame (RANGE/ROWS) — 윈도우의 범위 미세 조정

WINDOW 절 — 재사용

-- 같은 윈도우를 여러 함수에 쓸 때
SELECT id, name, price,
       ROW_NUMBER() OVER w AS rn,
       SUM(price)   OVER w AS running
FROM   products
WINDOW w AS (PARTITION BY category_id ORDER BY price DESC);

윈도우 함수 vs GROUP BY — 한 표