[DATABASE] 그룹함수란? ROLLUP, CUBE, GROUPING SETS 란?
ANSI/ISO SQL 표준에서 정의한 함수란?
AGGREGATE FUNCTION
GROUP AGGREGATE FUNCTION 이라고도 부릅니다.
COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수들이 포함됩니다.
GROUP FUNCTION
집계함수를 제외하고, 소그룹 간의 소계를 계산하는 ROLLUP, GROUP BY 항목들간 다차원적인 소계를 계산하는 CUBE, 특정항목에 대한 소계를 계산하는 GROUPING SETS 함수가 있습니다.
WINDOW FUNCTION
분석함수(ANALYTIC FUNCTION)나 순위함수(RANK FUNCTION)로도 알려져 있는 윈도우 함수는 데이터웨어하우스에서 발전된 기능입니다.
ROLLUP 함수
ROLLUP 에 지정된 Grouping Columns 의 List 는 Subtotal 을 생성하기 위해 사용되어집니다.
Grouping Columns 의 수를 N 이라고 했을 때 N + 1 Level 의 Subtotal 이 생성됩니다.
ROLLUP 의 인수는 계층구조이므로 인수 순서가 바뀌게 되면 수행 결과도 바뀌게 되므로 인수의 순서에 주의하여야 합니다.
일반적인 GROUP BY 절을 사용한 구문의 결과입니다.
이전과 달리 현재는 GROUP BY 가 정렬을 지원하지 않으므로 정렬을 원하는 경우 ORDER BY 를 써야합니다.
SQL> SELECT DNAME, JOB, 2 COUNT(*) "TOTAL EMPL", 3 SUM(SAL) "TOTAL SAL" 4 FROM EMP, DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 GROUP BY DNAME, JOB; DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- SALES MANAGER 1 2850 SALES CLERK 1 950 ACCOUNTING MANAGER 1 2450 ACCOUNTING PRESIDENT 1 5000 ACCOUNTING CLERK 1 1300 SALES SALESMAN 4 5600 RESEARCH MANAGER 1 2975 RESEARCH ANALYST 2 6000 RESEARCH CLERK 2 1900 9 rows selected. SQL> SELECT DNAME, JOB, 2 COUNT(*) "TOTAL EMPL", 3 SUM(SAL) "TOTAL SAL" 4 FROM EMP, DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 GROUP BY DNAME, JOB 7 ORDER BY DNAME, JOB; DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- ACCOUNTING CLERK 1 1300 ACCOUNTING MANAGER 1 2450 ACCOUNTING PRESIDENT 1 5000 RESEARCH ANALYST 2 6000 RESEARCH CLERK 2 1900 RESEARCH MANAGER 1 2975 SALES CLERK 1 950 SALES MANAGER 1 2850 SALES SALESMAN 4 5600 9 rows selected.
이 구문을 ROLLUP 함수를 사용하여 나타내면 아래와 같습니다.
실행결과를 보면 추가 LEVEL 의 집계가 생성된 것을 볼 수 있습니다.
Level1 - GROUP BY 수행시 생성되는 표준집게 9건
Level2 - DNAME 별 모든 JOB 의 SUBTOTAL 3건
Level3 - GRAND TOTAL SUBTOTAL 의 합산
SQL> SELECT DNAME, JOB, 2 COUNT(*) "TOTAL EMPL", 3 SUM(SAL) "TOTAL SAL" 4 FROM EMP, DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 GROUP BY ROLLUP(DNAME, JOB); DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- SALES CLERK 1 950 SALES MANAGER 1 2850 SALES SALESMAN 4 5600 SALES 6 9400 -- SALES 기준 JOB 의 집계내역 RESEARCH CLERK 2 1900 RESEARCH ANALYST 2 6000 RESEARCH MANAGER 1 2975 RESEARCH 5 10875 -- RESEARCH 기준 JOB 의 집계내역 ACCOUNTING CLERK 1 1300 ACCOUNTING MANAGER 1 2450 ACCOUNTING PRESIDENT 1 5000 DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- ACCOUNTING 3 8750 -- ACCOUNTING 기준 JOB 의 집계내역 14 29025 -- 모든 DNAME 기준 모든 JOB 의 집계내역 13 rows selected.
ROLLUP 도 일반 GROUP BY 와 마찬가지로 정렬을 지원하지 않으므로 정렬을 원하는 경우 ORDER BY 를 써야 합니다.
CUBE 함수
ROLLUP 에서는 단지 가능한 Subtotal 만을 생성했지만, CUBE 는 결합 가능한 모든 값에 대하여 다차원 집계를 생성합니다.
내부적으로 Grouping Columns 의 순서를 바꾸어서 또 한번의 Query 를 추가로 수행해야 합니다. 뿐만 아니라 Grand Total 은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query 에서는 제거되어야만 하므로 ROLLUP 에 비해 시스템의 연산 대상이 많습니다.
CUBE 는 GROUPING COLUMNS 이 가질 수 있는 모든 경우의 수에 대하여 Subtotal 을 생성하므로 GROUPING COLUMNS 의 수가 N 이라고 가정하면, 2의 n승 LEVEL 의 Subtotal 을 생성합니다.
SQL> SELECT DNAME, JOB, 2 COUNT(*) "TOTAL EMPL", 3 SUM(SAL) "TOTAL SAL" 4 FROM EMP, DEPT 5 WHERE DEPT.DEPTNO = EMP.DEPTNO 6 GROUP BY CUBE(DNAME, JOB); DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- 14 29025 -- 모든 DNAME 기준 모든 JOB 의 집계내역 CLERK 4 4150 -- CLERK 기준 집계 내역 ANALYST 2 6000 -- ANALYST 기준 집계 내역 MANAGER 3 8275 -- MANAGER 기준 집계 내역 SALESMAN 4 5600 -- SALESMAN 기준 집계 내역 PRESIDENT 1 5000 -- PRESIDENT 기준 집계 내역 SALES 6 9400 -- SLAES 기준 JOB 의 집계내역 SALES CLERK 1 950 SALES MANAGER 1 2850 SALES SALESMAN 4 5600 RESEARCH 5 10875 -- RESEARCH 기준 JOB 의 집계내역 DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- RESEARCH CLERK 2 1900 RESEARCH ANALYST 2 6000 RESEARCH MANAGER 1 2975 ACCOUNTING 3 8750 -- ACCOUNTING 기준 JOB 의 집계내역 ACCOUNTING CLERK 1 1300 ACCOUNTING MANAGER 1 2450 ACCOUNTING PRESIDENT 1 5000 18 rows selected.
CUBE 도 일반 GROUP BY 와 마찬가지로 정렬을 지원하지 않으므로 정렬을 원하는 경우 ORDER BY 를 써야 합니다.
GROUPING SETS 함수
GROUPING SETS 를 이용해 더욱 다양한 소계 집합을 만들 수 있습니다.
DNAME 으로 그룹핑된 데이터와 JOB 으로 그룹핑된 데이터를 확인할 수 있습니다.
SQL> SELECT DNAME, JOB
2 COUNT(*) "TOTAL EMPL",
3 SUM(SAL) "TOTAL SAL"
4 FROM EMP, DEPT
5 WHERE DEPT.DEPTNO = EMP.DEPTNO
6 GROUP BY GROUPING SETS (DNAME, JOB);
DNAME JOB TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ----------
ACCOUNTING 3 8750
RESEARCH 5 10875
SALES 6 9400
CLERK 4 4150
SALESMAN 4 5600
PRESIDENT 1 5000
MANAGER 3 8275
ANALYST 2 6000
8 rows selected.GROUPING SETS 에는 다양한 집계를 포함할 수 있습니다.
1 ~ 10 건의 데이터는 DNAME, JOB, MGR 의 집계 기준
11 ~ 18 건의 데이터는 JOB, MGR 의 집계 기준
19 ~ 27 건의 데이터는 DNAME, JOB 의 집계 기준을 나타냅니다.
SQL> SELECT DNAME, JOB, MGR,
2 COUNT(*) "TOTAL EMPL",
3 SUM(SAL) "TOTAL SAL"
4 FROM EMP, DEPT
5 WHERE DEPT.DEPTNO = EMP.DEPTNO
6 GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));
DNAME JOB MGR TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ---------- ----------
SALES CLERK 7698 1 950
SALES MANAGER 7839 1 2850
SALES SALESMAN 7698 4 5600
RESEARCH CLERK 7788 1 1100
RESEARCH CLERK 7902 1 800
RESEARCH ANALYST 7566 2 6000
RESEARCH MANAGER 7839 1 2975
ACCOUNTING CLERK 7782 1 1300
ACCOUNTING MANAGER 7839 1 2450
ACCOUNTING PRESIDENT 1 5000
SALES CLERK 1 950
DNAME JOB MGR TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ---------- ----------
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
CLERK 7698 1 950
CLERK 7902 1 800
PRESIDENT 1 5000
DNAME JOB MGR TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ---------- ----------
CLERK 7788 1 1100
CLERK 7782 1 1300
SALESMAN 7698 4 5600
MANAGER 7839 3 8275
ANALYST 7566 2 6000
27 rows selected.GROUPING 함수를 사용하여 빈란 채우는 방법
소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고, 그 외의 결과에는 0 이 표시됩니다.
GROUPING 함수와 CASE/DECODE 를 이용해, 소계를 나타내는 필드에 원하는 문자를 지정할 수 있습니다.
일반적인 ROLLUP 함수를 썼을때의 결과입니다.
SQL> SELECT DNAME,
2 JOB,
3 COUNT(*) "TOTAL EMPL",
4 SUM(SAL) "TOTAL SAL"
5 FROM EMP, DEPT
6 WHERE DEPT.DEPTNO = EMP.DEPTNO
7 GROUP BY ROLLUP(DNAME, JOB);
DNAME JOB TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ----------
SALES CLERK 1 950
SALES MANAGER 1 2850
SALES SALESMAN 4 5600
SALES 6 9400
RESEARCH CLERK 2 1900
RESEARCH ANALYST 2 6000
RESEARCH MANAGER 1 2975
RESEARCH 5 10875
ACCOUNTING CLERK 1 1300
ACCOUNTING MANAGER 1 2450
ACCOUNTING PRESIDENT 1 5000
DNAME JOB TOTAL EMPL TOTAL SAL
-------------- --------- ---------- ----------
ACCOUNTING 3 8750
14 29025
13 rows selected.여기에 GROUPING 함수를 붙이면 다음과 같습니다.
집계를 표시한 레코드에서는 1 이 출력되는 것을 확인할 수 있습니다.
SQL> SELECT DNAME, GROUPING(DNAME),
2 JOB, GROUPING(JOB),
3 COUNT(*) "TOTAL EMPL",
4 SUM(SAL) "TOTAL SAL"
5 FROM EMP, DEPT
6 WHERE DEPT.DEPTNO = EMP.DEPTNO
7 GROUP BY ROLLUP(DNAME, JOB);
DNAME GROUPING(DNAME) JOB GROUPING(JOB) TOTAL EMPL TOTAL SAL
-------------- --------------- --------- ------------- ---------- ----------
SALES 0 CLERK 0 1 950
SALES 0 MANAGER 0 1 2850
SALES 0 SALESMAN 0 4 5600
SALES 0 1 6 9400
RESEARCH 0 CLERK 0 2 1900
RESEARCH 0 ANALYST 0 2 6000
RESEARCH 0 MANAGER 0 1 2975
RESEARCH 0 1 5 10875
ACCOUNTING 0 CLERK 0 1 1300
ACCOUNTING 0 MANAGER 0 1 2450
ACCOUNTING 0 PRESIDENT 0 1 5000
DNAME GROUPING(DNAME) JOB GROUPING(JOB) TOTAL EMPL TOTAL SAL
-------------- --------------- --------- ------------- ---------- ----------
ACCOUNTING 0 1 3 8750
1 1 14 29025
13 rows selected.여기에 CASE 절을 사용하여 집계 레코드를 보기좋게 출력시킬 수 있습니다.
SQL> SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All DNAME' ELSE DNAME END AS DNAME, 2 CASE GROUPING(JOB) WHEN 1 THEN 'All JOB' ELSE JOB END AS JOB, 3 COUNT(*) "TOTAL EMPL", 4 SUM(SAL) "TOTAL SAL" 5 FROM EMP, DEPT 6 WHERE DEPT.DEPTNO = EMP.DEPTNO 7 GROUP BY ROLLUP(DNAME, JOB); DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- SALES CLERK 1 950 SALES MANAGER 1 2850 SALES SALESMAN 4 5600 SALES All JOB 6 9400 RESEARCH CLERK 2 1900 RESEARCH ANALYST 2 6000 RESEARCH MANAGER 1 2975 RESEARCH All JOB 5 10875 ACCOUNTING CLERK 1 1300 ACCOUNTING MANAGER 1 2450 ACCOUNTING PRESIDENT 1 5000 DNAME JOB TOTAL EMPL TOTAL SAL -------------- --------- ---------- ---------- ACCOUNTING All JOB 3 8750 All DNAME All JOB 14 29025 13 rows selected. }}}
이 내용은 'SQL 전문가 가이드, 한국데이터베이스 진흥원' 에서 공부한 내용입니다.