PostgreSQL Window Function 심화

글쓴이 /

Window Function이 필요한 이유

일반 집계 함수(SUM, COUNT, AVG)는 결과를 하나의 행으로 축소합니다. 하지만 “각 행의 값을 유지하면서 동시에 집계 결과를 참조”해야 하는 경우가 많습니다. 매출 순위, 누적 합계, 이동 평균, 이전 행과의 차이 등이 대표적입니다. Window Function은 행을 그룹화하지 않고 각 행에 대해 “창(window)” 범위의 집계를 계산합니다.

기본 문법과 OVER 절

-- 기본 구조
함수() OVER (
  PARTITION BY 그룹_컬럼
  ORDER BY 정렬_컬럼
  ROWS/RANGE BETWEEN 시작 AND 끝
)

-- 예시: 부서별 급여 순위
SELECT
  name,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank,
  salary - AVG(salary) OVER (
    PARTITION BY department
  ) AS diff_from_avg
FROM employees;
  • PARTITION BY: 윈도우를 그룹으로 분할 (GROUP BY와 유사하지만 행 유지)
  • ORDER BY: 윈도우 내 행 정렬 순서
  • ROWS/RANGE BETWEEN: 윈도우 프레임 범위 지정

순위 함수: ROW_NUMBER·RANK·DENSE_RANK

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- 결과 (동일 급여 시 차이점)
-- name     salary  row_num  rank  dense_rank
-- 김철수    8000      1       1       1
-- 이영희    7000      2       2       2
-- 박지민    7000      3       2       2    ← RANK: 동순위
-- 최민수    6000      4       4       3    ← RANK: 4로 건너뜀, DENSE_RANK: 3
함수 동일 값 처리 용도
ROW_NUMBER() 무조건 연속 번호 페이지네이션, 중복 제거
RANK() 동순위 후 건너뜀 경쟁 순위 (1,2,2,4)
DENSE_RANK() 동순위, 건너뛰지 않음 연속 순위 (1,2,2,3)

실전 활용 — 부서별 TOP 3 급여자 추출:

SELECT * FROM (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

LAG·LEAD: 이전·다음 행 참조

-- 월별 매출과 전월 대비 증감율
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month))::numeric
    / LAG(revenue, 1) OVER (ORDER BY month) * 100, 1
  ) AS growth_pct
FROM monthly_sales;

-- 결과
-- month    revenue   prev_month  growth_pct
-- 2026-01  1000000   NULL        NULL
-- 2026-02  1200000   1000000     20.0
-- 2026-03  1150000   1200000     -4.2

-- LEAD: 다음 행 참조
SELECT
  order_id,
  created_at,
  LEAD(created_at) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) AS next_order_at,
  LEAD(created_at) OVER (
    PARTITION BY user_id
    ORDER BY created_at
  ) - created_at AS days_between_orders
FROM orders;
  • LAG(column, n, default): n행 이전 값 (기본 1행)
  • LEAD(column, n, default): n행 이후 값
  • 세 번째 인자로 NULL 대신 기본값 지정 가능

FIRST_VALUE·LAST_VALUE·NTH_VALUE

-- 부서별 최고/최저 급여와 본인 급여 비교
SELECT
  name,
  department,
  salary,
  FIRST_VALUE(name) OVER w AS top_earner,
  FIRST_VALUE(salary) OVER w AS max_salary,
  LAST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner,
  NTH_VALUE(salary, 2) OVER w AS second_highest
FROM employees
WINDOW w AS (
  PARTITION BY department
  ORDER BY salary DESC
);

주의: LAST_VALUE는 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이므로, 전체 파티션의 마지막 값을 얻으려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING을 명시해야 합니다.

윈도우 프레임: ROWS vs RANGE

프레임은 현재 행을 기준으로 집계 범위를 지정합니다.

-- ROWS: 물리적 행 수 기준
-- 최근 3일 이동 평균 (행 기준)
SELECT
  date,
  revenue,
  AVG(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3
FROM daily_sales;

-- RANGE: 논리적 값 범위 기준
-- 급여 ±1000 범위 내 직원 수
SELECT
  name, salary,
  COUNT(*) OVER (
    ORDER BY salary
    RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
  ) AS similar_salary_count
FROM employees;

-- GROUPS: 동일 값 그룹 기준 (PostgreSQL 11+)
SELECT
  date, revenue,
  SUM(revenue) OVER (
    ORDER BY date
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS sum_3_groups
FROM daily_sales;
프레임 기준 예시
ROWS 물리적 행 수 앞 2행 + 현재 행
RANGE ORDER BY 값 범위 ±1000 값 범위
GROUPS 동일 값 그룹 수 앞뒤 1그룹

실전: 누적 합계와 퍼센타일

-- 누적 매출
SELECT
  date,
  revenue,
  SUM(revenue) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_sales;

-- 매출 비율 (전체 대비)
SELECT
  product_name,
  revenue,
  ROUND(
    revenue::numeric / SUM(revenue) OVER () * 100, 1
  ) AS revenue_pct,
  ROUND(
    SUM(revenue) OVER (ORDER BY revenue DESC)::numeric
    / SUM(revenue) OVER () * 100, 1
  ) AS cumulative_pct  -- 파레토 분석용
FROM product_sales
ORDER BY revenue DESC;

-- 백분위 계산
SELECT
  name,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cume_dist,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

WINDOW 절로 재사용

동일한 윈도우 정의를 반복하지 않고 WINDOW 절로 이름을 붙여 재사용합니다.

SELECT
  name,
  department,
  salary,
  RANK() OVER dept_salary AS rank,
  AVG(salary) OVER dept_salary AS dept_avg,
  MAX(salary) OVER dept_salary AS dept_max,
  salary - AVG(salary) OVER dept_salary AS diff
FROM employees
WINDOW dept_salary AS (
  PARTITION BY department
  ORDER BY salary DESC
)
ORDER BY department, rank;

성능 최적화

  • 인덱스: PARTITION BY + ORDER BY 컬럼에 복합 인덱스 생성
  • ROWS vs RANGE: ROWS가 RANGE보다 빠름 (동일 값 비교 불필요)
  • 프레임 제한: UNBOUNDED보다 고정 범위가 메모리 효율적
  • EXPLAIN ANALYZE: WindowAgg 노드의 실행 시간 확인
-- Window Function용 인덱스
CREATE INDEX idx_employees_dept_salary
ON employees (department, salary DESC);

-- EXPLAIN으로 실행 계획 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC)
FROM employees;

관련 글

위로 스크롤
WordPress Appliance - Powered by TurnKey Linux