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