그룹 함수와 윈도 함수 - ROLLUP CUBE GROUPING SETS RANK 윈도 프레임 (SQLD 기본 11장)
이 장에서 배우는 것
8장의 GROUP BY 는 한 가지 기준으로만 묶었다. 보고서는 대개 "지역·상품별 합계, 지역별 소계, 전체 총계"를 한 표에 원한다. 이것을 한 문장으로 만드는 것이 그룹 함수(ROLLUP, CUBE, GROUPING SETS)다. 또 "각 직원의 급여와 팀 내 순위"처럼 행을 줄이지 않고 집계 값을 옆에 붙이는 것이 윈도 함수다. 윈도 함수의 기본 문법은 『SQL 실전 기초』 10단원에 있다.
- ROLLUP·CUBE·GROUPING SETS 가 어떤 그룹 층을 만드는지 계산하고, 같은 결과를 UNION ALL 로 직접 쌓아 확인한다.
- RANK·DENSE_RANK·ROW_NUMBER 의 동점 처리와 PARTITION BY 를 확인한다.
- 기본 윈도 프레임 때문에 누적합과 LAST_VALUE 가 예상과 달라지는 이유, LAG·LEAD 와 비율 함수를 정리한다.
핵심 개념
엑셀 피벗 표에서 "부분합"을 켜 본 적이 있다면 ROLLUP 이 하는 일을 이미 안다. 문제는 SQL 결과에서 소계 행과 일반 행을 구분하는 방법이다. 소계 행의 상품 칸은 NULL 인데, 원래 데이터의 상품이 NULL 이어도 똑같이 NULL 로 보인다. 이 둘을 가르는 것이 GROUPING 함수다.
그룹 함수가 만드는 층
| 문법 | 만드는 그룹 | 층 개수 |
|---|---|---|
ROLLUP(a, b) | (a, b), (a), () | 인자 수 + 1 |
CUBE(a, b) | (a, b), (a), (b), () | 2 의 인자 수 제곱 |
GROUPING SETS((a), (b)) | (a), (b) | 적은 그대로 |
ROLLUP 은 인자 순서가 결과를 바꾼다. ROLLUP(a, b) 는 a 의 소계를, ROLLUP(b, a) 는 b 의 소계를 만든다. CUBE 와 GROUPING SETS 는 순서와 무관하게 같은 그룹을 만든다(행 순서만 다를 수 있다). GROUPING(컬럼) 은 그 컬럼이 소계 때문에 NULL 이 된 행에서 1, 아니면 0 이다.
윈도 함수의 구조
함수() OVER (PARTITION BY 묶을기준 ORDER BY 정렬기준 프레임) 이다. PARTITION BY 는 GROUP BY 처럼 묶되 행을 줄이지 않는다. ORDER BY 는 순위와 누적의 순서를 정한다. 프레임은 현재 행 기준으로 계산에 넣을 범위다.
- 순위:
RANK(동점 뒤 건너뜀: 1, 2, 2, 4),DENSE_RANK(건너뛰지 않음: 1, 2, 2, 3),ROW_NUMBER(동점도 서로 다른 번호). - 집계:
SUM,AVG,COUNT,MAX,MIN에 OVER 를 붙인다. - 행 순서:
FIRST_VALUE,LAST_VALUE,LAG(앞 행),LEAD(뒤 행). - 비율:
PERCENT_RANK,CUME_DIST,NTILE, Oracle 의RATIO_TO_REPORT.
ORDER BY 가 있고 프레임을 생략하면 기본 프레임은 "파티션 처음부터 현재 행과 같은 값을 가진 마지막 행까지"(RANGE)다. 이 한 줄이 이 장의 결과 예측 문제 절반을 설명한다.
예제 스키마
7장과 같은 스키마다. 그룹 함수는 판매(sale)를, 윈도 함수는 직원(staff)을 쓴다. 판매 금액을 지역·상품별로 미리 계산해 두면 예측이 쉽다. 서울 A 220, 서울 B 50(6번은 NULL 이라 빠짐), 부산 A 140, 부산 B 80, 서울 270, 부산 220, 총계 490.
| sale_id | staff_id | sale_month | region | product | amount |
|---|---|---|---|---|---|
| 1 | 102 | 2026-07 | 서울 | A | 100 |
| 2 | 103 | 2026-07 | 서울 | B | 50 |
| 3 | 104 | 2026-07 | 부산 | A | 70 |
| 4 | 105 | 2026-07 | 부산 | A | 30 |
| 5 | 102 | 2026-08 | 서울 | A | 120 |
| 6 | 103 | 2026-08 | 서울 | B | NULL |
| 7 | 104 | 2026-08 | 부산 | B | 80 |
| 8 | 107 | 2026-08 | 부산 | A | 40 |
SQL과 실행 결과
SQLite 에는 ROLLUP·CUBE·GROUPING SETS·GROUPING 이 없다. 그래서 각 문법이 정의상 만드는 그룹 층을 UNION ALL 로 직접 쌓아 실행했다. 결과 행은 같지만, 아래 그룹 함수 SQL 자체는 이 책에서 실행하지 않은 비교용 문법이다.
-- 비교용 문법(실행하지 않음): Oracle·SQL Server 공통
SELECT region, product, SUM(amount) AS total
FROM sale
GROUP BY ROLLUP (region, product);
SELECT region, product, SUM(amount) AS total, GROUPING(product) AS g
FROM sale
GROUP BY CUBE (region, product);
SELECT region, product, SUM(amount) AS total
FROM sale
GROUP BY GROUPING SETS ((region), (product));ROLLUP(region, product) 와 같은 결과
-- GROUP BY ROLLUP(region, product) 가 만드는 세 층을 UNION ALL 로 직접 쌓는다
SELECT * FROM (
SELECT region, product, SUM(amount) AS total FROM sale GROUP BY region, product
UNION ALL
SELECT region, NULL, SUM(amount) FROM sale GROUP BY region
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM sale
)
ORDER BY region IS NULL, region, product IS NULL, product;실행 결과:
region product total
------ ------- -----
부산 A 140
부산 B 80
부산 NULL 220
서울 A 220
서울 B 50
서울 NULL 270
NULL NULL 490층이 셋이라 행은 4(지역·상품) + 2(지역 소계) + 1(총계) = 7 이다.
그림 · ROLLUP(region, product) 가 쌓는 세 층 — 지역·상품별 4행 위에 지역 소계 2행, 맨 위에 총계 1행이 쌓여 7행이 된다. 서울 B 의 50 은 금액이 NULL 인 판매를 SUM 이 건너뛴 값이다(11-rollup-equiv).
CUBE(region, product) 와 같은 결과
-- GROUP BY CUBE(region, product): ROLLUP 에 (product) 층이 더해진다
SELECT * FROM (
SELECT region, product, SUM(amount) AS total FROM sale GROUP BY region, product
UNION ALL
SELECT region, NULL, SUM(amount) FROM sale GROUP BY region
UNION ALL
SELECT NULL, product, SUM(amount) FROM sale GROUP BY product
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM sale
)
ORDER BY region IS NULL, region, product IS NULL, product;실행 결과:
region product total
------ ------- -----
부산 A 140
부산 B 80
부산 NULL 220
서울 A 220
서울 B 50
서울 NULL 270
NULL A 360
NULL B 130
NULL NULL 490ROLLUP 에 상품별 소계 2행이 더해져 9행이다.
GROUPING SETS((region), (product)) 와 같은 결과
-- GROUP BY GROUPING SETS ((region), (product)): 소계만 있고 총계가 없다
SELECT * FROM (
SELECT region, NULL AS product, SUM(amount) AS total FROM sale GROUP BY region
UNION ALL
SELECT NULL, product, SUM(amount) FROM sale GROUP BY product
)
ORDER BY region IS NULL, region, product;실행 결과:
region product total
------ ------- -----
부산 NULL 220
서울 NULL 270
NULL A 360
NULL B 130지정한 두 층만 있고 총계가 없어 4행이다.
소계 행에 이름 붙이기
그룹 함수를 쓰는 제품에서는 CASE WHEN GROUPING(product) = 1 THEN '(소계)' ... 로 쓴다. 여기서는 층마다 표시 컬럼을 직접 붙였다.
-- GROUPING() 대신 층 번호를 직접 붙여 '소계'·'총계' 이름을 단다
SELECT CASE WHEN g_region = 1 THEN '(총계)' ELSE region END AS region,
CASE WHEN g_product = 1 AND g_region = 0 THEN '(소계)'
WHEN g_product = 1 THEN '' ELSE product END AS product,
total
FROM (
SELECT region, product, 0 AS g_region, 0 AS g_product, SUM(amount) AS total FROM sale GROUP BY region, product
UNION ALL
SELECT region, NULL, 0, 1, SUM(amount) FROM sale GROUP BY region
UNION ALL
SELECT NULL, NULL, 1, 1, SUM(amount) FROM sale
)
ORDER BY g_region, region, g_product, product;실행 결과:
region product total
------ ------- -----
부산 A 140
부산 B 80
부산 (소계) 220
서울 A 220
서울 B 50
서울 (소계) 270
(총계) 490순위 함수 세 가지
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense,
ROW_NUMBER() OVER (ORDER BY salary DESC, staff_id) AS rn
FROM staff
ORDER BY rn;실행 결과:
name salary rnk dense rn
------ ------ --- ----- --
한지수 5200 1 1 1
정우진 4800 2 2 2
오민재 4300 3 3 3
이도현 4300 3 3 4
박서윤 3900 5 4 5
윤태오 3900 5 4 6
최하늘 3600 7 5 7
강예린 3100 8 6 84,300 두 명은 RANK 와 DENSE_RANK 모두 3위다. 다음 사람(3,900)은 RANK 로 5위, DENSE_RANK 로 4위다. ROW_NUMBER 는 동점에도 다른 번호를 주므로, 두 번째 정렬 기준(staff_id)이 없으면 누가 3번인지 실행할 때마다 바뀔 수 있다.
표 · 순위 함수 세 가지의 동점 처리 (11-rank 실행 결과)
| 함수 | 4300 동점 두 명 | 다음 사람(3900) | 규칙 |
|---|---|---|---|
RANK | 3, 3 | 5 | 동점 수만큼 다음 순위를 건너뛴다 |
DENSE_RANK | 3, 3 | 4 | 건너뛰지 않는다 |
ROW_NUMBER | 3, 4 | 5 | 동점도 다른 번호(두 번째 정렬 기준 staff_id) |
PARTITION BY
SELECT team_id, name, salary,
RANK() OVER (PARTITION BY team_id ORDER BY salary DESC) AS rank_in_team
FROM staff
WHERE team_id IS NOT NULL
ORDER BY team_id, rank_in_team, staff_id;실행 결과:
team_id name salary rank_in_team
------- ------ ------ ------------
10 한지수 5200 1
10 오민재 4300 2
10 박서윤 3900 3
20 이도현 4300 1
20 최하늘 3600 2
30 정우진 4800 1
30 윤태오 3900 2기본 프레임과 누적합
SELECT name, salary,
SUM(salary) OVER (ORDER BY salary) AS default_frame,
SUM(salary) OVER (ORDER BY salary, staff_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_frame
FROM staff
ORDER BY salary, staff_id;실행 결과:
name salary default_frame rows_frame
------ ------ ------------- ----------
강예린 3100 3100 3100
최하늘 3600 6700 6700
박서윤 3900 14500 10600
윤태오 3900 14500 14500
오민재 4300 23100 18800
이도현 4300 23100 23100
정우진 4800 27900 27900
한지수 5200 33100 33100default_frame 은 급여가 같은 박서윤·윤태오에게 둘 다 14,500 을 줬다. 기본 프레임(RANGE)은 현재 행과 정렬 값이 같은 행(피어)을 모두 포함하기 때문이다. ROWS 프레임은 물리적인 행 단위로 끊으므로 10,600 → 14,500 으로 한 줄씩 늘어난다.
그림 · ORDER BY 만 쓴 윈도의 기본 프레임은 같은 값까지 포함한다 — SUM(salary) OVER (ORDER BY salary) 의 기본 프레임은 RANGE 라 급여가 같은 박서윤·윤태오가 서로를 포함해 둘 다 14500 이 된다. ROWS 로 적으면 한 행씩 늘어 박서윤은 10600 이다(11-frame-default).
LAST_VALUE 가 자기 자신을 돌려주는 이유
SELECT team_id, name, salary,
FIRST_VALUE(name) OVER w AS first_v,
LAST_VALUE(name) OVER w AS last_default,
LAST_VALUE(name) OVER (PARTITION BY team_id ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full
FROM staff
WHERE team_id IN (10, 30)
WINDOW w AS (PARTITION BY team_id ORDER BY salary DESC)
ORDER BY team_id, salary DESC;실행 결과:
team_id name salary first_v last_default last_full
------- ------ ------ ------- ------------ ---------
10 한지수 5200 한지수 한지수 박서윤
10 오민재 4300 한지수 오민재 박서윤
10 박서윤 3900 한지수 박서윤 박서윤
30 정우진 4800 정우진 정우진 윤태오
30 윤태오 3900 정우진 윤태오 윤태오기본 프레임은 "처음부터 현재 행까지"라서, 그 범위의 마지막은 언제나 현재 행(또는 그 피어)이다. 그래서 last_default 가 각 행의 자기 이름이 됐다. 팀의 진짜 마지막 값을 원하면 프레임을 UNBOUNDED FOLLOWING 까지 넓힌다. FIRST_VALUE 는 프레임 시작이 늘 파티션 처음이라 이 문제가 없다.
LAG 와 LEAD
SELECT region, sale_month, SUM(amount) AS total,
LAG(SUM(amount)) OVER (PARTITION BY region ORDER BY sale_month) AS prev_month,
LEAD(SUM(amount)) OVER (PARTITION BY region ORDER BY sale_month) AS next_month
FROM sale
GROUP BY region, sale_month
ORDER BY region, sale_month;실행 결과:
region sale_month total prev_month next_month
------ ---------- ----- ---------- ----------
부산 2026-07 100 NULL 120
부산 2026-08 120 100 NULL
서울 2026-07 150 NULL 120
서울 2026-08 120 150 NULLGROUP BY 로 먼저 월별 합계를 만든 뒤, 그 결과에 윈도 함수를 적용했다. 윈도 함수는 GROUP BY·HAVING 다음, ORDER BY 전에 계산된다.
비율과 분할
SELECT name, salary,
ROUND(salary * 1.0 / SUM(salary) OVER (), 3) AS ratio,
NTILE(3) OVER (ORDER BY salary DESC, staff_id) AS tile,
ROUND(PERCENT_RANK() OVER (ORDER BY salary DESC), 2) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY salary DESC), 2) AS cume
FROM staff
ORDER BY salary DESC, staff_id;실행 결과:
name salary ratio tile pct_rank cume
------ ------ ----- ---- -------- ----
한지수 5200 0.157 1 0.0 0.13
정우진 4800 0.145 1 0.14 0.25
오민재 4300 0.13 1 0.29 0.5
이도현 4300 0.13 2 0.29 0.5
박서윤 3900 0.118 2 0.57 0.75
윤태오 3900 0.118 2 0.57 0.75
최하늘 3600 0.109 3 0.86 0.88
강예린 3100 0.094 3 1.0 1.0ratio 는 Oracle 의 RATIO_TO_REPORT(salary) OVER () 와 같은 값이다. NTILE(3) 은 8행을 3, 3, 2 로 나눈다. 나머지가 앞 그룹부터 하나씩 붙는다. PERCENT_RANK 는 (순위 − 1) ÷ (행 수 − 1), CUME_DIST 는 (현재 값 이하인 행 수) ÷ (행 수)다. 동점 두 명의 CUME_DIST 가 똑같이 0.5 인 것은 4,300 이하(내림차순이므로 4,300 이상)인 행이 4개이기 때문이다.
표준과 구현의 차이
아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.
| 항목 | SQLite(실행) | Oracle | SQL Server |
|---|---|---|---|
| ROLLUP·CUBE·GROUPING SETS | 없음(UNION ALL 로 대체) | 지원 | 지원(옛 WITH ROLLUP 표기도 있음) |
| GROUPING, GROUPING_ID | 없음 | 지원 | 지원 |
| RATIO_TO_REPORT | 없음(SUM OVER 로 나눔) | 지원 | 없음 |
| WINDOW 절(윈도 정의 재사용) | 지원 | 21c 부터 지원 | SQL Server 2022 부터 지원 |
| 기본 프레임(ORDER BY 있을 때) | RANGE UNBOUNDED PRECEDING ~ CURRENT ROW | 같음 | 같음 |
[구현 차이] 그룹 함수 결과의 행 순서는 제품과 실행 계획에 따라 다르다. Oracle 에서 ROLLUP 결과가 소계 행이 각 그룹 끝에 오는 순서로 나오는 경우가 많지만 보장되지 않는다. 순서가 중요하면
ORDER BY GROUPING(region), region, GROUPING(product), product처럼 명시한다.
시험에서 헷갈리는 지점
판단 1. "ROLLUP(a, b) 와 ROLLUP(b, a) 는 같은 행들을 만든다"
틀렸다. 앞은 a 별 소계를, 뒤는 b 별 소계를 만든다. 행 수는 같을 수 있어도 내용이 다르다. CUBE(a, b) 와 CUBE(b, a) 는 같은 그룹을 만든다.
판단 2. "SUM(x) OVER (ORDER BY y) 는 언제나 한 행씩 늘어나는 누적합이다"
틀렸다. y 에 동점이 있으면 동점 행들이 같은 누적값을 받는다. 한 행씩 늘어나게 하려면 ROWS 프레임을 명시하거나 정렬 기준을 유일하게 만든다.
판단 3. "윈도 함수는 WHERE 절에서 쓸 수 있다"
틀렸다. 윈도 함수는 WHERE·GROUP BY·HAVING 이 끝난 뒤 계산된다. 윈도 함수 결과로 거르려면 인라인 뷰로 감싸 바깥 WHERE 에서 거른다(12장 Top-N 에서 쓴다).
연습 문제
- 박서윤(103)의
RANK()와DENSE_RANK()(급여 내림차순)를 쓰라. GROUP BY ROLLUP(product, region)의 결과 행 수를 쓰라.- 서울 판매에 대해
SUM(amount) OVER (PARTITION BY region ORDER BY sale_id)를 판매 번호 순으로 쓰라. SELECT COUNT(*) OVER (), COUNT(bonus) OVER (PARTITION BY team_id) FROM staff WHERE staff_id = 103의 결과를 쓰라.- 팀별(팀 없음 포함) 급여 1위 한 명씩을 구하라. 동점이면 직원 번호가 작은 사람을 고른다.
정답과 해설
1. 5 와 4. 앞에 5,200 / 4,800 / 4,300 / 4,300 네 명이 있으므로 RANK 는 5, 서로 다른 값은 셋이므로 DENSE_RANK 는 4.
SELECT rnk, dense FROM (
SELECT staff_id, RANK() OVER (ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM staff)
WHERE staff_id = 103;실행 결과:
rnk dense
--- -----
5 42. 7. 상품·지역 4행, 상품 소계 2행(A 360, B 130), 총계 1행. 행 수는 ROLLUP(region, product) 와 같지만 소계의 기준이 상품이다.
SELECT COUNT(*) AS rollup_rows FROM (
SELECT product, region FROM sale GROUP BY product, region
UNION ALL SELECT product, NULL FROM sale GROUP BY product
UNION ALL SELECT NULL, NULL);실행 결과:
rollup_rows
-----------
73. 100, 150, 270, 270. 6번 판매는 금액이 NULL 이라 SUM 이 건너뛰어 누적값이 그대로다.
SELECT sale_id, SUM(amount) OVER (PARTITION BY region ORDER BY sale_id) AS running
FROM sale WHERE region = '서울' ORDER BY sale_id;실행 결과:
sale_id running
------- -------
1 100
2 150
5 270
6 2704. 1 과 0. WHERE 가 먼저 한 행만 남기므로 전체 행 수도 1이고, 그 한 행(박서윤)의 보너스가 NULL 이라 0이다. 윈도 함수가 WHERE 뒤에 계산된다는 규칙을 묻는 문제다.
SELECT COUNT(*) OVER () AS a, COUNT(bonus) OVER (PARTITION BY team_id) AS b
FROM staff WHERE staff_id = 103;실행 결과:
a b
- -
1 05. 팀 없음: 강예린, 10: 한지수, 20: 이도현, 30: 정우진. PARTITION BY 는 NULL 도 하나의 파티션으로 묶는다.
SELECT team_id, name FROM (
SELECT team_id, name,
ROW_NUMBER() OVER (PARTITION BY team_id ORDER BY salary DESC, staff_id) AS rn
FROM staff)
WHERE rn = 1
ORDER BY team_id;실행 결과:
team_id name
------- ------
NULL 강예린
10 한지수
20 이도현
30 정우진다음 장에서는 순위로 상위 N 개를 자르는 방법, 계층형 질의, 행과 열을 바꾸는 PIVOT 을 다룬다.
참고 자료
- SQLite 윈도 함수
- Oracle Database 19c SQL Language Reference — ROLLUP·CUBE·GROUPING SETS 문법 확인용