← 정보처리기사 실기 개념정리 목차

SQL 완전 정리 ⑥ 윈도 함수

윈도 함수(window function)는 행을 줄이지 않고 다른 행들의 값을 끌어와 계산한다. "각 사원 옆에 부서 합계", "급여 순위", "입사 순서상 직전 사람의 급여", "누적 합계" — GROUP BY 로는 행이 접혀서, 상관 서브쿼리로는 쿼리가 길어져서 불편했던 계산을 OVER (…) 한 줄로 해결한다. 이 편은 OVER 절의 세 부품(PARTITION BY · ORDER BY · 프레임)을 하나씩 분해하고, 순위·집계·탐색 함수를 차례로 다룬 뒤, 기본 프레임이 만드는 두 가지 함정과 Top-N 패턴으로 마무리한다. 본문의 모든 쿼리와 결과 표는 sqlite3 (3.25+ 가 윈도 함수 지원)와 PostgreSQL 로 실행해 맞췄다.


0. 이 편에서 쓰는 테이블

사원 (이 편에서 쓰는 열만)

enoenamednojobsalhiredate
1김하늘10팀장5002019-03-01
2이도윤10대리3502021-07-15
3박서준10사원2802023-01-02
4최지우20팀장4502018-11-20
5정민재20사원3002022-05-09
6한유나20사원3002024-02-01
7오세린NULL사원2502025-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;
dnocnt합계
1031130
2031050

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;
enamednosal부서합계인원
김하늘1050011303
이도윤1035011303
박서준1028011303
최지우2045010503
정민재2030010503
한유나2030010503

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 이 다 끝난 행들 위에서 계산된다. 여기서 세 가지 규칙이 나온다.

  1. WHERE 가 먼저 행을 지우면 윈도는 남은 행만 본다. WHERE ename = '박서준' 뒤에 DENSE_RANK() OVER (ORDER BY sal DESC) 를 쓰면 박서준 혼자라서 순위가 1 이다.
  2. WHERE·GROUP BY·HAVING 안에서 윈도 함수를 쓸 수 없다(아직 계산 전이므로) → §8 의 인라인 뷰 패턴.
  3. 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합계순위
1011301
2010502
NULL2503

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;
enamejobsalrn누적
최지우팀장4501450
정민재사원3002750
한유나사원30031050

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;
enamesalRANKDENSE_RANKROW_NUMBER
김하늘500111
최지우450222
이도윤350333
정민재300444
한유나300445
박서준280656
오세린250767

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 사원;
enamesalSQLite rnPostgreSQL rn
정민재30034
한유나30043

(나머지 행은 같다.) 둘 다 맞는 결과다. 재현 가능한 번호가 필요하면 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;
enamednosal부서내순위
김하늘105001
이도윤103502
박서준102803
최지우204501
정민재203002
한유나203002

파티션이 바뀌면 순위가 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;
enamesal전체순위부서순위
김하늘50011
최지우45021
이도윤35032
정민재30042
한유나30042
박서준28063
오세린25071

오세린은 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;
enamesalNTILE(3)NTILE(4)NTILE(2)
김하늘500111
최지우450111
이도윤350121
정민재300221
한유나300232
박서준280332
오세린250342
n7 ÷ n묶음 크기
3몫 2, 나머지 13, 2, 2
4몫 1, 나머지 32, 2, 2, 1
2몫 3, 나머지 14, 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;
enamednosal부서내비율
김하늘1050044.2
이도윤1035031.0
박서준1028024.8
최지우2045042.9
정민재2030028.6
한유나2030028.6

OVER () 처럼 괄호를 비우면 결과 전체가 하나의 창이다. 전체 급여 합계 2430 대비 비율:

SELECT ename, sal, ROUND(100.0 * sal / SUM(sal) OVER (), 1) AS 전체비율
FROM   사원 ORDER BY eno;
enamesal전체비율
김하늘50020.6
이도윤35014.4
박서준28011.5
최지우45018.5
정민재30012.3
한유나30012.3
오세린25010.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;
enamednosal부서누적계산
김하늘10500500500
이도윤10350850500+350
박서준102801130500+350+280
최지우20450450파티션 바뀜 → 다시 시작
정민재20300750450+300
한유나203001050450+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;
enamehiredatesal그때까지최고
최지우2018-11-20450450
김하늘2019-03-01500500
이도윤2021-07-15350500
정민재2022-05-09300500
박서준2023-01-02280500
한유나2024-02-01300500
오세린2025-01-06250500

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;
enamednosal합계정렬추가
박서준102801130280
이도윤103501130630
김하늘1050011301130
정민재203001050600
한유나203001050600
최지우2045010501050

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물리적 행 개수한 행씩 따로
RANGEORDER 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;
enamesal기본프레임 (RANGE)ROWS 누적동점해소 (ORDER BY sal, eno)
김하늘500500500500
최지우450950950950
이도윤350130013001300
정민재300190016001600
한유나300190019001900
박서준280218021802180
오세린250243024302430

정민재 행을 계산할 때 프레임에 무엇이 들어가는지 펼쳐 보면:

프레임정민재(300) 행의 프레임합계
RANGE … CURRENT ROW500, 450, 350, 300(정민재), 300(한유나) — "현재 행의 값 300 까지"이므로 같은 값 한유나도 포함1900
ROWS … CURRENT ROW500, 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;
enamesal프레임이동평균
김하늘500500, 350 (앞 행 없음)425.0
이도윤350500, 350, 280376.7
박서준280350, 280, 450360.0
최지우450280, 450, 300343.3
정민재300450, 300, 300350.0
한유나300300, 300, 250283.3
오세린250300, 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;
enamesal역누적
김하늘5002430
최지우4501930
이도윤3501480
정민재3001130
한유나300830
박서준280530
오세린250250

