SQL 완전 정리 ⑥ 윈도 함수
윈도 함수(window function)는 행을 줄이지 않고 다른 행들의 값을 끌어와 계산한다. "각 사원 옆에 부서 합계", "급여 순위", "입사 순서상 직전 사람의 급여", "누적 합계" — GROUP BY 로는 행이 접혀서, 상관 서브쿼리로는 쿼리가 길어져서 불편했던 계산을
OVER (…)한 줄로 해결한다. 이 편은 OVER 절의 세 부품(PARTITION BY · ORDER BY · 프레임)을 하나씩 분해하고, 순위·집계·탐색 함수를 차례로 다룬 뒤, 기본 프레임이 만드는 두 가지 함정과 Top-N 패턴으로 마무리한다. 본문의 모든 쿼리와 결과 표는 sqlite3 (3.25+ 가 윈도 함수 지원)와 PostgreSQL 로 실행해 맞췄다.
0. 이 편에서 쓰는 테이블
사원 (이 편에서 쓰는 열만)
| eno | ename | dno | job | sal | hiredate |
|---|---|---|---|---|---|
| 1 | 김하늘 | 10 | 팀장 | 500 | 2019-03-01 |
| 2 | 이도윤 | 10 | 대리 | 350 | 2021-07-15 |
| 3 | 박서준 | 10 | 사원 | 280 | 2023-01-02 |
| 4 | 최지우 | 20 | 팀장 | 450 | 2018-11-20 |
| 5 | 정민재 | 20 | 사원 | 300 | 2022-05-09 |
| 6 | 한유나 | 20 | 사원 | 300 | 2024-02-01 |
| 7 | 오세린 | NULL | 사원 | 250 | 2025-01-06 |
정민재·한유나의 급여가 둘 다 300 — 이 동점이 순위 함수와 프레임 함정의 주인공이다. 급여 내림차순 전체 순서는 500 › 450 › 350 › 300 = 300 › 280 › 250.
1. GROUP BY 와 무엇이 다른가 — 행이 줄지 않는다
같은 "부서별 합계"를 두 방식으로 구해 본다.
SELECT dno, COUNT(*) AS cnt, SUM(sal) AS 합계
FROM 사원
WHERE dno IS NOT NULL
GROUP BY dno
ORDER BY dno;
| dno | cnt | 합계 |
|---|---|---|
| 10 | 3 | 1130 |
| 20 | 3 | 1050 |
6행이 2행으로 접혔다. 그룹마다 한 행만 남으므로 ename 처럼 그룹 안에서 값이 여러 개인 열은 더 이상 SELECT 할 수 없다.
SELECT ename, dno, sal,
SUM(sal) OVER (PARTITION BY dno) AS 부서합계,
COUNT(*) OVER (PARTITION BY dno) AS 인원
FROM 사원
WHERE dno IS NOT NULL
ORDER BY eno;
| ename | dno | sal | 부서합계 | 인원 |
|---|---|---|---|---|
| 김하늘 | 10 | 500 | 1130 | 3 |
| 이도윤 | 10 | 350 | 1130 | 3 |
| 박서준 | 10 | 280 | 1130 | 3 |
| 최지우 | 20 | 450 | 1050 | 3 |
| 정민재 | 20 | 300 | 1050 | 3 |
| 한유나 | 20 | 300 | 1050 | 3 |
6행이 그대로 6행이다. 각 행이 자기가 속한 부서(창, window)를 내다보고 그 합계를 자기 행 옆에 적는다. "창문 너머로 같은 그룹의 행들을 본다"는 것이 윈도라는 이름의 뜻이다.
| GROUP BY + 집계 | 집계 함수 + OVER | |
|---|---|---|
| 결과 행 수 | 그룹 수로 줄어듦 | 입력 행 수 그대로 |
| 개별 열(ename) | 선택 불가 | 선택 가능 |
| 그룹 기준 | GROUP BY 열 | OVER (PARTITION BY 열) |
| 한 쿼리에 여러 기준 | 불가 (한 번의 GROUP BY) | 가능 (OVER 마다 다른 PARTITION) |
1.1 논리적 처리 순서에서의 위치
FROM → WHERE → GROUP BY → HAVING → [윈도 함수] → SELECT 목록 → DISTINCT → ORDER BY → LIMIT
윈도 함수는 WHERE·GROUP BY·HAVING 이 다 끝난 행들 위에서 계산된다. 여기서 세 가지 규칙이 나온다.
- WHERE 가 먼저 행을 지우면 윈도는 남은 행만 본다.
WHERE ename = '박서준'뒤에DENSE_RANK() OVER (ORDER BY sal DESC)를 쓰면 박서준 혼자라서 순위가 1 이다. - WHERE·GROUP BY·HAVING 안에서 윈도 함수를 쓸 수 없다(아직 계산 전이므로) → §8 의 인라인 뷰 패턴.
- GROUP BY 결과에 윈도를 씌울 수 있다 — 그룹이 먼저 만들어지니
RANK() OVER (ORDER BY SUM(sal) DESC)가 가능하다.
SELECT dno, SUM(sal) AS 합계,
RANK() OVER (ORDER BY SUM(sal) DESC) AS 순위
FROM 사원
GROUP BY dno
ORDER BY 순위;
| dno | 합계 | 순위 |
|---|---|---|
| 10 | 1130 | 1 |
| 20 | 1050 | 2 |
| NULL | 250 | 3 |
2. OVER 절의 구조
함수명([인수]) OVER (
[PARTITION BY 열, …] -- ① 창을 나누는 기준
[ORDER BY 열 [ASC|DESC], …] -- ② 창 안의 순서
[{ROWS | RANGE} 프레임] -- ③ 계산에 쓸 행의 범위
)
| 부품 | 생략하면 | 역할 |
|---|---|---|
| ① PARTITION BY | 전체 결과가 하나의 파티션 | GROUP BY 처럼 행을 묶되, 접지는 않는다 |
| ② ORDER BY | 파티션 안의 순서가 없음 | 순위·LAG/LEAD 의 기준, 누적 계산을 켜는 스위치 |
| ③ 프레임 | ORDER BY 유무에 따라 기본값이 달라짐(§6) | 현재 행 기준 "어디서부터 어디까지" |
세 부품이 어떻게 겹쳐지는지 한 그림으로:
PARTITION BY dno ORDER BY sal DESC 프레임: 처음 ~ 현재 행
┌─ 파티션 dno=10 ──────┐ 김하늘 500 ← 1번째 [김하늘] → 500
│ 김하늘 이도윤 박서준 │ ───▶ 이도윤 350 ← 2번째 ───▶ [김하늘, 이도윤] → 850
└──────────────────────┘ 박서준 280 ← 3번째 [김하늘, 이도윤, 박서준] → 1130
┌─ 파티션 dno=20 ──────┐
│ 최지우 정민재 한유나 │ (파티션이 바뀌면 순서·프레임이 처음부터 다시 시작)
└──────────────────────┘
OVER 의 ORDER BY 와 쿼리 끝의 ORDER BY 는 별개다. OVER 안의 것은 "계산 순서"이고, 쿼리 끝의 것은 "출력 순서"다. 순위를 급여순으로 매기고 출력은 사원번호순으로 할 수 있다. 출력 순서를 보장하려면 쿼리 끝 ORDER BY 를 따로 써야 한다.
같은 OVER 를 여러 번 쓰면 WINDOW 절로 이름을 붙일 수 있다(PostgreSQL·MySQL 8·SQLite, Oracle 21c+).
SELECT ename, job, sal,
ROW_NUMBER() OVER w AS rn,
SUM(sal) OVER w AS 누적
FROM 사원
WHERE dno = 20
WINDOW w AS (ORDER BY sal DESC, eno)
ORDER BY rn;
| ename | job | sal | rn | 누적 |
|---|---|---|---|---|
| 최지우 | 팀장 | 450 | 1 | 450 |
| 정민재 | 사원 | 300 | 2 | 750 |
| 한유나 | 사원 | 300 | 3 | 1050 |
3. 순위 함수 — RANK · DENSE_RANK · ROW_NUMBER
RANK() OVER ([PARTITION BY …] ORDER BY …)
DENSE_RANK() OVER ([PARTITION BY …] ORDER BY …)
ROW_NUMBER() OVER ([PARTITION BY …] ORDER BY …)
세 함수 모두 인수가 없고 OVER 안의 ORDER BY 가 필수다(순서 없이 순위를 매길 수 없으므로 — ROW_NUMBER 는 문법상 생략을 허용하는 엔진도 있지만 번호가 임의로 붙는다).
SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY sal DESC) AS drnk,
ROW_NUMBER() OVER (ORDER BY sal DESC, eno) AS rn
FROM 사원
ORDER BY sal DESC, eno;
| ename | sal | RANK | DENSE_RANK | ROW_NUMBER |
|---|---|---|---|---|
| 김하늘 | 500 | 1 | 1 | 1 |
| 최지우 | 450 | 2 | 2 | 2 |
| 이도윤 | 350 | 3 | 3 | 3 |
| 정민재 | 300 | 4 | 4 | 4 |
| 한유나 | 300 | 4 | 4 | 5 |
| 박서준 | 280 | 6 | 5 | 6 |
| 오세린 | 250 | 7 | 6 | 7 |
3.1 동점 처리 비교
| 함수 | 동점끼리 | 동점 다음 순위 | 계산 원리 | 마지막 값 |
|---|---|---|---|---|
RANK | 같은 순위 | 건너뜀 (4, 4 → 6) | 나보다 앞선 행 수 + 1 | 항상 ≤ 행 수 |
DENSE_RANK | 같은 순위 | 연속 (4, 4 → 5) | 나보다 앞선 서로 다른 값 수 + 1 | = 서로 다른 값의 개수 |
ROW_NUMBER | 다른 번호 | 연속 (4, 5 → 6) | 1부터 하나씩 | = 행 수 |
올림픽 메달표로 생각하면 쉽다. 금메달 공동 2명이면 다음은 동메달(RANK, 은메달 없음)이다. DENSE_RANK 는 "몇 번째 급여 수준인가", ROW_NUMBER 는 "줄 서는 순번"이다.
RANK 는 ⑤편 상관 서브쿼리와 같다 —
(SELECT COUNT(*) FROM 사원 e2 WHERE e2.sal > e1.sal) + 1이 정확히 RANK 를 만든다. DENSE_RANK 는COUNT(DISTINCT e2.sal)로 바꾸면 된다. 윈도 함수는 이것을 한 번의 정렬로 계산한다.
3.2 ROW_NUMBER 와 동점 — 결과가 흔들린다
동점 행끼리 누가 먼저 번호를 받는지는 정해져 있지 않다. 같은 쿼리를 두 엔진에서 돌린 실제 결과:
SELECT ename, sal, ROW_NUMBER() OVER (ORDER BY sal) AS rn FROM 사원;
| ename | sal | SQLite rn | PostgreSQL rn |
|---|---|---|---|
| 정민재 | 300 | 3 | 4 |
| 한유나 | 300 | 4 | 3 |
(나머지 행은 같다.) 둘 다 맞는 결과다. 재현 가능한 번호가 필요하면 ORDER BY sal, eno 처럼 유일한
열로 동점을 끊는다(tie-breaker). 이 편의 ROW_NUMBER 예제가 전부 , eno 를 붙인 이유다.
3.3 PARTITION BY 와 함께 — 그룹 안 순위
SELECT ename, dno, sal,
RANK() OVER (PARTITION BY dno ORDER BY sal DESC) AS 부서내순위
FROM 사원
WHERE dno IS NOT NULL
ORDER BY dno, 부서내순위, eno;
| ename | dno | sal | 부서내순위 |
|---|---|---|---|
| 김하늘 | 10 | 500 | 1 |
| 이도윤 | 10 | 350 | 2 |
| 박서준 | 10 | 280 | 3 |
| 최지우 | 20 | 450 | 1 |
| 정민재 | 20 | 300 | 2 |
| 한유나 | 20 | 300 | 2 |
파티션이 바뀌면 순위가 1부터 다시 시작한다. 한 SELECT 안에 기준이 다른 순위를 나란히 둘 수도 있다.
SELECT ename, sal,
RANK() OVER (ORDER BY sal DESC) AS 전체순위,
RANK() OVER (PARTITION BY dno ORDER BY sal DESC) AS 부서순위
FROM 사원
ORDER BY 전체순위, eno;
| ename | sal | 전체순위 | 부서순위 |
|---|---|---|---|
| 김하늘 | 500 | 1 | 1 |
| 최지우 | 450 | 2 | 1 |
| 이도윤 | 350 | 3 | 2 |
| 정민재 | 300 | 4 | 2 |
| 한유나 | 300 | 4 | 2 |
| 박서준 | 280 | 6 | 3 |
| 오세린 | 250 | 7 | 1 |
오세린은 dno 가 NULL 인 혼자만의 파티션에서 1등이다 — PARTITION BY 는 GROUP BY 처럼 NULL 들을 하나의 그룹으로 묶는다.
3.4 NTILE(n) — n 개 그룹으로 나누기
NTILE(n) OVER ([PARTITION BY …] ORDER BY …)
정렬된 행을 최대한 균등하게 n 개 묶음으로 나눠 1~n 번호를 매긴다. 나누어떨어지지 않으면 앞쪽 묶음부터 한 행씩 더 받는다.
SELECT ename, sal,
NTILE(3) OVER (ORDER BY sal DESC, eno) AS n3,
NTILE(4) OVER (ORDER BY sal DESC, eno) AS n4,
NTILE(2) OVER (ORDER BY sal DESC, eno) AS n2
FROM 사원
ORDER BY sal DESC, eno;
| ename | sal | NTILE(3) | NTILE(4) | NTILE(2) |
|---|---|---|---|---|
| 김하늘 | 500 | 1 | 1 | 1 |
| 최지우 | 450 | 1 | 1 | 1 |
| 이도윤 | 350 | 1 | 2 | 1 |
| 정민재 | 300 | 2 | 2 | 1 |
| 한유나 | 300 | 2 | 3 | 2 |
| 박서준 | 280 | 3 | 3 | 2 |
| 오세린 | 250 | 3 | 4 | 2 |
| n | 7 ÷ n | 묶음 크기 |
|---|---|---|
| 3 | 몫 2, 나머지 1 | 3, 2, 2 |
| 4 | 몫 1, 나머지 3 | 2, 2, 2, 1 |
| 2 | 몫 3, 나머지 1 | 4, 3 |
NTILE 은 값이 아니라 행 개수로 자른다. 그래서 NTILE(4) 에서 동점인 정민재(2)와 한유나(3)가 다른 묶음에 들어갔다. "상위 25%" 같은 등급 나누기에 쓰지만 동점을 갈라놓을 수 있다는 점을 기억한다.
4. 집계 윈도 함수 — SUM · AVG · COUNT · MAX · MIN + OVER
집계함수(열) OVER ([PARTITION BY …] [ORDER BY … [프레임]])
평소의 집계 함수에 OVER 를 붙이면 윈도 함수가 된다. 집계의 NULL 규칙(COUNT(*) 는 행 수, COUNT(열)·SUM·AVG 는 NULL 무시)은 그대로다. ORDER BY 가 있느냐 없느냐에 따라 의미가 완전히 바뀐다.
4.1 ORDER BY 없음 → 파티션 전체 집계
SELECT ename, dno, sal,
ROUND(100.0 * sal / SUM(sal) OVER (PARTITION BY dno), 1) AS 부서내비율
FROM 사원
WHERE dno IS NOT NULL
ORDER BY eno;
| ename | dno | sal | 부서내비율 |
|---|---|---|---|
| 김하늘 | 10 | 500 | 44.2 |
| 이도윤 | 10 | 350 | 31.0 |
| 박서준 | 10 | 280 | 24.8 |
| 최지우 | 20 | 450 | 42.9 |
| 정민재 | 20 | 300 | 28.6 |
| 한유나 | 20 | 300 | 28.6 |
OVER () 처럼 괄호를 비우면 결과 전체가 하나의 창이다. 전체 급여 합계 2430 대비 비율:
SELECT ename, sal, ROUND(100.0 * sal / SUM(sal) OVER (), 1) AS 전체비율
FROM 사원 ORDER BY eno;
| ename | sal | 전체비율 |
|---|---|---|
| 김하늘 | 500 | 20.6 |
| 이도윤 | 350 | 14.4 |
| 박서준 | 280 | 11.5 |
| 최지우 | 450 | 18.5 |
| 정민재 | 300 | 12.3 |
| 한유나 | 300 | 12.3 |
| 오세린 | 250 | 10.3 |
100.0 *을 앞에 둔 이유 — 정수끼리 나누면 PostgreSQL·SQLite·SQL Server 는 정수 나눗셈(500/2430 = 0)을 한다. Oracle·MySQL 은 소수로 나눈다. 한쪽을 실수로 만들어 엔진 차이를 없앤다.
MAX(sal) OVER (PARTITION BY dno) 로 "부서 최고 급여"를 각 행에 붙이는 것도 같은 원리다.
4.2 ORDER BY 있음 → 누적(running) 집계
SELECT ename, dno, sal,
SUM(sal) OVER (PARTITION BY dno ORDER BY eno) AS 부서누적
FROM 사원
WHERE dno IS NOT NULL
ORDER BY dno, eno;
| ename | dno | sal | 부서누적 | 계산 |
|---|---|---|---|---|
| 김하늘 | 10 | 500 | 500 | 500 |
| 이도윤 | 10 | 350 | 850 | 500+350 |
| 박서준 | 10 | 280 | 1130 | 500+350+280 |
| 최지우 | 20 | 450 | 450 | 파티션 바뀜 → 다시 시작 |
| 정민재 | 20 | 300 | 750 | 450+300 |
| 한유나 | 20 | 300 | 1050 | 450+300+300 |
ORDER BY 를 넣는 순간 기본 프레임이 "파티션 전체"에서 "처음 ~ 현재 행" 으로 바뀌기 때문이다(§6).
같은 원리로 MAX(sal) OVER (ORDER BY hiredate) 는 "그 시점까지의 최고 급여"가 된다.
SELECT ename, hiredate, sal, MAX(sal) OVER (ORDER BY hiredate) AS 그때까지최고
FROM 사원 ORDER BY hiredate;
| ename | hiredate | sal | 그때까지최고 |
|---|---|---|---|
| 최지우 | 2018-11-20 | 450 | 450 |
| 김하늘 | 2019-03-01 | 500 | 500 |
| 이도윤 | 2021-07-15 | 350 | 500 |
| 정민재 | 2022-05-09 | 300 | 500 |
| 박서준 | 2023-01-02 | 280 | 500 |
| 한유나 | 2024-02-01 | 300 | 500 |
| 오세린 | 2025-01-06 | 250 | 500 |
4.3 함정 — "합계를 구하려고 ORDER BY 를 넣었더니 합계가 아니다"
SELECT ename, dno, sal,
SUM(sal) OVER (PARTITION BY dno) AS 합계,
SUM(sal) OVER (PARTITION BY dno ORDER BY sal) AS 정렬추가
FROM 사원
WHERE dno IS NOT NULL
ORDER BY dno, sal, eno;
| ename | dno | sal | 합계 | 정렬추가 |
|---|---|---|---|---|
| 박서준 | 10 | 280 | 1130 | 280 |
| 이도윤 | 10 | 350 | 1130 | 630 |
| 김하늘 | 10 | 500 | 1130 | 1130 |
| 정민재 | 20 | 300 | 1050 | 600 |
| 한유나 | 20 | 300 | 1050 | 600 |
| 최지우 | 20 | 450 | 1050 | 1050 |
ORDER BY 하나 때문에 "부서 합계"가 "누적 합계"로 변했다. 그리고 20번 부서의 300 두 명은 둘 다 600 이다 — 이것이 다음 절의 RANGE 함정이다.
5. 프레임 — 현재 행 기준 "어디서부터 어디까지"
{ROWS | RANGE} BETWEEN 시작 AND 끝
시작/끝: UNBOUNDED PRECEDING -- 파티션의 첫 행
n PRECEDING -- n 앞
CURRENT ROW -- 현재 행
n FOLLOWING -- n 뒤
UNBOUNDED FOLLOWING -- 파티션의 마지막 행
축약형: ROWS UNBOUNDED PRECEDING ≡ ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
| 단위 | 범위를 세는 기준 | 동점(같은 ORDER BY 값) |
|---|---|---|
ROWS | 물리적 행 개수 | 한 행씩 따로 |
RANGE | ORDER BY 값의 범위 | 같은 값이면 한 덩어리(peer)로 함께 포함 |
5.1 ROWS vs RANGE — 동점에서 갈린다
SELECT ename, sal,
SUM(sal) OVER (ORDER BY sal DESC) AS 기본프레임,
SUM(sal) OVER (ORDER BY sal DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows누적,
SUM(sal) OVER (ORDER BY sal DESC, eno) AS 동점해소
FROM 사원
ORDER BY sal DESC, eno;
| ename | sal | 기본프레임 (RANGE) | ROWS 누적 | 동점해소 (ORDER BY sal, eno) |
|---|---|---|---|---|
| 김하늘 | 500 | 500 | 500 | 500 |
| 최지우 | 450 | 950 | 950 | 950 |
| 이도윤 | 350 | 1300 | 1300 | 1300 |
| 정민재 | 300 | 1900 | 1600 | 1600 |
| 한유나 | 300 | 1900 | 1900 | 1900 |
| 박서준 | 280 | 2180 | 2180 | 2180 |
| 오세린 | 250 | 2430 | 2430 | 2430 |
정민재 행을 계산할 때 프레임에 무엇이 들어가는지 펼쳐 보면:
| 프레임 | 정민재(300) 행의 프레임 | 합계 |
|---|---|---|
RANGE … CURRENT ROW | 500, 450, 350, 300(정민재), 300(한유나) — "현재 행의 값 300 까지"이므로 같은 값 한유나도 포함 | 1900 |
ROWS … CURRENT ROW | 500, 450, 350, 300(정민재) — 물리적으로 현재 행까지 | 1600 |
해결은 두 가지 — 프레임을 ROWS 로 명시하거나, ORDER BY 에 유일한 열을 추가해 동점 자체를 없앤다
(셋째 열). 다만 동점이 있는 상태에서 ROWS 만 쓰면 정민재·한유나 중 누가 먼저 더해질지는 §3.2 처럼
엔진 마음이다. 실제로 COUNT(*) OVER (ORDER BY sal ROWS UNBOUNDED PRECEDING) 은 SQLite 에서 정민재 3·
한유나 4, PostgreSQL 에서 정민재 4·한유나 3 이 나왔다. 동점은 유일 열로 끊는 것이 근본 해결이다.
5.2 기본 프레임 규칙
| OVER 안의 모양 | 기본 프레임 | 결과 |
|---|---|---|
OVER () 또는 OVER (PARTITION BY …) | 파티션 전체 | 그룹 합계·평균 |
OVER ([PARTITION BY …] ORDER BY …) | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | 누적, 동점은 함께 |
표준 SQL 이 정한 규칙이며 Oracle·PostgreSQL·MySQL·SQLite·SQL Server 가 모두 따른다. 이 두 줄이 §4.3 과 §7.3 두 함정의 원인이다.
5.3 프레임 응용
이동 평균 — 앞 1행 · 현재 · 뒤 1행 (사원번호 순):
SELECT ename, sal,
ROUND(AVG(sal) OVER (ORDER BY eno ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1) AS 이동평균
FROM 사원 ORDER BY eno;
| ename | sal | 프레임 | 이동평균 |
|---|---|---|---|
| 김하늘 | 500 | 500, 350 (앞 행 없음) | 425.0 |
| 이도윤 | 350 | 500, 350, 280 | 376.7 |
| 박서준 | 280 | 350, 280, 450 | 360.0 |
| 최지우 | 450 | 280, 450, 300 | 343.3 |
| 정민재 | 300 | 450, 300, 300 | 350.0 |
| 한유나 | 300 | 300, 300, 250 | 283.3 |
| 오세린 | 250 | 300, 250 (뒤 행 없음) | 275.0 |
파티션 경계 밖은 그냥 비어 있는 것으로 처리한다(첫 행의 평균은 2개로 나눈다).
역누적 — 현재 행부터 끝까지:
SELECT ename, sal,
SUM(sal) OVER (ORDER BY sal DESC, eno
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 역누적
FROM 사원 ORDER BY sal DESC, eno;
| ename | sal | 역누적 |
|---|---|---|
| 김하늘 | 500 | 2430 |
| 최지우 | 450 | 1930 |
| 이도윤 | 350 | 1480 |
| 정민재 | 300 | 1130 |
| 한유나 | 300 | 830 |
| 박서준 | 280 | 530 |
| 오세린 | 250 | 250 |
값 범위 RANGE — RANGE BETWEEN 50 PRECEDING AND CURRENT ROW 는 "현재 급여 −50 ~ 현재 급여" 범위의
행을 모두 더한다(행 개수가 아니라 값의 거리).
SELECT ename, sal,
SUM(sal) OVER (ORDER BY sal RANGE BETWEEN 50 PRECEDING AND CURRENT ROW) AS 범위50
FROM 사원 ORDER BY sal, eno;
| ename | sal | 범위 | 포함되는 급여 | 범위50 |
|---|---|---|---|---|
| 오세린 | 250 | 200~250 | 250 | 250 |
| 박서준 | 280 | 230~280 | 250, 280 | 530 |
| 정민재 | 300 | 250~300 | 250, 280, 300, 300 | 1130 |
| 한유나 | 300 | 250~300 | 250, 280, 300, 300 | 1130 |
| 이도윤 | 350 | 300~350 | 300, 300, 350 | 950 |
| 최지우 | 450 | 400~450 | 450 | 450 |
| 김하늘 | 500 | 450~500 | 450, 500 | 950 |
(숫자 오프셋 RANGE 는 Oracle·MySQL 8·PostgreSQL 11+·SQLite 3.28+ 지원. ORDER BY 열이 하나의 숫자·날짜일
때만 가능하다. 참고로 PostgreSQL·SQLite 에는 "동점 덩어리 n 개"를 세는 세 번째 단위 GROUPS 도 있다.)
6. 탐색 함수 — LAG · LEAD · FIRST_VALUE · LAST_VALUE
LAG(열 [, n [, 기본값]]) OVER ([PARTITION BY …] ORDER BY …) -- n 행 앞의 값 (기본 n=1)
LEAD(열 [, n [, 기본값]]) OVER ([PARTITION BY …] ORDER BY …) -- n 행 뒤의 값
FIRST_VALUE(열) OVER (… ORDER BY … [프레임]) -- 프레임의 첫 값
LAST_VALUE(열) OVER (… ORDER BY … [프레임]) -- 프레임의 마지막 값
6.1 LAG / LEAD — 이전·다음 행
입사일 순으로 줄 세우고 직전·직후 입사자의 급여를 가져온다.
SELECT ename, hiredate, sal,
LAG(sal) OVER (ORDER BY hiredate) AS 이전,
LEAD(sal) OVER (ORDER BY hiredate) AS 다음,
sal - LAG(sal) OVER (ORDER BY hiredate) AS 증감
FROM 사원
ORDER BY hiredate;
| ename | hiredate | sal | 이전 | 다음 | 증감 |
|---|---|---|---|---|---|
| 최지우 | 2018-11-20 | 450 | NULL | 500 | NULL |
| 김하늘 | 2019-03-01 | 500 | 450 | 350 | 50 |
| 이도윤 | 2021-07-15 | 350 | 500 | 300 | -150 |
| 정민재 | 2022-05-09 | 300 | 350 | 280 | -50 |
| 박서준 | 2023-01-02 | 280 | 300 | 300 | -20 |
| 한유나 | 2024-02-01 | 300 | 280 | 250 | 20 |
| 오세린 | 2025-01-06 | 250 | 300 | NULL | -50 |
첫 행엔 이전 행이, 마지막 행엔 다음 행이 없어 NULL 이다. 그래서 증감의 첫 칸도 NULL(NULL 과의 연산은 NULL). "전월 대비 매출 증감", "직전 로그와의 시간 간격" 같은 계산을 셀프 조인 없이 한다.
n 과 기본값, PARTITION BY:
SELECT ename, dno, sal,
LAG(sal, 1, 0) OVER (PARTITION BY dno ORDER BY sal DESC, eno) AS 바로위급여,
LEAD(ename, 2) OVER (PARTITION BY dno ORDER BY sal DESC, eno) AS 두칸아래
FROM 사원
WHERE dno IS NOT NULL
ORDER BY dno, sal DESC, eno;
| ename | dno | sal | 바로위급여 | 두칸아래 |
|---|---|---|---|---|
| 김하늘 | 10 | 500 | 0 | 박서준 |
| 이도윤 | 10 | 350 | 500 | NULL |
| 박서준 | 10 | 280 | 350 | NULL |
| 최지우 | 20 | 450 | 0 | 한유나 |
| 정민재 | 20 | 300 | 450 | NULL |
| 한유나 | 20 | 300 | 300 | NULL |
- 세 번째 인수
0이 "앞 행이 없을 때" NULL 대신 들어갔다. - 파티션 경계를 넘지 않는다 — 최지우의 LAG 는 박서준(10번 부서 마지막)이 아니라 없음(0) 이다.
- LAG/LEAD 는 프레임을 무시한다. 항상 파티션 안에서 ORDER BY 기준 n 칸을 센다.
6.2 FIRST_VALUE — 기본 프레임으로도 잘 동작
SELECT ename, dno, sal,
FIRST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal DESC) AS 부서최고
FROM 사원 WHERE dno IS NOT NULL
ORDER BY dno, sal DESC, eno;
부서최고 열: 10번 부서 세 행 모두 김하늘, 20번 부서 세 행 모두 최지우. 기본 프레임이 "처음 ~ 현재"이므로 처음은 언제나 파티션의 첫 행이다 → 문제없다.
6.3 LAST_VALUE 함정 — 기본 프레임이면 "자기 자신"이 나온다
SELECT ename, dno, sal,
FIRST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal DESC) AS 최고,
LAST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal DESC) AS 기본프레임_LAST,
LAST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS 전체프레임_LAST
FROM 사원
WHERE dno IS NOT NULL
ORDER BY dno, sal DESC, eno;
| ename | dno | sal | 최고 | 기본프레임_LAST | 전체프레임_LAST |
|---|---|---|---|---|---|
| 김하늘 | 10 | 500 | 김하늘 | 김하늘 | 박서준 |
| 이도윤 | 10 | 350 | 김하늘 | 이도윤 | 박서준 |
| 박서준 | 10 | 280 | 김하늘 | 박서준 | 박서준 |
| 최지우 | 20 | 450 | 최지우 | 최지우 | 한유나 |
| 정민재 | 20 | 300 | 최지우 | 한유나 | 한유나 |
| 한유나 | 20 | 300 | 최지우 | 한유나 | 한유나 |
"부서 최저 급여자"를 원했는데 기본 프레임 열은 대부분 자기 자신이다. 기본 프레임의 끝이
CURRENT ROW 라서 프레임의 마지막 행 = 현재 행이기 때문이다. 정민재 행만 한유나가 나온 것은 RANGE
때문에 동점 한유나가 같은 덩어리로 프레임 끝에 들어갔기 때문이다(§5.1 과 같은 원리, 동점 순서에 따라
결과가 달라질 수 있음).
| 해결 | 쓰는 법 |
|---|---|
| ① 프레임을 끝까지 연다 | ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
| ② 정렬을 뒤집어 FIRST_VALUE | FIRST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal ASC, eno) |
②의 결과 — 10번 부서 모든 행에 박서준, 20번 부서 모든 행에 정민재(300 동점 중 eno 가 작은 쪽). FIRST_VALUE 쪽이 프레임을 몰라도 안전해서 실무에서 자주 쓴다.
Oracle·PostgreSQL 에는
NTH_VALUE(열, n)도 있다(프레임의 n 번째 값). 같은 LAST_VALUE 함정이 적용된다. Oracle 은FIRST_VALUE(열 IGNORE NULLS)처럼 NULL 을 건너뛰는 옵션도 지원한다.
7. 윈도 결과로 거르기 — WHERE 불가 → 인라인 뷰로 감싼다
7.1 왜 안 되나
SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS r
FROM 사원
WHERE RANK() OVER (ORDER BY sal DESC) <= 3; -- ✗
| 엔진 | 오류 |
|---|---|
| PostgreSQL | window functions are not allowed in WHERE |
| SQLite | misuse of window function RANK() |
| Oracle | ORA-30483: window functions are not allowed here |
별칭으로 WHERE r <= 3 을 써도 안 된다(SELECT 별칭은 WHERE 시점에 없다). §1.1 의 순서대로, WHERE 가 끝나야
윈도 함수가 계산되기 때문이다. WHERE 가 순위를 보고 행을 지우면 순위 자체가 바뀌는 순환이 생긴다.
HAVING 도 같은 이유로 불가다.
7.2 해결 — 한 겹 감싸면 순위가 "열"이 된다
SELECT ename, dno, sal, rn
FROM (SELECT ename, dno, sal,
ROW_NUMBER() OVER (PARTITION BY dno ORDER BY sal DESC, eno) AS rn
FROM 사원
WHERE dno IS NOT NULL) t
WHERE rn <= 2
ORDER BY dno, rn;
| ename | dno | sal | rn |
|---|---|---|---|
| 김하늘 | 10 | 500 | 1 |
| 이도윤 | 10 | 350 | 2 |
| 최지우 | 20 | 450 | 1 |
| 정민재 | 20 | 300 | 2 |
안쪽 쿼리가 끝나면 rn 은 인라인 뷰 t 의 평범한 열이다. 바깥 WHERE 가 그것을 거른다. CTE 로 써도 같다
(WITH t AS (…) SELECT … FROM t WHERE rn <= 2). 이것이 그룹별 Top-N 의 정석 패턴이다.
7.3 Top-N 에서 세 함수가 갈리는 지점
같은 "상위 N" 이라도 동점을 어떻게 볼지에 따라 행 수가 달라진다.
| 조건 | RANK | DENSE_RANK | ROW_NUMBER (sal DESC, eno) |
|---|---|---|---|
전체 <= 4 | 5행 (4위 동점 2명) | 5행 | 4행 (정민재까지) |
전체 <= 5 | 5행 (5위가 없음) | 6행 (박서준 = 5) | 5행 |
20번 부서 <= 2 | 3행 (최지우 + 300 두 명) | 3행 | 2행 |
SELECT ename, sal, r
FROM (SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) AS r FROM 사원) t
WHERE r <= 4
ORDER BY r, ename;
| ename | sal | r |
|---|---|---|
| 김하늘 | 500 | 1 |
| 최지우 | 450 | 2 |
| 이도윤 | 350 | 3 |
| 정민재 | 300 | 4 |
| 한유나 | 300 | 4 |
| 요구 | 함수 |
|---|---|
| "정확히 N 개" (게시판 페이지, 부서당 대표 1명) | ROW_NUMBER + 유일 tie-breaker |
| "N 위 안에 드는 사람 모두" (동점자 포함 시상) | RANK |
| "상위 N 개의 서로 다른 값에 해당하는 사람" (급여 상위 3단계) | DENSE_RANK |
그룹별 1명 뽑기 — 직급별 최고 급여자 한 명:
SELECT job, ename, sal
FROM (SELECT job, ename, sal,
ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal DESC, eno) AS rn
FROM 사원) t
WHERE rn = 1
ORDER BY job;
| job | ename | sal |
|---|---|---|
| 대리 | 이도윤 | 350 |
| 사원 | 정민재 | 300 |
| 팀장 | 김하늘 | 500 |
(사원 직급의 300 동점 중 eno 가 작은 정민재가 1번. RANK 로 바꾸면 한유나도 함께 나온다.)
엔진 차이 — Oracle 의
ROWNUM은 윈도 함수가 아니라 행이 결과에 들어오는 순서대로 붙는 번호라WHERE ROWNUM <= 3 ORDER BY sal DESC는 정렬 전 3행을 자른다(정렬하려면 인라인 뷰 안에서 정렬 후 바깥에서 ROWNUM). PostgreSQL·MySQL·SQLite 의LIMIT n, 표준·Oracle 12c+ 의FETCH FIRST n ROWS ONLY는 전체 Top-N 에는 간단하지만 그룹별 Top-N 은 못 한다 — 그때가 ROW_NUMBER 의 차례다. 또 일부 엔진(Snowflake·DuckDB·BigQuery 등)은 윈도 결과를 거르는QUALIFY절을 제공하지만 표준이 아니다.
8. 분포 함수 — PERCENT_RANK · CUME_DIST
PERCENT_RANK() OVER ([PARTITION BY …] ORDER BY …) -- (RANK − 1) / (행 수 − 1)
CUME_DIST() OVER ([PARTITION BY …] ORDER BY …) -- (현재 값 이하인 행 수) / 행 수
둘 다 0~1 사이의 상대 위치를 낸다.
SELECT ename, sal,
PERCENT_RANK() OVER (ORDER BY sal) AS pr,
CUME_DIST() OVER (ORDER BY sal) AS cd
FROM 사원
ORDER BY sal, eno;
| ename | sal | RANK | PERCENT_RANK | 계산 | CUME_DIST | 계산 |
|---|---|---|---|---|---|---|
| 오세린 | 250 | 1 | 0 | 0/6 | 0.143 | 1/7 |
| 박서준 | 280 | 2 | 0.167 | 1/6 | 0.286 | 2/7 |
| 정민재 | 300 | 3 | 0.333 | 2/6 | 0.571 | 4/7 |
| 한유나 | 300 | 3 | 0.333 | 2/6 | 0.571 | 4/7 |
| 이도윤 | 350 | 5 | 0.667 | 4/6 | 0.714 | 5/7 |
| 최지우 | 450 | 6 | 0.833 | 5/6 | 0.857 | 6/7 |
| 김하늘 | 500 | 7 | 1 | 6/6 | 1 | 7/7 |
| PERCENT_RANK | CUME_DIST | |
|---|---|---|
| 첫 행 | 항상 0 | 1/n (0 이 아님) |
| 마지막 행 | 1 | 항상 1 |
| 동점 | 같은 값 (RANK 기반) | 같은 값 — 동점 덩어리의 마지막 위치로 셈(300 두 명 모두 4/7) |
| 읽는 법 | "나보다 아래가 몇 %" | "나 이하가 몇 %" (누적 분포) |
(엔진이 보여 주는 소수 자릿수는 다르다 — SQLite 0.33333333333333332, PostgreSQL 0.3333333333333333. 값은 같다.)
9. 엔진 지원과 함수 분류 정리
| 엔진 | 윈도 함수 지원 |
|---|---|
| Oracle | 8i 부터 ("분석 함수 analytic function" 이라 부름) |
| SQL Server | 2005 부터 (프레임은 2012 부터) |
| PostgreSQL | 8.4 부터 |
| MySQL | 8.0 부터 (5.7 이하는 없음 — 사용자 변수로 흉내 냈다) |
| SQLite | 3.25 부터 |
| 분류 | 함수 | ORDER BY | 프레임 영향 |
|---|---|---|---|
| 순위 | RANK, DENSE_RANK, ROW_NUMBER, NTILE | 필수 | 없음 |
| 분포 | PERCENT_RANK, CUME_DIST | 필수 | 없음 |
| 탐색(오프셋) | LAG, LEAD | 필수 | 없음 (파티션 기준 n 칸) |
| 탐색(프레임) | FIRST_VALUE, LAST_VALUE, NTH_VALUE | 사실상 필수 | 있음 |
| 집계 | SUM, AVG, COUNT, MAX, MIN | 선택 (있으면 누적) | 있음 |
"정보처리기사·SQLD 계열의 분류 용어"로는 순위 함수, 집계 함수, 행 순서 함수(FIRST_VALUE·LAST_VALUE·LAG·LEAD), 비율 함수(PERCENT_RANK·CUME_DIST·NTILE, Oracle 의 RATIO_TO_REPORT)로 나누기도 한다.
확인 문제
1. (결과 쓰기) 다음 쿼리에서 박서준(280) 행의 a, b, c 값은?
SELECT ename,
RANK() OVER (ORDER BY sal DESC) AS a,
DENSE_RANK() OVER (ORDER BY sal DESC) AS b,
ROW_NUMBER() OVER (ORDER BY sal DESC, eno) AS c
FROM 사원;
2. (결과 쓰기) 다음 쿼리 결과의 d 값은?
SELECT ename, DENSE_RANK() OVER (ORDER BY sal DESC) AS d
FROM 사원
WHERE ename = '박서준';
3. (결과 쓰기) 20번 부서만 대상으로 할 때 각 행의 s 값을 쓰시오.
SELECT ename, sal, SUM(sal) OVER (ORDER BY sal) AS s
FROM 사원 WHERE dno = 20;
4. (결과 쓰기) 10번 부서 박서준(280) 행의 nx 값은?
SELECT ename, sal,
LEAD(sal, 1, -1) OVER (PARTITION BY dno ORDER BY sal DESC, eno) AS nx
FROM 사원 WHERE dno = 10;
5. (결과 쓰기) 다음 쿼리의 결과 행 수는?
SELECT COUNT(*) FROM (
SELECT DENSE_RANK() OVER (ORDER BY sal DESC) AS dr FROM 사원
) t WHERE dr <= 5;
6. (빈칸) 부서별 최저 급여자 이름을 모든 행에 붙이려 한다. LAST_VALUE 를 쓸 때 ( ① ) 에 들어갈 프레임은?
LAST_VALUE(ename) OVER (PARTITION BY dno ORDER BY sal DESC ( ① ))
7. (OX)
- (가)
SUM(sal) OVER (PARTITION BY dno)와SUM(sal) OVER (PARTITION BY dno ORDER BY sal)은 항상 같은 값을 낸다. - (나)
WHERE ROW_NUMBER() OVER (ORDER BY sal) <= 3은 표준 SQL 에서 허용된다. - (다) ORDER BY 가 있는 OVER 의 기본 프레임은
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW이다. - (라) 7 행에
NTILE(3)을 적용하면 묶음 크기는 3, 2, 2 다.
8. (결과 쓰기) 7명 전체에서 CUME_DIST() OVER (ORDER BY sal) 의 정민재(300) 값을 분수로 쓰시오.
정답과 풀이
1. a = 6, b = 5, c = 6. 앞에 500·450·350·300·300 다섯 행이 있어 RANK 는 6, 서로 다른 값은 500·450·350·300 네 개라 DENSE_RANK 는 5, ROW_NUMBER 는 그냥 6번째.
2. 1. WHERE 가 윈도 함수보다 먼저 실행돼 박서준 1행만 남는다. 혼자서는 1등(§1.1).
3. 정민재 600, 한유나 600, 최지우 1050. ORDER BY 가 있으니 누적이고, 기본 프레임 RANGE 가 300 동점을 한 덩어리로 묶어 둘 다 300+300 = 600(§4.3·§5.1).
4. −1. 박서준은 10번 부서에서 급여가 가장 낮아 다음 행이 없다. 세 번째 인수 −1 이 NULL 대신 들어간다.
5. 6. DENSE_RANK = 1, 2, 3, 4, 4, 5, 6 → 5 이하는 6행. RANK 였다면 1, 2, 3, 4, 4, 6, 7 → 5행(§7.3).
6. ① ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. 기본 프레임은 현재 행에서 끝나므로
LAST_VALUE 가 자기 자신을 돌려준다(§6.3). (RANGE BETWEEN … UNBOUNDED FOLLOWING 도 정답. 또는 ORDER BY 를
ASC 로 뒤집어 FIRST_VALUE.)
7. (가) X — ORDER BY 가 들어가면 누적 합계가 된다. 마지막 행(과 그 동점)에서만 같다. (나) X — 윈도 함수는 WHERE 에서 쓸 수 없다. 인라인 뷰로 감싼다. (다) O. (라) O — 7 = 3×2 + 1, 나머지 1행을 첫 묶음이 받는다.
8. 4/7. 300 이하인 행은 250·280·300·300 네 개. 동점은 덩어리 끝 위치로 센다.
한 줄 요약
| 주제 | 한 줄 |
|---|---|
| 윈도 vs GROUP BY | GROUP BY 는 행을 접고, OVER 는 행을 그대로 두고 옆에 값을 적는다 |
| 처리 순서 | WHERE·GROUP BY·HAVING 다음, ORDER BY 전 — 그래서 WHERE 에서 못 쓴다 |
| OVER 구조 | PARTITION BY(창 나누기) + ORDER BY(순서·누적 스위치) + 프레임(ROWS/RANGE 범위) |
| RANK | 동점 같은 순위, 다음 순위 건너뜀 (1, 2, 2, 4) |
| DENSE_RANK | 동점 같은 순위, 다음 순위 연속 (1, 2, 2, 3) |
| ROW_NUMBER | 동점도 다른 번호 — 유일 열로 tie-break 해야 결과가 고정 |
| NTILE(n) | 행 개수로 n 등분, 나머지는 앞 묶음부터 한 행씩 |
| 집계 + OVER | ORDER BY 없으면 파티션 전체, 있으면 처음~현재 누적 |
| ROWS vs RANGE | ROWS 는 물리적 행, RANGE 는 값 — RANGE 는 동점을 함께 포함 |
| 기본 프레임 | ORDER BY 있으면 RANGE UNBOUNDED PRECEDING ~ CURRENT ROW → 동점이 같은 누적값 |
| LAG / LEAD | n 칸 앞/뒤 값, 없으면 NULL(또는 기본값), 파티션 경계를 넘지 않음, 프레임 무시 |
| LAST_VALUE 함정 | 기본 프레임 끝이 현재 행 → 자기 자신이 나옴 → UNBOUNDED FOLLOWING 또는 FIRST_VALUE 역정렬 |
| Top-N | 윈도 결과는 인라인 뷰(또는 CTE)로 감싼 뒤 바깥 WHERE 로 거른다 |
| PERCENT_RANK / CUME_DIST | (RANK−1)/(n−1) 은 0 에서 시작, (값 이하 행 수)/n 은 1 에서 끝 |