그룹 함수와 윈도 함수 - 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     490

ROLLUP 에 상품별 소계 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      8

4,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          33100

default_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         NULL

GROUP 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.0

ratio 는 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 에서 쓴다).

연습 문제

1. 박서윤(103)의 RANK() 와 DENSE_RANK()(급여 내림차순)를 쓰라.
2. GROUP BY ROLLUP(product, region) 의 결과 행 수를 쓰라.
3. 서울 판매에 대해 SUM(amount) OVER (PARTITION BY region ORDER BY sale_id) 를 판매 번호 순으로 쓰라.
4. SELECT COUNT(*) OVER (), COUNT(bonus) OVER (PARTITION BY team_id) FROM staff WHERE staff_id = 103 의 결과를 쓰라.
5. 팀별(팀 없음 포함) 급여 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    4

2. 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
-----------
7

3. 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        270

4. 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  0

5. 팀 없음: 강예린, 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 문법 확인용