그룹 함수

그룹 함수 ROLLUP · CUBE는 GROUP BY 결과에 소계와 전체 합계 줄을 끼워 넣고, GROUPING SETS는 고른 묶음의 합계만 냅니다. GROUPING 함수는 어느 줄이 합계 줄인지 알려 줍니다.

GROUP BY 하나로는 부서별 줄만 나와서, 전체 합계를 더하려면 합계 쿼리를 따로 만들어 UNION ALL로 붙여야 합니다. 그룹 함수는 GROUP BY 절 하나로 소계와 합계를 함께 냅니다. 이 과목에서 그룹 함수(group function)는 ROLLUP · CUBE · GROUPING SETS를 가리키며, SUM·COUNT 같은 집계 함수와 구분합니다. 자료에 따라 집계 함수도 그룹 함수의 한 갈래로 묶고, Oracle 오류 문구의 group function(ORA-00934)도 집계 함수를 가리킵니다. 시험에서 소계·합계를 내는 그룹 함수는 이 셋입니다.

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY DEPTNOORDER BY DEPTNO;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY ROLLUP(DEPTNO)ORDER BY DEPTNO;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY ROLLUP(DEPTNO)ORDER BY DEPTNO;

SELECT CASE    WHEN GROUPING(DEPTNO) = 1    THEN '전체'    ELSE NVL(TO_CHAR(DEPTNO),      '부서 없음')  END AS GRP,  SUM(SAL) AS TOTAL,  GROUPING(DEPTNO) AS GFROM EMPGROUP BY ROLLUP(DEPTNO)ORDER BY GROUPING(DEPTNO),  DEPTNO;

SELECT DEPTNO,  MGR,  SUM(SAL)FROM EMPWHERE DEPTNO IN (20, 30)GROUP BY ROLLUP(DEPTNO, MGR)ORDER BY DEPTNO, MGR;

SELECT DEPTNO,  MGR,  SUM(SAL)FROM EMPWHERE DEPTNO IN (20, 30)GROUP BY CUBE(DEPTNO, MGR)ORDER BY DEPTNO, MGR;

SELECT DEPTNO,  MGR,  SUM(SAL)FROM EMPWHERE DEPTNO IN (20, 30)GROUP BY GROUPING SETS(    DEPTNO, MGR)ORDER BY DEPTNO, MGR;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY DEPTNOORDER BY DEPTNO;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY ROLLUP(DEPTNO)ORDER BY DEPTNO;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY ROLLUP(DEPTNO)ORDER BY DEPTNO;

SELECT DEPTNO,  SUM(SAL)FROM EMPGROUP BY DEPTNOORDER BY DEPTNO;

1ROLLUP과 합계 줄

ROLLUP은 괄호 안의 열을 오른쪽부터 하나씩 비우며 묶음마다 합계를 냅니다. 열이 DEPTNO 하나면 부서별 네 줄 아래에 모든 행을 더한 2530 한 줄이 붙습니다. 소계·합계 줄에서 묶지 않은 열은 NULL로 나옵니다. 그래서 DEPTNO가 NULL인 줄이 정하늘의 NULL 그룹과 전체 합계 둘이 되고, ORDER BY DEPTNO만으로는 두 줄의 순서도 정해지지 않습니다.

2GROUPING으로 합계 줄 가리기

GROUPING(열)이 1이면 그 열을 비운 합계 줄이고, 0이면 그 열의 값이 그대로 나온 줄입니다. 데이터에 원래 있던 NULL은 0입니다. 그래서 CASE로 1인 줄에 '전체', 0인데 NULL인 줄에 '부서 없음'을 붙이고, ORDER BY GROUPING(DEPTNO)⁠로 합계 줄을 맨 아래에 둡니다. DECODE로도 붙이지만, 다음 꼴은 정하늘 그룹에서 DEPTNO 값인 NULL을 그대로 냅니다.

DECODE(GROUPING(DEPTNO), 1,
    '전체', DEPTNO)

GROUPING의 인자는 GROUP BY에 쓴 열이어야 하고(GROUPING(SAL)은 ORA-00979), ROLLUP 없이 GROUP BY만 쓰면 늘 0입니다.

3ROLLUP·CUBE·GROUPING SETS

김민준·정하늘의 NULL이 소계 줄의 NULL과 섞이지 않게, 20·30번 부서 사원 4명을 DEPTNO와 MGR로 묶습니다.

