그룹 함수와 ROLLUP·CUBE

GROUP BY는 행을 묶어 묶음마다 한 줄을 냅니다. 보고서는 그 한 줄들에 더해 「지역별 합계」·「전체 합계」 같은 소계 행을 원하는데, 예전에는 같은 테이블을 묶는 기준만 바꿔 여러 번 읽고 UNION ALL로 이었습니다. 한 번 읽고 여러 층의 소계를 함께 내는 기능이 이 노트의 주제이고, 시험은 어떤 인자에서 어떤 소계 행이 몇 줄 나오는가를 묻습니다.

이 노트는 아래 판매 테이블 하나로 계속 갑니다.

집계 함수

집계 함수와 NULL

여러 행을 받아 값 하나를 돌려주는 함수가 집계 함수입니다. SUM·AVG·MAX·MIN·COUNT가 대표이고, GROUP BY가 있으면 묶음마다, 없으면 테이블 전체를 한 묶음으로 보고 계산합니다.

집계 함수는 NULL을 계산에서 뺍니다. 그래서 AVG(보너스)는 보너스가 NULL인 사원을 분모에서도 빼고, 「보너스가 없는 사원은 0으로 쳐서 평균」을 원하면 AVG(NVL(보너스, 0))로 적어야 합니다. 같은 이유로 COUNT(칼럼)은 그 칼럼이 NULL이 아닌 행만 세고, COUNT(*)는 NULL과 상관없이 행 자체를 셉니다.

GROUP BY와 HAVING

GROUP BY에 적은 칼럼만 SELECT에 그대로 쓸 수 있고, 나머지 칼럼은 집계 함수 안에서만 쓸 수 있습니다. 묶음에 조건을 거는 자리는 HAVING이고, WHERE는 묶기 전의 행을 거릅니다.

SELECT 지역, SUM(매출) AS 합계
  FROM 판매
 GROUP BY 지역
HAVING SUM(매출) >= 200;   -- 서울 250 한 줄

위 판매 테이블에 이 질의를 돌리면 부산은 합계가 150이라 빠지고 서울 한 줄만 남습니다. WHERE SUM(매출) >= 200으로 적으면 집계 전 단계에 집계 함수를 쓴 것이라 오류입니다.

ROLLUP

계층 소계

ROLLUP은 인자를 오른쪽부터 하나씩 떼어 가며 묶음을 추가로 만듭니다. 인자가 (지역, 분기)이면 만들어지는 묶음이 셋입니다.

1. (지역, 분기) — 원래의 상세 행
2. (지역) — 분기를 뗀 지역별 소계
3. () — 전부 뗀 전체 합계

SELECT 지역, 분기, SUM(매출) AS 합계
  FROM 판매
 GROUP BY ROLLUP(지역, 분기);

결과는 상세 네 줄, 지역 소계 두 줄(서울 250, 부산 150), 전체 합계 한 줄(400)로 일곱 줄입니다. 소계 행에서는 떼어 낸 칼럼 자리에 NULL이 찍힙니다 — 서울 소계 줄의 분기가 NULL이고, 전체 합계 줄은 지역과 분기가 둘 다 NULL입니다. 인자가 n개이면 묶음은 $n + 1$ 개이고, 인자의 순서를 바꾸면 결과가 달라집니다. ROLLUP(분기, 지역)이면 지역 소계 대신 분기 소계(1Q 180, 2Q 220)가 나옵니다.

괄호로 묶은 인자

ROLLUP 안에서 칼럼 몇 개를 괄호로 묶으면 그 묶음은 한 덩어리로 떼어집니다. 칼럼이 셋인 ROLLUP(A, B, C)는 (A,B,C)·(A,B)·(A)·() 네 묶음을 만들지만, 괄호를 넣으면 달라집니다.

마지막 줄처럼 ROLLUP 밖에 칼럼을 두면 그 칼럼은 끝까지 떼어지지 않아 전체 합계 줄이 사라집니다. 「지역별로는 소계를 보되 전체 합계는 필요 없다」는 요구가 이 모양입니다.

CUBE와 GROUPING SETS

CUBE

CUBE는 인자의 모든 부분집합을 묶음으로 만듭니다. 인자가 n개이면 묶음이 $2^n$ 개이고, 순서를 바꿔도 만들어지는 묶음은 같습니다. CUBE(지역, 분기)는 ROLLUP의 세 묶음에 (분기) 하나가 더해져 네 묶음입니다.

결과 행 수는 묶음 수가 아니라 묶음마다 나오는 줄을 더한 값입니다. 판매 테이블에서 세어 봅니다.

합계는 $4 + 2 + 2 + 1 = 9$ 줄입니다. 같은 데이터에 ROLLUP을 쓰면 (분기) 두 줄이 빠진 7줄입니다. 시험에서는 지역이 3개, 분기가 4개처럼 값의 가짓수를 주고 세게 하는데, 상세 줄은 실제로 있는 조합의 수이지 곱이 아니라는 점을 놓치기 쉽습니다. 서울에 3Q 매출이 없으면 그 조합의 상세 줄도 없습니다.

