백엔드 데이터 인프라 18편. 윈도우 함수(ROW_NUMBER·RANK·LAG·LEAD)와 CTE·집계 위에 올라가는 고급 SQL 풀어쓴 학습 노트.
이 글은 백엔드 데이터 인프라 시리즈 70편 중 18편이에요. Part 1 PostgreSQL 튜토리얼의 마지막 — "단순 SQL을 넘어 진짜 SQL의 힘". 윈도우 함수 + CTE + 재귀 + INHERITANCE 4가지를 한 편에.
윈도우 함수 — 그룹 안의 행 비교
GROUP BY 가 "그룹별 한 결과" 라면, 윈도우 함수 는 "그룹 컨텍스트를 유지하면서 각 행에 결과 박기".
-- 매출 순위
SELECT
product_id,
amount,
RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;
| product_id | amount | rank |
|---|---|---|
| 5 | 50000 | 1 |
| 3 | 40000 | 2 |
| 7 | 40000 | 2 |
| 1 | 30000 | 4 |
각 행이 "전체에서 몇 등인가" 를 옆 컬럼에. GROUP BY 처럼 행을 줄이지 않음.
OVER 절 — 윈도우 정의
함수() OVER (
PARTITION BY 그룹컬럼
ORDER BY 정렬컬럼
ROWS BETWEEN ... AND ...
)
PARTITION BY — 그룹
-- 사용자별 매출 순위
SELECT
user_id, order_id, amount,
RANK() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_in_user
FROM orders;
PARTITION BY user_id = "사용자 안에서 순위". 사용자 A의 1등·B의 1등이 따로.
주요 윈도우 함수
순위 함수
ROW_NUMBER() OVER (ORDER BY amount DESC) -- 1, 2, 3, 4 (동률에도 다른 번호)
RANK() OVER (ORDER BY amount DESC) -- 1, 2, 2, 4 (동률 같은 순위, 다음 건너뜀)
DENSE_RANK() OVER (ORDER BY amount DESC) -- 1, 2, 2, 3 (동률 같은 순위, 다음 X 건너뜀)
NTILE(4) OVER (ORDER BY amount DESC) -- 4분위로 나누기
위치 비교 — LAG·LEAD
-- 전 행 비교
SELECT
date, amount,
LAG(amount) OVER (ORDER BY date) AS prev_amount,
LEAD(amount) OVER (ORDER BY date) AS next_amount,
amount - LAG(amount) OVER (ORDER BY date) AS diff
FROM daily_sales;
매일 매출이 "전날 대비 얼마나 늘었나" 한 SQL로.
FIRST_VALUE·LAST_VALUE
SELECT
user_id, order_id, amount,
FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_amount
FROM orders;
각 사용자의 "첫 주문 금액" 을 모든 행에.
집계 함수도 OVER 가능
SELECT
user_id, order_id, amount,
SUM(amount) OVER (PARTITION BY user_id) AS user_total,
AVG(amount) OVER (PARTITION BY user_id) AS user_avg
FROM orders;
사용자별 합·평균을 "각 주문 행마다" 표시. 대시보드에 자주.
누적 합 — Cumulative Sum
SELECT
date, amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM daily_sales;
"오늘까지의 누적 매출". 차트 데이터의 표준.
CTE — WITH 절
복잡한 쿼리 분해.
WITH active_users AS (
SELECT id, name FROM users WHERE deleted_at IS NULL
),
user_stats AS (
SELECT
user_id,
COUNT(*) AS orders,
SUM(amount) AS total
FROM orders
GROUP BY user_id
)
SELECT u.name, s.orders, s.total
FROM active_users u
JOIN user_stats s ON u.id = s.user_id
ORDER BY s.total DESC;
WITH name AS (...) = "임시 결과 집합에 이름". SQL 가독성·재사용성 한 단계 상승. 한국 회사 백엔드 표준.
CTE + 윈도우 함수 조합
WITH ranked_orders AS (
SELECT
user_id, id, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders
)
SELECT * FROM ranked_orders WHERE rn <= 3; -- 각 사용자별 TOP 3
"각 그룹 TOP-N" 표준 패턴. JPA로는 거의 불가능 — native SQL 필수.
재귀 CTE — RECURSIVE
계층 구조 (트리·그래프) 표현.
-- 사원 계층 (관리자·부하 관계)
WITH RECURSIVE org_chart AS (
-- 시작점 (사장)
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 재귀
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;
level | name
------+--------
1 | CEO
2 | VP Engineering
2 | VP Marketing
3 | Senior Dev
3 | Junior Dev
조직도·카테고리 트리·메뉴 계층 등 표준 패턴.
INHERITANCE — PG 특별 (가끔 사용)
PG는 "테이블 상속" 도 지원.
CREATE TABLE animals (
id BIGSERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE dogs (
breed TEXT
) INHERITS (animals);
dogs 는 animals 의 컬럼 자동 상속. SELECT * FROM animals 시 — 자동으로 dogs 행도 포함.
거의 안 권장 — 파티셔닝(PG 10+) 으로 대체. 가끔 옛 코드에서 발견.
고급 — UPSERT 변종
-- 통계 갱신 (있으면 더하고 없으면 1)
INSERT INTO daily_visits (date, count)
VALUES (CURRENT_DATE, 1)
ON CONFLICT (date) DO UPDATE
SET count = daily_visits.count + 1;
대시보드·카운터 패턴.
EXPLAIN 미리 보기
EXPLAIN ANALYZE
SELECT user_id, RANK() OVER (PARTITION BY user_id ORDER BY amount DESC)
FROM orders
WHERE created_at > '2026-01-01';
EXPLAIN ANALYZE = 쿼리 계획 + 실제 실행 시간. 40편에서 깊이.
Spring 백엔드 흐름
JPA가 자동 생성하는 SQL은 — 윈도우 함수·재귀 CTE 거의 불가능. native SQL 또는 QueryDSL (자바 백엔드 입문 50편) 필요.
@Query(value = """
SELECT *
FROM (
SELECT
id, user_id, amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
FROM orders
) ranked
WHERE rn <= 3
""", nativeQuery = true)
List<Order> findTop3PerUser();
nativeQuery = true 로 PG 윈도우 함수 직접.
함정 5가지
(1) GROUP BY vs 윈도우 함수 혼동
- GROUP BY = 그룹별 1행
- 윈도우 = 각 행 그대로 + 그룹 컨텍스트
(2) RANK vs DENSE_RANK
- RANK = 동률 후 다음 순위 건너뜀 (1, 2, 2, 4)
- DENSE_RANK = 건너뜀 X (1, 2, 2, 3)
(3) PARTITION BY 누락
ROW_NUMBER() OVER (ORDER BY amount DESC)
-- 전체 데이터 기준. 그룹별 원하면 PARTITION BY 필수
(4) 재귀 CTE 무한 루프
WITH RECURSIVE r AS (
SELECT 1
UNION ALL
SELECT 1 FROM r -- ❌ 종료 조건 없음
)
SELECT * FROM r;
종료 조건 (WHERE) 필수.
(5) 윈도우 함수 + WHERE 순서
SELECT *, ROW_NUMBER() OVER (...) AS rn
FROM orders
WHERE rn = 1; -- ❌ rn은 같은 레벨에서 못 참조
서브쿼리 또는 CTE 로 감싸기.
"각 그룹 TOP-N" · "전 행 대비 변화" · "누적 합" · "순위·백분위" 4가지 시나리오에 윈도우 함수 = 한 SQL로 끝. GROUP BY로는 표현 불가. CTE와 조합이 한국 백엔드 표준.
한 줄 정리 — 윈도우 함수 = 그룹 컨텍스트 유지 + 각 행에 결과. ROW_NUMBER·RANK·LAG·LEAD·SUM OVER 5종. CTE (WITH) 로 복잡한 쿼리 분해. 재귀 CTE는 계층 구조. JPA로 안 됨 — native SQL 필수.
시험 직전 한 번 더 — 윈도우 함수·CTE 입문자가 매번 헷갈리는 것
- 윈도우 함수 = 그룹 컨텍스트 유지 + 각 행 결과
OVER (PARTITION BY ... ORDER BY ...)- ROW_NUMBER = 1, 2, 3, 4 (동률에도 다름)
- RANK = 1, 2, 2, 4 (동률 같은 순위, 건너뜀)
- DENSE_RANK = 1, 2, 2, 3 (건너뜀 X)
- NTILE(N) = N분위
- LAG = 전 행 값, LEAD = 다음 행 값
- FIRST_VALUE·LAST_VALUE = 그룹의 첫·마지막
- SUM·AVG OVER = 집계 함수도 윈도우 가능
- 누적 합 =
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - GROUP BY = 행 줄임, 윈도우 = 행 유지
- CTE =
WITH name AS (...) SELECT ... - CTE는 가독성·재사용 표준
- 재귀 CTE =
WITH RECURSIVE(트리·계층) - 종료 조건 (UNION ALL의 두 번째) 필수
- 각 그룹 TOP-N = CTE + ROW_NUMBER 표준
- 윈도우 함수는 WHERE 같은 레벨 참조 X — 서브쿼리 감싸기
- INHERITANCE = PG 특별 (옛 — 파티셔닝 권장)
- JPA로 윈도우 함수 = native SQL
- @Query(nativeQuery = true) 표준
- EXPLAIN ANALYZE = 윈도우 함수 비용 확인
- 한국 백엔드 = 대시보드·통계에 윈도우 함수 무조건
- 면접 단골 = RANK vs DENSE_RANK 차이
시리즈 다른 편
- Part 1 PostgreSQL 튜토리얼: 1편 · 11편 SELECT · 12편 JOIN · 16편 VIEW · 17편 트랜잭션 · 18편 (현재 글)
- Part 1 PostgreSQL 튜토리얼 18편 완주 🎉 — 19편부터 Part 2 SQL Language 깊이 시작
시리즈 다음 글
다음 글(19편)에서는 PostgreSQL SQL 어휘 구조 — Part 2 SQL Language 깊이의 시작.
공식 문서: PostgreSQL 18 — Tutorial: Window Functions + Advanced Features에서 더 자세한 사양을 확인할 수 있어요.