by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

제5절 그룹 함수(GROUP FUNCTION)




제5절 그룹 함수(GROUP FUNCTION)

1. 데이터 분석 개요

ANSI/ISO SQL 표준은 데이터 분석을 위해서 다음 세 가지 함수를 정의하고 있다.

  • AGGREGATE FUNCTION : COUNT, SUM, AVG, MAX, MIN 외 각종 집계 함수 (동의어 : GROUP AGGREGATE FUNCTION)
  • GROUP FUNCTION : ROLLUP , CUBE, GROUPING SETS (소계 , 중계, 총합계, 등 여러 레벨로 사용할때)
  • WINDOW FUNCTION : RANK, DENSE_RANK ( 분석 함수(ANALYTIC FUNCTION), 순위 함수(RANK FUNCTION) )

2. ROLLUP 함수.

Useful Information

ROLLUP에 지정된 Grouping Columns의 List는 Subtotal을 생성하기 위해 사용되어지며, Grouping Columns의 수를 N이라고 했을 때 N+1 Level의 Subtotal이 생성된다.
중요한 것은, ROLLUP의 인수는 계층 구조이므로 인수 순서가 바뀌면 수행 결과도 바뀌게 되므로 인수의 순서에도 주의해야 한다.

일반 Group By 와 Rollup 사용 시 차이점.

SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     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, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     GROUP BY DNAME, JOB 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.

SQL> SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     GROUP BY ROLLUP (DNAME, JOB); 

DNAME          JOB       Total Empl  Total Sal
-------------- --------- ---------- ----------
SALES          CLERK              1        950   Level 1
SALES          MANAGER            1       2850
SALES          SALESMAN           4       5600
SALES                             6       9400   > Level 2
RESEARCH       CLERK              2       1900
RESEARCH       ANALYST            2       6000
RESEARCH       MANAGER            1       2975   > Level 2
RESEARCH                          5      10875
ACCOUNTING     CLERK              1       1300
ACCOUNTING     MANAGER            1       2450 
ACCOUNTING     PRESIDENT          1       5000
ACCOUNTING                        3       8750   > Level 2
                                 14      29025   > Level 3

13 rows selected.

 * L1 - GROUP BY 수행시 생성되는 표준 집계 (9건)
 * L2 - DNAME 별 모든 JOB의 SUBTOTAL (3건)
 * L3 - GRAND TOTAL (마지막 행, 1건)

 추가로 ROLLUP의 경우 계층 간 집계에 대해서는 LEVEL 별 순서(L1→L2→L3)를 정렬하지만, 
 계층 내 GROUP BY 수행시 생성되는 표준 집계에는 별도의 정렬을 지원하지 않는다. 
 L1, L2, L3 계층 내 정렬을 위해서는 별도의 ORDER BY 절을 사용해야 한다

3. GROUPING 함수.

Useful Information

ROLLUP, CUBE, GROUPING SETS 등 새로운 그룹 함수를 지원하기 위해 GROUPING 함수가 추가되었다.
ROLLUP이나 CUBE에 의한 소계가 계산된 결과에는 GROUPING(EXPR) = 1 이 표시되고, - 그 외의 결과에는 GROUPING(EXPR) = 0 이 표시된다.
GROUPING 함수와 CASE/DECODE를 이용해, 소계를 나타내는 필드에 원하는 문자열을 지정할 수 있어, 보고서 작성시 유용하게 사용할 수 있다.


 STEP 3. GROUPING 함수 사용 

 SELECT DNAME, GROUPING(DNAME), JOB, GROUPING(JOB), COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
 FROM EMP, DEPT 
 WHERE DEPT.DEPTNO = EMP.DEPTNO 
 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
ACCOUNTING                   0                       1          3       8750
                             1                       1         14      29025

13 rows selected.

STEP 4. GROUPING 함수 + CASE 사용 

SQL> SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
            CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, 
     COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     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 Jobs           6       9400
RESEARCH        CLERK              2       1900
RESEARCH        ANALYST            2       6000
RESEARCH        MANAGER            1       2975
RESEARCH        All Jobs           5      10875
ACCOUNTING      CLERK              1       1300
ACCOUNTING      MANAGER            1       2450
ACCOUNTING      PRESIDENT          1       5000
ACCOUNTING      All Jobs           3       8750
All Departments All Jobs          14      29025

13 rows selected.

