Oracle の SQL で クロス集計を実施

集計関数を使って 少ないステップで高速に集計できます。

以下の データから SQL で jobごと、deptno ごとの SAL列合計を求めます。
（社員の所属している職種(job)毎、部門(deptno)毎の賃金(sal)合計、　職種毎の賃金合計、部門毎の賃金合計、全社員の賃金合計を表示する）

     EMPNO JOB                             DEPTNO        SAL
---------- --------------------------- ---------- ----------
      7369 CLERK                               20        800
      7499 SALESMAN                            30       1600
      7521 SALESMAN                            30       1250
      7566 MANAGER                             20       2975
      7654 SALESMAN                            30       1250
      7698 MANAGER                             30       2850
      7782 MANAGER                             10       2450
      7788 ANALYST                             20       3000
      7839 PRESIDENT                           10       5000
      7844 SALESMAN                            30       1500
      7876 CLERK                               20       1100
      7900 CLERK                               30        950
      7902 ANALYST                             20       3000
      7934 CLERK                               10       1300

結果
JOB                             DEPTNO   SUM(SAL)
--------------------------- ---------- ----------
ANALYST                             20       6000
ANALYST                                      6000
CLERK                               10       1300
CLERK                               20       1900
CLERK                               30        950
CLERK                                        4150
MANAGER                             10       2450
MANAGER                             20       2975
MANAGER                             30       2850
MANAGER                                      8275
PRESIDENT                           10       5000
PRESIDENT                                    5000
SALESMAN                            30       5600
SALESMAN                                     5600
                                    10       8750
                                    20      10875
                                    30       9400
                                            29025

18 rows selected.


この結果を 得るのに４種類のSQL を記述できます。

１、SELECT job, deptno, SUM(sal)   FROM emp GROUP BY job,deptno
UNION ALL
SELECT job, NULL deptno, SUM(sal)  FROM emp GROUP BY job
UNION ALL
SELECT NULL job, deptno, SUM(sal)  FROM emp GROUP BY deptno
UNION ALL
SELECT NULL job, NULL deptno, SUM(sal)  FROM emp
 ORDER BY job,deptno
;

２、SELECT job, deptno, SUM(sal)  FROM emp
 GROUP BY CUBE(job,deptno)
 ORDER BY job,deptno;

３、SELECT job, deptno, SUM(sal)  FROM emp
 GROUP BY ROLLUP(job), ROLLUP(deptno)
 ORDER BY job,deptno;

４、SELECT job,deptno, SUM(sal) FROM emp
 GROUP BY GROUPING SETS (
  (job, deptno),  (job),  (deptno),  ()
 )
 ORDER BY job,deptno;

それぞれの SQL実行計画を 確認すると


SQL1

------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    43 |  1091 |    16  (25)|
|   1 |  SORT ORDER BY       |      |    43 |  1091 |    15  (20)|
|   2 |   UNION-ALL          |      |       |       |            |
|   3 |    HASH GROUP BY     |      |    14 |   448 |     4  (25)|
|   4 |     TABLE ACCESS FULL| EMP  |    14 |   448 |     3   (0)|
|   5 |    HASH GROUP BY     |      |    14 |   266 |     4  (25)|
|   6 |     TABLE ACCESS FULL| EMP  |    14 |   266 |     3   (0)|
|   7 |    HASH GROUP BY     |      |    14 |   364 |     4  (25)|
|   8 |     TABLE ACCESS FULL| EMP  |    14 |   364 |     3   (0)|
|   9 |    SORT AGGREGATE    |      |     1 |    13 |            |
|  10 |     TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)|
------------------------------------------------------------------

Statistics
----------------------------------------------------------
         28  consistent gets
          1  sorts (memory)
         18  rows processed







SQL2

------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   448 |     4  (25)|
|   1 |  SORT GROUP BY       |      |    14 |   448 |     4  (25)|
|   2 |   GENERATE CUBE      |      |    14 |   448 |     4  (25)|
|   3 |    SORT GROUP BY     |      |    14 |   448 |     4  (25)|
|   4 |     TABLE ACCESS FULL| EMP  |    14 |   448 |     3   (0)|
------------------------------------------------------------------

Statistics
----------------------------------------------------------
          7  consistent gets
          2  sorts (memory)
         18  rows processed







SQL3

--------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    14 |   448 |     5  (40)|
|   1 |  SORT ORDER BY         |      |    14 |   448 |     5  (40)|
|   2 |   SORT GROUP BY ROLLUP |      |    14 |   448 |     5  (40)|
|   3 |    SORT GROUP BY ROLLUP|      |    14 |   448 |     5  (40)|
|   4 |     TABLE ACCESS FULL  | EMP  |    14 |   448 |     3   (0)|
--------------------------------------------------------------------

Statistics
----------------------------------------------------------
          7  consistent gets
          3  sorts (memory)
         18  rows processed







SQL4

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                       | Rows  | Bytes| Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                            |    14 |   448|    12  (25)|
|   1 |  TEMP TABLE TRANSFORMATION |                            |       |      |            |
|   2 |   MULTI-TABLE INSERT       |                            |       |      |            |
|   3 |    SORT GROUP BY ROLLUP    |                            |    14 |   448|     4  (25)|
|   4 |     TABLE ACCESS FULL      | EMP                        |    14 |   448|     3   (0)|
|   5 |    DIRECT LOAD INTO        | SYS_TEMP_0FD9DF0B2_366DBCE |       |      |            |
|   6 |    DIRECT LOAD INTO        | SYS_TEMP_0FD9DF0B3_366DBCE |       |      |            |
|   7 |   LOAD AS SELECT           | SYS_TEMP_0FD9DF0B3_366DBCE |       |      |            |
|   8 |    SORT GROUP BY ROLLUP    |                            |     3 |    78|     3  (34)|
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9DF0B2_366DBCE |    14 |   364|     2   (0)|
|  10 |   SORT ORDER BY            |                            |    14 |   448|     5  (20)|
|  11 |    VIEW                    |                            |    28 |   896|     4   (0)|
|  12 |     VIEW                   |                            |    28 |   896|     4   (0)|
|  13 |      UNION-ALL             |                            |       |      |            |
|  14 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9DF0B2_366DBCE |    14 |   448|     2   (0)|
|  15 |       TABLE ACCESS FULL    | SYS_TEMP_0FD9DF0B3_366DBCE |    14 |   448|     2   (0)|
---------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          4  recursive calls
         22  db block gets
         21  consistent gets
          4  physical reads
       1360  redo size
          3  sorts (memory)
         18  rows processed