그룹 함수: ROLLUP·CUBE

그룹 함수 ROLLUP·CUBE·GROUPING SETS는 GROUP BY 결과에 소계와 총계 줄을 더합니다. GROUPING 함수는 어느 줄이 합계를 낸 줄인지 1과 0으로 알려 줍니다.

학과·학년마다 학생 수를 세다 보면 학과별 소계와 전체 총계도 함께 보고 싶어집니다. 그룹과 집계: GROUP BY·HAVING의 GROUP BY 하나로는 학과·학년마다의 줄만 나옵니다. 그룹 함수를 쓰면 GROUP BY 절 하나로 소계와 총계를 함께 냅니다. 실기 출제기준도 그룹 함수로 소계·중계·총합계를 내는 DML을 적어 둡니다. 자료에 따라 COUNT·SUM 같은 집계 함수까지 묶어 그룹 함수라고 부르기도 합니다. 이 레슨에서는 ROLLUP·CUBE·GROUPING SETS를 가리킵니다.

SELECT 학과코드,  학년,  COUNT(*) AS 인원FROM 학생WHERE 학과코드 IS NOT NULLGROUP BY 학과코드, 학년ORDER BY 학과코드, 학년;

SELECT 학과코드,  학년,  COUNT(*) AS 인원FROM 학생WHERE 학과코드 IS NOT NULLGROUP BY ROLLUP(학과코드, 학년)ORDER BY 학과코드, 학년;

SELECT 학과코드,  학년,  COUNT(*) AS 인원FROM 학생WHERE 학과코드 IS NOT NULLGROUP BY CUBE(학과코드, 학년)ORDER BY 학과코드, 학년;

SELECT 학과코드,  학년,  COUNT(*) AS 인원FROM 학생WHERE 학과코드 IS NOT NULLGROUP BY GROUPING SETS(    학과코드, 학년)ORDER BY 학과코드, 학년;

SELECT 학과코드,  학년,  COUNT(*) AS 인원,  GROUPING(학과코드) AS GFROM 학생GROUP BY ROLLUP(학과코드, 학년)ORDER BY GROUPING(학과코드),  학과코드, 학년;

SELECT 학과코드,  학년,  COUNT(*) AS 인원FROM 학생WHERE 학과코드 IS NOT NULLGROUP BY 학과코드, 학년ORDER BY 학과코드, 학년;

그림은 학과코드가 NULL인 강도현(107)을 뺀 학생 6행으로 세다가, 마지막에 강도현을 넣습니다.

1ROLLUP — 소계와 총계 줄

GROUP BY 학과코드, 학년은 학과와 학년이 같은 학생끼리 한 줄로 셉니다. CS-2 2명, CS-3 1명, EE-1 1명, EE-3 2명, 4줄입니다. GROUP BY ROLLUP(학과코드, 학년)⁠으로 바꾸면 학과마다 소계 줄이 끼어들고, 맨 끝에 총계 줄이 붙습니다. CS 3명, EE 3명, 총계 6명으로 7줄입니다. 소계 줄에서 묶지 않은 열(학년)은 NULL로 보입니다.

2CUBE와 GROUPING SETS — 어떤 묶음을 낼까

CUBE(학과코드, 학년)는 학년별 소계까지 더합니다. 1학년 1명, 2학년 2명, 3학년 3명 세 줄이 더해져 10줄입니다. GROUPING SETS(학과코드, 학년)⁠는 괄호 안에 적은 묶음, 곧 학과별 소계와 학년별 소계만 냅니다. 상세 줄과 총계 줄이 없어 5줄입니다. ROLLUP은 오른쪽 열부터 하나씩 빼며 묶음을 만들고, CUBE는 가능한 모든 조합을 만듭니다. ()는 아무 열로도 묶지 않은 총계입니다.

• 묶음 수는 결과 행 수가 아닙니다. 위 ROLLUP은 묶음이 3가지이고 결과는 7행입니다.
• ROLLUP은 열 순서를 바꾸면 소계가 달라집니다. CUBE는 열 순서를 바꿔도 같은 묶음이 나옵니다.

3GROUPING — 데이터의 NULL과 합계 줄의 NULL

강도현까지 넣으면 학과코드가 NULL인 줄이 데이터의 NULL인지 합계 줄의 NULL인지 겉으로 가릴 수 없습니다. GROUPING(학과코드)는 합계를 내느라 학과코드를 비운 줄에서 1, 나머지 줄에서 0을 돌려줍니다.

예제

결과 표는 Oracle에서 돌린 결과입니다. Oracle은 오름차순 정렬에서 NULL을 맨 뒤에 두므로, ORDER BY 학과코드, 학년이면 학과마다 소계 줄이 그 학과 줄들 바로 아래에 옵니다.

ROLLUP