값 범위 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;
enamesal범위포함되는 급여범위50
오세린250200~250250250
박서준280230~280250, 280530
정민재300250~300250, 280, 300, 3001130
한유나300250~300250, 280, 300, 3001130
이도윤350300~350300, 300, 350950
최지우450400~450450450
김하늘500450~500450, 500950

(숫자 오프셋 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;
enamehiredatesal이전다음증감
최지우2018-11-20450NULL500NULL
김하늘2019-03-0150045035050
이도윤2021-07-15350500300-150
정민재2022-05-09300350280-50
박서준2023-01-02280300300-20
한유나2024-02-0130028025020
오세린2025-01-06250300NULL-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;
enamednosal바로위급여두칸아래
김하늘105000박서준
이도윤10350500NULL
박서준10280350NULL
최지우204500한유나
정민재20300450NULL
한유나20300300NULL
  • 세 번째 인수 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;
enamednosal최고기본프레임_LAST전체프레임_LAST
김하늘10500김하늘김하늘박서준
이도윤10350김하늘이도윤박서준
박서준10280김하늘박서준박서준
최지우20450최지우최지우한유나
정민재20300최지우한유나한유나
한유나20300최지우한유나한유나

"부서 최저 급여자"를 원했는데 기본 프레임 열은 대부분 자기 자신이다. 기본 프레임의 끝이 CURRENT ROW 라서 프레임의 마지막 행 = 현재 행이기 때문이다. 정민재 행만 한유나가 나온 것은 RANGE 때문에 동점 한유나가 같은 덩어리로 프레임 끝에 들어갔기 때문이다(§5.1 과 같은 원리, 동점 순서에 따라 결과가 달라질 수 있음).

해결쓰는 법
① 프레임을 끝까지 연다ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
② 정렬을 뒤집어 FIRST_VALUEFIRST_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;   -- ✗
엔진오류
PostgreSQLwindow functions are not allowed in WHERE
SQLitemisuse of window function RANK()
OracleORA-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;
enamednosalrn
김하늘105001
이도윤103502
최지우204501
정민재203002

안쪽 쿼리가 끝나면 rn 은 인라인 뷰 t 의 평범한 열이다. 바깥 WHERE 가 그것을 거른다. CTE 로 써도 같다 (WITH t AS (…) SELECT … FROM t WHERE rn <= 2). 이것이 그룹별 Top-N 의 정석 패턴이다.

7.3 Top-N 에서 세 함수가 갈리는 지점

같은 "상위 N" 이라도 동점을 어떻게 볼지에 따라 행 수가 달라진다.

조건RANKDENSE_RANKROW_NUMBER (sal DESC, eno)
전체 <= 45행 (4위 동점 2명)5행4행 (정민재까지)
전체 <= 55행 (5위가 없음)6행 (박서준 = 5)5행
20번 부서 <= 23행 (최지우 + 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;
enamesalr
김하늘5001
최지우4502
이도윤3503
정민재3004
한유나3004
요구함수
"정확히 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;
jobenamesal
대리이도윤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;
enamesalRANKPERCENT_RANK계산CUME_DIST계산
오세린250100/60.1431/7
박서준28020.1671/60.2862/7
정민재30030.3332/60.5714/7
한유나30030.3332/60.5714/7
이도윤35050.6674/60.7145/7
최지우45060.8335/60.8576/7
김하늘500716/617/7
PERCENT_RANKCUME_DIST
첫 행항상 01/n (0 이 아님)
마지막 행1항상 1
동점같은 값 (RANK 기반)같은 값 — 동점 덩어리의 마지막 위치로 셈(300 두 명 모두 4/7)
읽는 법"나보다 아래가 몇 %""나 이하가 몇 %" (누적 분포)

(엔진이 보여 주는 소수 자릿수는 다르다 — SQLite 0.33333333333333332, PostgreSQL 0.3333333333333333. 값은 같다.)


9. 엔진 지원과 함수 분류 정리

엔진윈도 함수 지원
Oracle8i 부터 ("분석 함수 analytic function" 이라 부름)
SQL Server2005 부터 (프레임은 2012 부터)
PostgreSQL8.4 부터
MySQL8.0 부터 (5.7 이하는 없음 — 사용자 변수로 흉내 냈다)
SQLite3.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 BYGROUP 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 등분, 나머지는 앞 묶음부터 한 행씩
집계 + OVERORDER BY 없으면 파티션 전체, 있으면 처음~현재 누적
ROWS vs RANGEROWS 는 물리적 행, RANGE 는 값 — RANGE 는 동점을 함께 포함
기본 프레임ORDER BY 있으면 RANGE UNBOUNDED PRECEDING ~ CURRENT ROW → 동점이 같은 누적값
LAG / LEADn 칸 앞/뒤 값, 없으면 NULL(또는 기본값), 파티션 경계를 넘지 않음, 프레임 무시
LAST_VALUE 함정기본 프레임 끝이 현재 행 → 자기 자신이 나옴 → UNBOUNDED FOLLOWING 또는 FIRST_VALUE 역정렬
Top-N윈도 결과는 인라인 뷰(또는 CTE)로 감싼 뒤 바깥 WHERE 로 거른다
PERCENT_RANK / CUME_DIST(RANK−1)/(n−1) 은 0 에서 시작, (값 이하 행 수)/n 은 1 에서 끝
← SQL ⑤ 서브쿼리·집합 연산SQL ⑦ DDL·제약·뷰·인덱스 →