SQL> SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME,
            DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB,
            COUNT(*) "Total Empl", SUM(SAL) "Total Sal"
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO
     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 Jobs           6       9400
RESEARCH        CLERK              2       1900
RESEARCH        ANALYST            2       6000
RESEARCH        MANAGER            1       2975
RESEARCH        All Jobs           5      10875
ACCOUNTING      CLERK              1       1300
ACCOUNTING      MANAGER            1       2450
ACCOUNTING      PRESIDENT          1       5000
ACCOUNTING      All Jobs           3       8750
All Departments All Jobs          14      29025

13 rows selected.

STEP 4-2. ROLLUP 함수 일부 사용 

SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
       CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, 
       COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DNAME, ROLLUP(JOB) 
 

DNAME           JOB       Total Empl  Total Sal
--------------- --------- ---------- ----------
SALES           CLERK              1        950
SALES           MANAGER            1       2850
SALES           SALESMAN           4       5600
SALES           All Jobs           6       9400
RESEARCH        CLERK              2       1900
RESEARCH        ANALYST            2       6000
RESEARCH        MANAGER            1       2975
RESEARCH        All Jobs           5      10875
ACCOUNTING      CLERK              1       1300
ACCOUNTING      MANAGER            1       2450
ACCOUNTING      PRESIDENT          1       5000
ACCOUNTING      All Jobs           3       8750

12 rows selected.

 결과는 마지막 ALL DEPARTMENTS & ALL JOBS 줄만 계산이 되지 않았다. 
 ROLLUP이 JOB 칼럼에만 사용되었기 때문에 DNAME에 대한 집계는 필요하지 않기 때문이다. 

STEP 4-3. ROLLUP 함수 결합 칼럼 사용

SQL> SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     GROUP BY ROLLUP (DNAME, (JOB, MGR));

DNAME          JOB              MGR  Total Sal
-------------- --------- ---------- ----------
SALES          CLERK           7698        950
SALES          MANAGER         7839       2850
SALES          SALESMAN        7698       5600
SALES                                     9400
RESEARCH       CLERK           7788       1100
RESEARCH       CLERK           7902        800
RESEARCH       ANALYST         7566       6000
RESEARCH       MANAGER         7839       2975
RESEARCH                                 10875
ACCOUNTING     CLERK           7782       1300
ACCOUNTING     MANAGER         7839       2450
ACCOUNTING     PRESIDENT                  5000
ACCOUNTING                                8750
                                         29025

14 rows selected.

4.CUBE 함수

Useful Information

ROLLUP에서는 단지 가능한 Subtotal만을 생성하였지만, CUBE는 결합 가능한 모든 값에 대하여 다차원 집계를 생성한다. CUBE를 사용할 경우에는 내부적으로는 Grouping Columns의 순서를 바꾸어서 또 한 번의 Query를 추가 수행해야 한다. 뿐만 아니라 Grand Total은 양쪽의 Query 에서 모두 생성이 되므로 한 번의 Query에서는 제거되어야만 하므로 ROLLUP에 비해 시스템의 연산 대상이 많다.

이처럼 Grouping Columns이 가질 수 있는 모든 경우에 대하여 Subtotal을 생성해야 하는 경우에는 CUBE를 사용하는 것이 바람직하나, ROLLUP에 비해 시스템에 많은 부담을 주므로 사용에 주의해야 한다. CUBE 함수의 경우 표시된 인수들에 대한 계층별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌는 경우 행간에 정렬 순서는 바뀔 수 있어도 데이터 결과는 같다. 그리고 CUBE도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

GROUP BY ROLLUP (DNAME, JOB) 조건에서 GROUP BY CUBE (DNAME, JOB) 조건으로 변경해서 수행한다

SQL> SELECT CASE GROUPING(DNAME) WHEN 1 THEN 'All Departments' ELSE DNAME END AS DNAME,
            CASE GROUPING(JOB) WHEN 1 THEN 'All Jobs' ELSE JOB END AS JOB, 
            COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO 
     GROUP BY CUBE (DNAME, JOB) ; 