SELECT 학과코드,
  학년,
  COUNT(*) AS 인원
FROM 학생
WHERE 학과코드 IS NOT NULL
GROUP BY ROLLUP(학과코드, 학년)
ORDER BY 학과코드, 학년;

(CS, NULL)은 CS 학과의 소계, (NULL, NULL)은 총계입니다.

CUBE

SELECT 학과코드,
  학년,
  COUNT(*) AS 인원
FROM 학생
WHERE 학과코드 IS NOT NULL
GROUP BY CUBE(학과코드, 학년)
ORDER BY 학과코드, 학년;

ROLLUP 결과에 학년별 소계 세 줄이 더해졌습니다. (NULL, 3) 3명은 CS 3학년 1명과 EE 3학년 2명의 합입니다.

GROUPING SETS

SELECT 학과코드,
  학년,
  COUNT(*) AS 인원
FROM 학생
WHERE 학과코드 IS NOT NULL
GROUP BY GROUPING SETS(
    학과코드, 학년)
ORDER BY 학과코드, 학년;

학과별로 센 결과와 학년별로 센 결과를 위아래로 이어 붙인 것과 같습니다.

107까지 넣으면 — GROUPING

WHERE를 빼면 학과코드가 NULL인 강도현이 들어옵니다. ORDER BY 학과코드, 학년만으로는 (NULL, NULL)인 두 줄의 차례가 정해지지 않아, GROUPING으로 먼저 정렬합니다.

SELECT 학과코드,
  학년,
  COUNT(*) AS 인원,
  GROUPING(학과코드) AS G
FROM 학생
GROUP BY ROLLUP(학과코드, 학년)
ORDER BY GROUPING(학과코드),
  학과코드, 학년;

• (NULL, 1) 1명은 학과가 없는 1학년 강도현의 줄입니다. 학과코드 NULL은 데이터의 NULL입니다.
• (NULL, NULL)이 두 줄입니다. G가 0인 1명 줄은 학과가 NULL인 그룹의 소계이고, G가 1인 7명 줄이 총계입니다.
• 이렇게 섞이지 않게 하려면 앞의 예제처럼 WHERE로 NULL인 행을 빼거나, GROUPING으로 가릅니다.

시험 포인트

• 필기: ROLLUP은 n + 1가지, CUBE는 2ⁿ가지 묶음으로 합계를 냅니다. ROLLUP은 열 순서를 바꾸면 결과가 달라지고, CUBE는 같습니다. 소계·총계 줄에서 묶지 않은 열은 NULL로 보이며, 데이터의 NULL과는 GROUPING으로 가립니다.
• 실기: 그룹 함수를 쓴 쿼리의 결과를 소계·총계 줄까지 행 차례대로 씁니다. 설명을 보고 ROLLUP·CUBE·GROUPING SETS·GROUPING을 가려 씁니다.

확인 문제

1. 수강 표를 GROUP BY CUBE(과목코드, 학번)⁠으로 묶습니다. 합계를 내는 묶음으로 알맞은 것을 고르세요.

• ① (과목코드, 학번) · (과목코드)
• ② (과목코드, 학번) · (과목코드) · ()
• ③ (과목코드, 학번) · (과목코드) · (학번)
• ④ (과목코드, 학번) · (과목코드) · (학번) · ()

2. 수강 표를 과목코드와 학번으로 묶어 점수 합계를 냅니다. (가)~(라)에 맞는 함수의 이름을 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 과목코드별 소계와 맨 끝의 총계는 내고, 학번별 소계는 내지 않습니다.
• (나) (가)의 결과에 학번별 소계까지 더해 냅니다.
• (다) 과목코드별 소계와 학번별 소계만 내고, 상세 줄과 총계 줄은 내지 않습니다.
• (라) 결과에 보이는 NULL이 데이터에 있던 값인지, 소계를 내며 비워진 칸인지 숫자로 알려 줍니다.

3. 수강 표의 점수 합계를 냅니다. (1)~(3)을 차례로 풀어 보세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.

(1) 아래 쿼리의 결과 표에서 (가)·(나)를 채우세요.

SELECT 과목코드,
  SUM(점수) AS 합계
FROM 수강
GROUP BY ROLLUP(과목코드)
ORDER BY 과목코드;

(2) 같은 쿼리의 ROLLUP만 CUBE로 바꾸면 결과는 몇 행일까요?

(3) 70점 이상인 점수만 보고 과목코드별 합계와 전체 총계를 내는 쿼리를 쓰고, 결과를 쓰세요.

SELECT 과목코드,
  SUM(점수) AS 합계
FROM 수강
WHERE 점수 >= 70
GROUP BY ROLLUP(과목코드)
ORDER BY 과목코드;

더 자세히: SQLD의 그룹 함수