ROLLUP은 오른쪽 열부터 빼므로 열 순서를 바꾸면 소계가 달라지고, 모든 조합인 CUBE와 적은 묶음만 내는 GROUPING SETS는 순서를 바꿔도 같은 묶음입니다. GROUPING SETS는 묶음마다 낸 결과를 UNION ALL로 이은 것과 같습니다. 상세 묶음 (DEPTNO, MGR)이나 전체 합계 ()도 괄호 안에 적어야 나옵니다. 그래서 ROLLUP과 CUBE를 GROUPING SETS로 바꿔 쓸 수 있습니다.

ROLLUP(A, B)
= GROUPING SETS(
    (A, B), A, ())

CUBE(A, B)
= GROUPING SETS(
    (A, B), A, B, ())

일부 열만 ROLLUP에 넣으면 ROLLUP 밖의 열은 늘 묶입니다. GROUP BY DEPTNO, ROLLUP(MGR)⁠은 부서별 소계는 내지만 전체 합계는 내지 않습니다. ROLLUP((DEPTNO, MGR))⁠처럼 열을 괄호로 한 번 더 묶으면 한 덩어리로 다뤄, 상세 줄과 전체 합계만 냅니다.

예제

GROUPING으로 합계 줄에 이름을 붙이고 맨 아래로 보냅니다.

SELECT CASE
    WHEN GROUPING(DEPTNO) = 1
    THEN '전체'
    ELSE NVL(TO_CHAR(DEPTNO),
      '부서 없음')
  END AS GRP,
  SUM(SAL) AS TOTAL,
  GROUPING(DEPTNO) AS G
FROM EMP
GROUP BY ROLLUP(DEPTNO)
ORDER BY GROUPING(DEPTNO),
  DEPTNO;

20·30번 부서 4명의 CUBE입니다. (NULL, 1001) 920은 김민준이 관리하는 강도윤(470)과 최유나(450)의 합입니다.

SELECT DEPTNO,
  MGR,
  SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY CUBE(DEPTNO, MGR)
ORDER BY DEPTNO, MGR;

GROUPING SETS에 상세 묶음 (DEPTNO, MGR)과 전체 합계 ()를 적으면 그 줄들만 나옵니다.

SELECT DEPTNO,
  MGR,
  SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY GROUPING SETS(
    (DEPTNO, MGR), ())
ORDER BY DEPTNO, MGR;

시험 포인트

• ROLLUP은 열이 n개이면 n + 1 묶음이고, 오른쪽 열부터 빼므로 열 순서를 바꾸면 결과가 바뀝니다.
• CUBE는 2의 n제곱 묶음이라 열 순서를 바꿔도 같은 묶음이 나옵니다.
• GROUPING SETS에 적지 않은 묶음은 나오지 않습니다. 상세 줄이나 전체 합계도 (A, B)나 ()를 적어야 합니다.
• 합계 줄의 NULL과 데이터의 NULL은 GROUPING(열)로 가립니다.
• GROUP BY A, ROLLUP(B)⁠는 A가 늘 묶여서 전체 합계 줄이 없습니다.
• 그룹 함수의 결과도 ORDER BY 없이는 순서가 보장되지 않습니다.

확인 문제

1. 20·30번 부서 4명을 ROLLUP(MGR, DEPTNO)로 묶으면 어떤 소계가 나오고, 결과는 몇 줄일까요?

SELECT MGR,
  DEPTNO,
  SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY ROLLUP(MGR, DEPTNO)
ORDER BY MGR, DEPTNO;

2. 다음 결과에서 GD가 1이고 GM이 0인 줄은 무엇을 뜻하고, 몇 줄일까요?

SELECT DEPTNO,
  MGR,
  SUM(SAL),
  GROUPING(DEPTNO) AS GD,
  GROUPING(MGR) AS GM
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY CUBE(DEPTNO, MGR)
ORDER BY DEPTNO, MGR;

3. 20·30번 부서 사원으로 부서별 합계와 전체 합계만 내려고 합니다. GROUPING SETS의 괄호 안에 무엇을 적고, 결과는 몇 줄일까요?

SELECT DEPTNO,
  SUM(SAL)
FROM EMP
WHERE DEPTNO IN (20, 30)
GROUP BY GROUPING SETS(
    DEPTNO, ())
ORDER BY DEPTNO;