DNAME           JOB       Total Empl  Total Sal
--------------- --------- ---------- ----------
All Departments All Jobs          14      29025
All Departments CLERK              4       4150
All Departments ANALYST            2       6000
All Departments MANAGER            3       8275
All Departments SALESMAN           4       5600
All Departments PRESIDENT          1       5000
SALES           All Jobs           6       9400
SALES           CLERK              1        950
SALES           MANAGER            1       2850
SALES           SALESMAN           4       5600
RESEARCH        All Jobs           5      10875
RESEARCH        CLERK              2       1900
RESEARCH        ANALYST            2       6000
RESEARCH        MANAGER            1       2975
ACCOUNTING      All Jobs           3       8750
ACCOUNTING      CLERK              1       1300
ACCOUNTING      MANAGER            1       2450
ACCOUNTING      PRESIDENT          1       5000

18 rows selected.




Execution Plan
----------------------------------------------------------
Plan hash value: 4232949899

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |    14 |   756 |     8  (25)| 00:00:01 |
|   1 |  SORT GROUP BY                |      |    14 |   756 |     8  (25)| 00:00:01 |
|   2 |   GENERATE CUBE               |      |    14 |   756 |     8  (25)| 00:00:01 |
|   3 |    SORT GROUP BY              |      |    14 |   756 |     8  (25)| 00:00:01 |
|*  4 |     HASH JOIN                 |      |    14 |   756 |     7  (15)| 00:00:01 |
|   5 |      TABLE ACCESS STORAGE FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS STORAGE FULL| EMP  |    14 |   448 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         30  consistent gets
          0  physical reads
          0  redo size
       1353  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         18  rows processed


CUBE는 GROUPING COLUMNS이 가질 수 있는 모든 경우의 수에 대하여 Subtotal을 생성하므로 GROUPING COLUMNS의 수가 N이라고 가정하면, 2의 N승 LEVEL의 Subtotal을 생성하게 된다. 실행 결과에서 CUBE 함수 사용으로 ROLLUP 함수의 결과에다 업무별 집계까지 추가해서 출력할 수 있는데, ROLLUP 함수에 비해 업무별 집계를 표시한 5건의 레코드가 추가된 것을 확인할 수 있다. (All Departments - CLERK, ANALYST, MANAGER, SALESMAN, PRESIDENT 별 집계가 5건 추가되었다.)

SQL>  SELECT DNAME, JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
      FROM EMP, DEPT 
      WHERE DEPT.DEPTNO = EMP.DEPTNO 
      GROUP BY DNAME, JOB 
      UNION ALL
      SELECT DNAME, 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
      FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO 
      GROUP BY DNAME 
      UNION ALL
      SELECT 'All Departments', JOB, COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
      FROM EMP, DEPT 
      WHERE DEPT.DEPTNO = EMP.DEPTNO
      GROUP BY JOB 
      UNION ALL
      SELECT 'All Departments', 'All Jobs', COUNT(*) "Total Empl", SUM(SAL) "Total Sal"        
      FROM EMP, DEPT 
      WHERE DEPT.DEPTNO = EMP.DEPTNO;

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
ACCOUNTING      All Jobs           3       8750
RESEARCH        All Jobs           5      10875
SALES           All Jobs           6       9400
All Departments CLERK              4       4150
All Departments SALESMAN           4       5600
All Departments PRESIDENT          1       5000
All Departments MANAGER            3       8275
All Departments ANALYST            2       6000
All Departments All Jobs          14      29025

18 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2199532226

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |    43 |  2097 |    29  (80)| 00:00:01 |
|   1 |  UNION-ALL                   |      |       |       |            |          |
|   2 |   HASH GROUP BY              |      |    14 |   756 |     8  (25)| 00:00:01 |
|*  3 |    HASH JOIN                 |      |    14 |   756 |     7  (15)| 00:00:01 |
|   4 |     TABLE ACCESS STORAGE FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|   5 |     TABLE ACCESS STORAGE FULL| EMP  |    14 |   448 |     3   (0)| 00:00:01 |
|   6 |   HASH GROUP BY              |      |    14 |   672 |     8  (25)| 00:00:01 |
|*  7 |    HASH JOIN                 |      |    14 |   672 |     7  (15)| 00:00:01 |
|   8 |     TABLE ACCESS STORAGE FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
|   9 |     TABLE ACCESS STORAGE FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
|  10 |   HASH GROUP BY              |      |    14 |   630 |     8  (25)| 00:00:01 |
|* 11 |    HASH JOIN                 |      |    14 |   630 |     7  (15)| 00:00:01 |
|  12 |     TABLE ACCESS STORAGE FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|  13 |     TABLE ACCESS STORAGE FULL| EMP  |    14 |   448 |     3   (0)| 00:00:01 |
|  14 |   SORT AGGREGATE             |      |     1 |    39 |            |          |
|* 15 |    HASH JOIN                 |      |    14 |   546 |     7  (15)| 00:00:01 |
|  16 |     TABLE ACCESS STORAGE FULL| DEPT |     4 |    52 |     3   (0)| 00:00:01 |
|  17 |     TABLE ACCESS STORAGE FULL| EMP  |    14 |   364 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   7 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  11 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
  15 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         56  consistent gets
          0  physical reads
          0  redo size
       1354  bytes sent via SQL*Net to client
        534  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         18  rows processed



