6장 : SQL 심화 — 집계와 윈도우 함수
윈도우 함수 기초
GROUP BY로 집계하면 개별 행이 사라지고 그룹당 하나의 행만 남습니다. 하지만 부서별 급여 순위처럼 집계 결과와 개별 행을 동시에 보고 싶을 때가 있습니다. 이것이 윈도우 함수의 존재 이유입니다.
윈도우 함수는 SQL:2003 표준에 도입되었으며, 현재 PostgreSQL, MySQL 8.0+, Oracle, SQL Server, SQLite 3.25+ 등 대부분의 주요 DBMS에서 지원합니다. 윈도우 함수를 잘 활용하면 서브쿼리나 셀프 조인 없이도 복잡한 분석을 간결하게 표현할 수 있습니다.
윈도우 함수란
윈도우 함수(Window Function)는 각 행에 대해 관련된 행들의 집합(윈도우)을 기반으로 계산을 수행하는 함수입니다. GROUP BY와 달리 행을 축소하지 않고 원래 행을 그대로 유지한 채 결과를 추가합니다.
GROUP BY
이름 부서 급여 부서 평균급여
김철수 개발 5000 → 그룹화 개발 4500
이영희 개발 4000 기획 3500
박민수 기획 3500
(개별 행 사라짐!)
윈도우 함수
이름 부서 급여 부서_평균 부서_순위
김철수 개발 5000 4500 1
이영희 개발 4000 4500 2
박민수 기획 3500 3500 1
(개별 행 유지 + 집계 결과 추가!)┌───────────────────────────────────────────────────────────┐
│ GROUP BY │
│ * 행을 그룹으로 축소 (N행 → G그룹) │
│ * SELECT에 집계 함수 또는 GROUP BY 컬럼만 가능 │
│ * 그룹 단위 결과만 조회 가능 │
├───────────────────────────────────────────────────────────┤
│ 윈도우 함수 │
│ * 행을 유지 (N행 → N행) │
│ * 개별 행과 집계 결과를 동시에 표시 │
│ * 순위, 누적합, 이동 평균 등 행 간 비교 가능 │
│ * PARTITION BY로 그룹 지정 (생략 시 전체가 하나의 윈도우)│
└───────────────────────────────────────────────────────────┘OVER 절
윈도우 함수의 핵심은 OVER() 절입니다. OVER 절은 윈도우(행의 범위)를 정의하며, 그 안에서 PARTITION BY와 ORDER BY를 사용합니다.
함수(인자) OVER (
PARTITION BY 그룹_컬럼 -- 윈도우를 나누는 기준 (생략 시 전체)
ORDER BY 정렬_컬럼 -- 윈도우 내 정렬 순서
ROWS/RANGE BETWEEN ... -- 프레임 범위 (고급, 다음 절에서)
)PARTITION BY
PARTITION BY는 데이터를 그룹으로 나눕니다. GROUP BY와 비슷하지만 행을 축소하지 않습니다.
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg
FROM employees;| name | department | salary | dept_avg | diff_from_avg |
| ------ | ---------- | ------ | -------- | ------------- |
| 김철수 | 개발 | 5000 | 4500 | 500 |
| 이영희 | 개발 | 4000 | 4500 | -500 |
| 박민수 | 기획 | 3500 | 3500 | 0 |
| 최지은 | 기획 | 3500 | 3500 | 0 |PARTITION BY를 생략하면 전체 테이블이 하나의 윈도우가 됩니다.
SELECT name, salary,
AVG(salary) OVER () AS total_avg,
salary - AVG(salary) OVER () AS diff_from_total
FROM employees;ORDER BY
OVER 절 안의 ORDER BY는 윈도우 내에서 행의 순서를 지정합니다. 순위 함수에서는 필수이며, 집계 함수에서는 누적 계산의 기준이 됩니다.
-- ORDER BY 없음: 전체 합계
SELECT name, salary,
SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;
-- ORDER BY 있음: 누적 합계 (Running Total)
SELECT name, salary,
SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employees;ORDER BY 없음: ORDER BY salary:
| name | salary | dept_total | | name | salary | running_total |
|--------|--------|------------| |--------|--------|---------------|
| 이영희 | 4000 | 9000 | | 이영희 | 4000 | 4000 |
| 김철수 | 5000 | 9000 | | 김철수 | 5000 | 9000 |
ORDER BY를 지정하면 "처음부터 현재 행까지"가 기본 프레임이 됨이 차이는 매우 중요합니다. ORDER BY를 넣으면 기본 프레임이 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW로 바뀌어 누적 계산이 됩니다.
다중 PARTITION BY
여러 컬럼으로 파티션을 나눌 수 있습니다.
-- 부서 + 직급별 평균 급여
SELECT name, department, position, salary,
AVG(salary) OVER (
PARTITION BY department, position
) AS dept_pos_avg
FROM employees;윈도우 함수의 종류
윈도우 함수는 크게 세 가지로 분류됩니다.
┌─────────────────────────────────────────────────────┐
│ 1. 순위 함수 (Ranking) │
│ ROW_NUMBER, RANK, DENSE_RANK, NTILE │
├─────────────────────────────────────────────────────┤
│ 2. 집계 함수 (Aggregate) │
│ SUM, AVG, COUNT, MAX, MIN │
│ (일반 집계 함수를 OVER 절과 함께 사용) │
├─────────────────────────────────────────────────────┤
│ 3. 값 참조 함수 (Value) │
│ LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE │
│ (다음 절 "윈도우 함수 심화"에서 상세히 다룸) │
└─────────────────────────────────────────────────────┘ROW_NUMBER, RANK, DENSE_RANK
세 함수 모두 순위를 매기지만, 동일한 값(타이)이 있을 때 처리 방식이 다릅니다.
| 함수 | 동일 값 처리 | 100, 90, 90, 80 | 특징 |
|---|---|---|---|
| ROW_NUMBER | 고유 번호 | 1, 2, 3, 4 | 항상 연속, 타이 없음 |
| RANK | 건너뜀 | 1, 2, 2, 4 | 타이 후 순위 건너뜀 |
| DENSE_RANK | 안 건너뜀 | 1, 2, 2, 3 | 타이 후 다음 순위 |
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;| name | department | salary | rn | rnk | drnk |
| ------ | ---------- | ------ | --- | --- | ---- |
| 김철수 | 개발 | 5000 | 1 | 1 | 1 |
| 이영희 | 개발 | 4000 | 2 | 2 | 2 |
| 박지민 | 개발 | 4000 | 3 | 2 | 2 | ← 동일 급여
| 최서연 | 개발 | 3000 | 4 | 4 | 3 | ← RANK는 4, DENSE_RANK는 3
| 정유진 | 기획 | 6000 | 1 | 1 | 1 |
| 한도현 | 기획 | 5000 | 2 | 2 | 2 |언제 어떤 함수를 사용할까
* ROW_NUMBER: 고유한 일련번호가 필요할 때
예: 페이지네이션, 중복 제거, TOP-N (정확히 N개만 필요)
주의: 동일 값이 있을 때 어떤 행이 선택될지 비결정적
* RANK: 동점을 인정하고, 다음 순위를 건너뛸 때
예: 경쟁 순위 (1등, 공동2등, 4등...)
시험에서 "3등이 없다"는 것을 표현
* DENSE_RANK: 동점을 인정하되, 순위를 건너뛰지 않을 때
예: 등급 분류 (상위 1~2등급, 3등급...)
총 등급 수를 예측 가능NTILE
NTILE(N)은 행을 N개의 동일한 크기의 그룹(버킷)으로 나누고, 각 행에 그룹 번호를 부여합니다.
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;| name | salary | quartile |
| ---- | ------ | -------- |
| 박민수 | 3000 | 1 | ← 하위 25%
| 이영희 | 3500 | 1 |
| 최지은 | 4000 | 2 |
| 김철수 | 4500 | 2 |
| 정유진 | 5000 | 3 |
| 한도현 | 5500 | 3 |
| 오세준 | 6000 | 4 | ← 상위 25%
| 장하린 | 7000 | 4 |행 수가 N으로 나누어떨어지지 않으면 앞쪽 그룹에 1개씩 더 배분됩니다. 예를 들어 10개 행을 NTILE(3)으로 나누면 4, 3, 3개씩 배분됩니다.
SELECT student_name, score,
CASE NTILE(5) OVER (ORDER BY score DESC)
WHEN 1 THEN 'A'
WHEN 2 THEN 'B'
WHEN 3 THEN 'C'
WHEN 4 THEN 'D'
WHEN 5 THEN 'F'
END AS grade
FROM exam_results;집계 함수를 윈도우 함수로 사용
SUM, AVG, COUNT, MAX, MIN 등 일반 집계 함수도 OVER 절과 함께 윈도우 함수로 사용할 수 있습니다.
SELECT name, department, salary,
-- 부서별 통계 (개별 행 유지)
COUNT(*) OVER (PARTITION BY department) AS dept_count,
SUM(salary) OVER (PARTITION BY department) AS dept_total,
AVG(salary) OVER (PARTITION BY department) AS dept_avg,
MAX(salary) OVER (PARTITION BY department) AS dept_max,
MIN(salary) OVER (PARTITION BY department) AS dept_min
FROM employees;| name | dept | salary | count | total | avg | max | min |
| ------ | ---- | ------ | ----- | ----- | ---- | ---- | ---- |
| 김철수 | 개발 | 5000 | 3 | 12000 | 4000 | 5000 | 3000 |
| 이영희 | 개발 | 4000 | 3 | 12000 | 4000 | 5000 | 3000 |
| 박지민 | 개발 | 3000 | 3 | 12000 | 4000 | 5000 | 3000 |
| 정유진 | 기획 | 6000 | 2 | 11000 | 5500 | 6000 | 5000 |
| 한도현 | 기획 | 5000 | 2 | 11000 | 5500 | 6000 | 5000 |비율 계산
윈도우 함수로 부서 내 비율, 전체 비율 등을 쉽게 계산할 수 있습니다.
SELECT name, department, salary,
-- 부서 내 급여 비율
ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 1)
AS dept_pct,
-- 전체 급여 비율
ROUND(salary * 100.0 / SUM(salary) OVER (), 1)
AS total_pct
FROM employees;누적 합계 (Running Total)
ORDER BY를 추가하면 윈도우 시작부터 현재 행까지의 누적 계산이 됩니다.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total,
AVG(amount) OVER (ORDER BY order_date) AS running_avg
FROM daily_sales;| order_date | amount | running_total | running_avg |
| ---------- | ------ | ------------- | ----------- |
| 2024-01-01 | 1000 | 1000 | 1000.0 |
| 2024-01-02 | 1500 | 2500 | 1250.0 |
| 2024-01-03 | 800 | 3300 | 1100.0 |
| 2024-01-04 | 2000 | 5300 | 1325.0 |실무 패턴: 그룹별 TOP-N
카테고리별 가장 비싼 상품 3개를 구하는 전형적인 패턴입니다.
SELECT * FROM (
SELECT p.*,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC
) AS rn
FROM products p
) ranked
WHERE rn <= 3;이 패턴은 다양한 비즈니스 시나리오에 적용됩니다.
-- 부서별 최근 입사 3명
SELECT * FROM (
SELECT emp.*,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY hire_date DESC
) AS rn
FROM employees emp
) t WHERE rn <= 3;
-- 고객별 최대 금액 주문 1건
SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY total_amount DESC
) AS rn
FROM orders o
) t WHERE rn = 1;ROW_NUMBER vs RANK의 차이 (TOP-N에서)
동일한 값이 있을 때 결과가 달라질 수 있습니다.
-- ROW_NUMBER: 정확히 2개만 반환 (타이가 있어도)
SELECT * FROM (
SELECT p.*, ROW_NUMBER() OVER (ORDER BY price DESC) AS rn
FROM products p
) t WHERE rn <= 2;
-- 결과: 2행 (어떤 행이 선택될지 비결정적)
-- RANK: 동점자 모두 포함 (3개 이상 반환 가능)
SELECT * FROM (
SELECT p.*, RANK() OVER (ORDER BY price DESC) AS rnk
FROM products p
) t WHERE rnk <= 2;
-- 결과: 공동 2위가 있으면 3행 이상정확히 N개가 필요하면 ROW_NUMBER, 동점자를 모두 포함하려면 RANK를 사용합니다.
윈도우 함수의 실행 순서
윈도우 함수는 SQL 실행 순서에서 SELECT 절 처리 시점에 실행되며, WHERE나 GROUP BY보다 늦게 실행됩니다.
1. FROM / JOIN ← 테이블 결합
2. WHERE ← 행 필터링
3. GROUP BY ← 그룹화
4. HAVING ← 그룹 필터링
5. SELECT ← 윈도우 함수 실행 ★
6. DISTINCT
7. ORDER BY
8. LIMIT/OFFSET 이 때문에 윈도우 함수의 결과를 WHERE 절에서 직접 사용할 수 없습니다.
-- 오류! WHERE에서 윈도우 함수 사용 불가
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 3; -- ERROR!
-- 해결: 서브쿼리 또는 CTE 사용
SELECT * FROM (
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
) t
WHERE rn <= 3; -- 정상 동작!
-- CTE 사용
WITH ranked AS (
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;같은 윈도우를 재사용: WINDOW 절
같은 OVER 절을 여러 번 반복하면 코드가 길어집니다. WINDOW 절로 이름을 붙여 재사용할 수 있습니다(PostgreSQL, MySQL 8.0+).
-- 중복되는 OVER 절
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS running
FROM employees;
-- WINDOW 절로 정리
SELECT name, department, salary,
ROW_NUMBER() OVER w AS rn,
RANK() OVER w AS rnk,
SUM(salary) OVER w AS running
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);흔한 실수와 주의사항
실수 1: ORDER BY 누락으로 비결정적 결과
-- 위험: ORDER BY가 없으면 번호가 매번 달라질 수 있음
SELECT name, ROW_NUMBER() OVER () AS rn FROM employees;
-- 올바른 사용: 반드시 ORDER BY 지정
SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn FROM employees;실수 2: PARTITION BY와 GROUP BY 혼동
-- GROUP BY 먼저 실행되고, 그 결과에 윈도우 함수 적용
SELECT department, COUNT(*) AS cnt,
RANK() OVER (ORDER BY COUNT(*) DESC) AS popularity_rank
FROM employees
GROUP BY department;
-- 윈도우 함수는 GROUP BY 이후의 결과 행에 대해 동작실수 3: 집계 함수 + ORDER BY의 의미 오해
-- SUM(...) OVER (PARTITION BY dept)
-- → 부서별 총합 (모든 행에 같은 값)
-- SUM(...) OVER (PARTITION BY dept ORDER BY salary)
-- → 부서 내에서 salary 순으로 누적합 (행마다 다른 값!)
-- 누적합이 아닌 전체합을 원하면 ORDER BY를 제거하거나
-- 프레임을 명시적으로 지정
SELECT name, salary,
SUM(salary) OVER (
PARTITION BY department
ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS dept_total
FROM employees;실수 4: NULL 정렬 순서
ORDER BY에서 NULL이 있으면 DBMS마다 위치가 다릅니다.
* PostgreSQL: NULL이 마지막 (ASC)
* Oracle: NULL이 마지막 (ASC)
* MySQL: NULL이 처음 (ASC)
* SQL Server: NULL이 처음 (ASC)
명시적으로 지정하려면:
ORDER BY salary NULLS FIRST
ORDER BY salary NULLS LAST윈도우 함수와 성능
윈도우 함수는 내부적으로 정렬이 필요하므로 대량 데이터에서 성능에 영향을 줄 수 있습니다.
┌────────────────────────────────────────────────────────┐
│ 1. ORDER BY 컬럼에 인덱스 생성 │
│ 특히 PARTITION BY + ORDER BY 조합 컬럼에 복합 인덱스│
├────────────────────────────────────────────────────────┤
│ 2. 같은 PARTITION BY + ORDER BY는 WINDOW 절로 통합 │
│ DBMS가 정렬을 한 번만 수행하도록 유도 │
├────────────────────────────────────────────────────────┤
│ 3. 불필요한 파티션 피하기 │
│ WHERE로 먼저 필터링한 후 윈도우 함수 적용 │
├────────────────────────────────────────────────────────┤
│ 4. EXPLAIN으로 실행 계획 확인 │
│ Sort, WindowAgg 노드의 비용 확인 │
└────────────────────────────────────────────────────────┘-- 윈도우 함수 최적화를 위한 인덱스
CREATE INDEX idx_emp_dept_salary
ON employees (department, salary DESC);
-- 이 인덱스로 아래 쿼리에서 정렬 비용 절감
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees;핵심 정리
┌────────────────────────────────────────────────────────┐
│ 윈도우 함수 = 행을 유지하면서 집계/순위/참조 계산 │
├────────────────────────────────────────────────────────┤
│ OVER 절 구성 │
│ PARTITION BY: 그룹 분할 (생략 시 전체) │
│ ORDER BY: 윈도우 내 정렬 (집계 시 누적 계산 유발) │
├────────────────────────────────────────────────────────┤
│ 순위 함수 │
│ ROW_NUMBER: 고유 번호 (1,2,3,4) │
│ RANK: 동점 후 건너뜀 (1,2,2,4) │
│ DENSE_RANK: 동점 후 연속 (1,2,2,3) │
│ NTILE(N): N개 그룹으로 분할 │
├────────────────────────────────────────────────────────┤
│ 집계 함수 + OVER │
│ PARTITION BY만: 그룹별 전체 통계 │
│ + ORDER BY: 누적 계산 (Running Total/Avg) │
├────────────────────────────────────────────────────────┤
│ TOP-N 패턴 │
│ 서브쿼리 + ROW_NUMBER/RANK + WHERE rn <= N │
├────────────────────────────────────────────────────────┤
│ 주의사항 │
│ WHERE에서 윈도우 함수 사용 불가 → 서브쿼리/CTE 필요 │
│ ORDER BY가 집계 윈도우의 의미를 바꿈 (누적) │
│ ROW_NUMBER는 타이에서 비결정적 → ORDER BY 완전 지정 │
└────────────────────────────────────────────────────────┘다음 절에서는 이전/다음 행을 참조하는 LAG/LEAD, 프레임 절을 활용한 이동 평균 등 윈도우 함수 심화를 다루겠습니다.