CUBE는 모든 조합을 계산하므로 인자가 늘면 비용이 두 배씩 늘어납니다. 필요한 소계가 몇 개뿐이라면 다음의 GROUPING SETS가 맞습니다.

GROUPING SETS

GROUPING SETS는 적어 준 묶음만 만듭니다. 떼어 가는 규칙도 부분집합 규칙도 없고, 목록이 곧 결과입니다.

SELECT 지역, 분기, SUM(매출) AS 합계
  FROM 판매
 GROUP BY GROUPING SETS (지역, 분기);

이 질의는 지역 소계 두 줄과 분기 소계 두 줄, 모두 네 줄만 냅니다. 상세 줄도 전체 합계도 없습니다 — 원하면 GROUPING SETS ((지역, 분기), 지역, 분기, ())처럼 직접 적어야 하고, 이렇게 적으면 CUBE(지역, 분기)와 같은 결과가 됩니다. 빈 괄호 ()가 전체 합계를 뜻합니다.

세 기능을 한 줄씩 정리하면 다음과 같습니다.

GROUPING 함수

소계 행의 NULL과 값의 NULL

소계 행에는 떼어 낸 칼럼 자리에 NULL이 찍힌다고 했습니다. 그런데 원래 데이터에도 NULL이 있으면 두 NULL이 화면에서 구별되지 않습니다. 판매 테이블에 지역이 NULL인 행(분기 1Q, 매출 30)이 하나 더 있다고 하면, ROLLUP(지역, 분기)의 결과에는 「지역 NULL · 분기 1Q · 30」인 상세 줄과 「지역 NULL · 분기 NULL · 30」인 소계 줄, 그리고 「지역 NULL · 분기 NULL · 430」인 전체 합계 줄이 함께 섭니다. 앞의 둘은 값이 NULL인 지역의 줄이고 마지막은 소계로 생긴 NULL인데 눈으로는 가를 수 없습니다.

GROUPING

GROUPING(칼럼)은 그 줄에서 해당 칼럼이 소계로 떼어져 NULL이 된 것이면 1, 원래 값(NULL이어도)이면 0을 돌려줍니다. 위 예에서 지역이 원래 NULL인 줄은 GROUPING(지역) = 0이고, 전체 합계 줄은 1입니다. 그래서 소계 줄에 이름을 붙일 때는 NVL이 아니라 GROUPING으로 가릅니다.

SELECT CASE WHEN GROUPING(지역) = 1 THEN '전체' ELSE 지역 END AS 지역,
       CASE WHEN GROUPING(분기) = 1 THEN '소계' ELSE 분기 END AS 분기,
       SUM(매출) AS 합계
  FROM 판매
 GROUP BY ROLLUP(지역, 분기);

NVL(지역, '전체')로 적으면 원래 지역이 NULL인 줄까지 「전체」로 바뀌어 보고서가 거짓말을 합니다.

GROUPING_ID

GROUPING_ID(A, B)는 각 칼럼의 GROUPING 값을 이진수 자리로 이어 붙인 수입니다. 왼쪽 칼럼이 높은 자리이므로 값은 $\mathrm{GROUPING}(A) \times 2 + \mathrm{GROUPING}(B)$ 입니다.

칼럼이 셋이면 자리값이 4·2·1이 되어 0부터 7까지 나옵니다. 한 번에 줄의 층을 숫자 하나로 받을 수 있어 HAVING GROUPING_ID(지역, 분기) IN (0, 3)처럼 상세와 전체 합계만 남기는 거르기에 씁니다.

소계 행의 정렬

ORDER BY 없는 결과

ROLLUP 결과가 소계를 제 묶음 바로 뒤에 두고 나오는 경우가 많지만, 그 순서는 보장되지 않습니다. 실행 계획이 바뀌면 달라질 수 있으므로 보고서 순서는 반드시 ORDER BY로 정합니다.

NULL의 정렬 위치

오라클은 오름차순 정렬에서 NULL을 가장 큰 값으로 취급해 맨 뒤에 둡니다. 그래서 ORDER BY 지역, 분기만 적어도 각 지역의 소계 줄이 그 지역의 상세 줄 뒤에, 전체 합계가 맨 끝에 섭니다. 반대로 ORDER BY 지역 DESC로 뒤집으면 NULL이 맨 앞으로 와서 전체 합계가 첫 줄이 됩니다. SQL Server는 NULL을 가장 작은 값으로 두어 오름차순에서 앞에 세우므로, 같은 질의가 두 DBMS에서 다른 모양을 냅니다.

원래 값이 NULL인 줄이 섞여 있으면 NULL 정렬만으로는 소계와 상세가 뒤섞이므로 GROUPING을 정렬 키로 씁니다.