4. GROUPING SETS 함수

Useful Information

,
GROUP BY SQL 문장을 여러 번 반복하지 않아도 원하는 결과를 쉽게 얻을 수 있게 되었다.
GROUPING SETS에 표시된 인수들에 대한 개별 집계를 구할 수 있으며, 이때 표시된 인수들 간에는 계층 구조인 ROLLUP과는 달리 평등한 관계이므로 인수의 순서가 바뀌어도 결과는 같다.
그리고 GROUPING SETS 함수도 결과에 대한 정렬이 필요한 경우는 ORDER BY 절에 명시적으로 정렬 칼럼이 표시가 되어야 한다.

SQL> SELECT DECODE(GROUPING(DNAME), 1, 'All Departments', DNAME) AS DNAME, 
            DECODE(GROUPING(JOB), 1, 'All Jobs', JOB) AS JOB, 
            COUNT(*) "Total Empl", SUM(SAL) "Total Sal" 
     FROM EMP, DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO
     GROUP BY GROUPING SETS (JOB, DNAME); 


DNAME           JOB       Total Empl  Total Sal
--------------- --------- ---------- ----------
All Departments CLERK              4       4150
All Departments SALESMAN           4       5600
All Departments PRESIDENT          1       5000
All Departments MANAGER            3       8275
All Departments ANALYST            2       6000
ACCOUNTING      All Jobs           3       8750
RESEARCH        All Jobs           5      10875
SALES           All Jobs           6       9400

8 rows selected.

SQL> SELECT DNAME, JOB, MGR, SUM(SAL) "Total Sal" 
     FROM EMP, DEPT 
     WHERE DEPT.DEPTNO = EMP.DEPTNO 
     GROUP BY GROUPING SETS ((DNAME, JOB, MGR), (DNAME, JOB), (JOB, MGR));

DNAME          JOB              MGR  Total Sal
-------------- --------- ---------- ----------
SALES          CLERK           7698        950
SALES          MANAGER         7839       2850
SALES          SALESMAN        7698       5600
RESEARCH       CLERK           7788       1100
RESEARCH       CLERK           7902        800
RESEARCH       ANALYST         7566       6000
RESEARCH       MANAGER         7839       2975
ACCOUNTING     CLERK           7782       1300
ACCOUNTING     MANAGER         7839       2450
ACCOUNTING     PRESIDENT                  5000
SALES          CLERK                       950
SALES          MANAGER                    2850
SALES          SALESMAN                   5600
RESEARCH       CLERK                      1900
RESEARCH       ANALYST                    6000
RESEARCH       MANAGER                    2975
ACCOUNTING     CLERK                      1300
ACCOUNTING     MANAGER                    2450
ACCOUNTING     PRESIDENT                  5000
               CLERK           7698        950
               CLERK           7902        800
               PRESIDENT                  5000
               CLERK           7788       1100
               CLERK           7782       1300
               SALESMAN        7698       5600
               MANAGER         7839       8275
               ANALYST         7566       6000

27 rows selected.




문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.
  1. 4월 04, 2013

    이재현 says:

    CUBE 랑 ROLLUP 나왔었어요.. 주관식으로다가요..

    CUBE 랑 ROLLUP 나왔었어요.. 주관식으로다가요..

  2. 4월 05, 2013

    이현희 says:

    그럼 직접 작성하는거예요??

    그럼 직접 작성하는거예요??

  3. 4월 06, 2013

    이준우 says:

    http://scidb.tistory.com/search/group%20by%20extension rollup, cube 등 관련 잘 설명되어...

    http://scidb.tistory.com/search/group%20by%20extension

    rollup, cube 등 관련 잘 설명되어 있는 참고자료입니다.