SQL 윈도우 함수 마스터 — ROW_NUMBER RANK LAG LEAD

윈도우 함수란?

윈도우 함수(Window Function)는 GROUP BY 없이 행별로 집계/분석 연산을 수행하는 SQL 함수입니다. 각 행이 자신이 속한 "윈도우(파티션)" 내에서 계산된 결과를 반환하며, 원본 행을 유지한 채 분석 값을 추가할 수 있습니다.

순위 함수: ROW_NUMBER, RANK, DENSE_RANK

-- 부서별 급여 순위
SELECT
  name, department, salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 결과 예시:
-- name    | dept | salary | row_num | rank | dense_rank
-- 김철수  | 개발 | 8000   | 1       | 1    | 1
-- 이영희  | 개발 | 8000   | 2       | 1    | 1
-- 박민수  | 개발 | 7000   | 3       | 3    | 2

-- 부서별 TOP 3 추출
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

ROW_NUMBER vs RANK vs DENSE_RANK 차이

오프셋 함수: LAG, LEAD

-- 전월 대비 매출 변화
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS diff,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month))::numeric
    / LAG(revenue, 1) OVER (ORDER BY month) * 100, 1
  ) AS growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_sales;

-- 연속 로그인 일수 계산
WITH login_gaps AS (
  SELECT
    user_id, login_date,
    login_date - LAG(login_date) OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS gap
  FROM user_logins
)
SELECT user_id, login_date, gap,
  SUM(CASE WHEN gap = 1 THEN 0 ELSE 1 END) OVER (
    PARTITION BY user_id ORDER BY login_date
  ) AS streak_group
FROM login_gaps;

집계 윈도우 함수

-- 누적합, 이동평균
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  COUNT(*) OVER (
    PARTITION BY EXTRACT(MONTH FROM order_date)
  ) AS monthly_order_count
FROM orders;

프레임 절(Frame Clause) 정리

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — 처음부터 현재 행까지 (누적)
• ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING — 현재 행 기준 전후 3행
• RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW — 날짜 기반 범위
• NTILE(n)으로 데이터를 n개 그룹으로 균등 분할할 수 있습니다
• PERCENT_RANK(), CUME_DIST()로 백분위 계산이 가능합니다