ORDER BY GROUPING(지역), 지역, GROUPING(분기), 분기

GROUPING(지역)이 먼저 오면 전체 합계(1)가 맨 끝으로 가고, 지역 안에서는 GROUPING(분기)가 소계(1)를 상세(0) 뒤로 보냅니다. 출제는 결과 표를 주고 「이 순서를 만드는 ORDER BY는?」을 묻거나, DESC 하나로 전체 합계가 어디로 가는지를 묻는 모양으로 나옵니다.

연습 문제

1. 판매 테이블의 지역 값이 서울·부산·대구 셋이고 분기 값이 1Q~4Q 넷이며, 세 지역 모두 네 분기의 매출이 다 있다. GROUP BY CUBE(지역, 분기)의 결과 행 수는?
   ① 12
   ② 16
   ③ 19
   ④ 20

   ④. 상세 줄이 $3 \times 4 = 12$, 지역 소계가 3, 분기 소계가 4, 전체 합계가 1이라 $12 + 3 + 4 + 1 = 20$ 줄입니다. ROLLUP(지역, 분기)였다면 분기 소계 4줄이 빠진 16줄입니다.
2. GROUP BY ROLLUP(A, (B, C))가 만드는 묶음으로 옳은 것은?
   ① (A,B,C) (A,B) (A) ()
   ② (A,B,C) (A) ()
   ③ (A,B,C) (A,B) ()
   ④ (A,B,C) (B,C) (A) ()

   ②. 괄호로 묶은 (B, C)는 한 덩어리로 떼어지므로 (A,B) 같은 중간 묶음이 생기지 않습니다. ③은 ROLLUP((A, B), C)의 결과입니다.
3. GROUP BY GROUPING SETS (지역, 분기)의 결과에 대한 설명으로 옳은 것은?
   ① 상세 줄과 전체 합계가 함께 나온다
   ② 지역별 소계와 분기별 소계만 나온다
   ③ ROLLUP(지역, 분기)와 같은 결과다
   ④ 인자 순서를 바꾸면 결과 줄이 달라진다

   ②. GROUPING SETS는 적어 준 묶음만 만듭니다. 상세 줄과 전체 합계를 원하면 (지역, 분기)와 ()를 직접 넣어야 합니다. 적은 묶음의 집합이 같으면 순서를 바꿔도 결과 줄은 같습니다.
4. ROLLUP(지역, 분기, 월)의 결과에서 월만 소계로 떼어진 줄의 GROUPING_ID(지역, 분기, 월) 값과, 전체 합계 줄의 값은?
   ① 1, 7
   ② 4, 7
   ③ 1, 3
   ④ 4, 3

   ①. 자리값이 지역 4, 분기 2, 월 1입니다. 월만 떼어진 줄은 $0 \times 4 + 0 \times 2 + 1 \times 1 = 1$, 셋이 다 떼어진 전체 합계는 $4 + 2 + 1 = 7$ 입니다.
5. 지역 칼럼에 NULL인 원래 값이 있는 테이블에서 ROLLUP(지역) 결과의 전체 합계 줄에만 「전체」라는 이름을 붙이려 한다. 알맞은 식은?
   ① NVL(지역, '전체')
   ② CASE WHEN 지역 IS NULL THEN '전체' ELSE 지역 END
   ③ CASE WHEN GROUPING(지역) = 1 THEN '전체' ELSE 지역 END
   ④ COALESCE(지역, '전체')

   ③. ①②④는 모두 NULL인지 아닌지만 보므로 지역이 원래 NULL인 상세 줄까지 「전체」로 바꿉니다. GROUPING은 소계로 떼어져 생긴 NULL일 때만 1을 돌려줍니다.
6. 오라클에서 SELECT 지역, 분기, SUM(매출) FROM 판매 GROUP BY ROLLUP(지역, 분기) ORDER BY 지역 DESC, 분기 DESC를 실행했다. 지역 값에는 NULL이 없다. 결과의 첫 줄과 마지막 줄은 각각 무엇인가?
   ① 전체 합계, 지역 이름이 가장 앞서는 지역의 첫 분기 상세 줄
   ② 지역 이름이 가장 뒤인 지역의 소계, 전체 합계
   ③ 전체 합계, 지역 이름이 가장 앞서는 지역의 소계
   ④ 지역 이름이 가장 뒤인 지역의 마지막 분기 상세 줄, 전체 합계

   ①. 오라클은 NULL을 가장 큰 값으로 보므로 내림차순에서는 NULL이 맨 앞에 섭니다. 지역이 NULL인 전체 합계가 첫 줄이 되고, 각 지역 안에서는 분기가 NULL인 소계가 상세 줄보다 앞섭니다. 마지막 줄은 지역을 내림차순으로 세웠을 때 끝에 오는 지역, 곧 이름이 가장 앞서는 지역의 가장 작은 분기 상세 줄입니다.