10-04.集計関数（Aggregate function）【CUBE】

■CUBE operator
　-下記、ROLLUPの例にて部署別JOBに該当する給与と社員数を出力した。

SQL> SELECT b.dname, a.job, SUM(a.sal) sal,
  2         COUNT(a.empno) emp_count
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY ROLLUP(b.dname, a.job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
SALES                          8150          5   ---> SALES部署の給与合計と社員数
RESEARCH       CLERK           1900          2
RESEARCH       ANALYST         6000          2
RESEARCH       MANAGER         2975          1
RESEARCH                      10875          5   ---> REASERCH部署の給与合計と社員数
ACCOUNTING     CLERK           1300          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING                     8750          3   ---> ACCOUNTING部署の給与合計と社員数
                              27775         13   ---> 全体給与合計と社員数

しかし、部署別で各JOBの給与と社員数また、各JOB別の給与合計と社員数を見るためには
2つのROLLUPを使用する必要がある。

SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_count
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY ROLLUP(b.dname, a.job)
  5  UNION
  6  SELECT ' ', job, SUM(sal) sal, COUNT(empno) emp_count
  7  FROM emp
  8  GROUP BY ROLLUP(job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
               ANALYST         6000          2　--->2回目のROLLUP結果
               CLERK           4150          4
               MANAGER         8275          3
               PRESIDENT       5000          1
               SALESMAN        4350          3
                              27775         13  
ACCOUNTING     CLERK           1300          1　--->ここから1回目のROLLUP結果
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1
ACCOUNTING                     8750          3
RESEARCH       ANALYST         6000          2
RESEARCH       CLERK           1900          2
RESEARCH       MANAGER         2975          1
RESEARCH                      10875          5
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
SALES                          8150          5
                              27775         13

CUBEを使用して簡単なSQL文で上記結果が得られる

SQL> SELECT b.dname, a.job, SUM(a.sal) sal, COUNT(a.empno) emp_coun
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY CUBE(b.dname, a.job);

DNAME          JOB              SAL  EMP_COUNT
-------------- --------- ---------- ----------
                              27775         13  --->JOB別の給与総計と社員数
               CLERK           4150          4
               ANALYST         6000          2
               MANAGER         8275          3
               SALESMAN        4350          3
               PRESIDENT       5000          1
SALES                          8150          5
SALES          CLERK            950          1
SALES          MANAGER         2850          1
SALES          SALESMAN        4350          3
RESEARCH                      10875          5
RESEARCH       CLERK           1900          2
RESEARCH       ANALYST         6000          2
RESEARCH       MANAGER         2975          1
ACCOUNTING                     8750          3
ACCOUNTING     CLERK           1300          1
ACCOUNTING     MANAGER         2450          1
ACCOUNTING     PRESIDENT       5000          1

--CUBEはCross-Tabに対するSummaryを出力する
--ROLLUP結果のItem Total値とColumn Total値を出力できる

■GROUPING()関数
　-GROUPING関数はROLLUP、CUBE両方で使用できる
　-GROUPING関数は該当ROWがGROUP BYにより算出されたROWの場合0を返し、
　　ROLLUPかCUBEにより算出されたROWの場合は1を返す。
　
　※該当ROWが結果集合により算出されたデータなのか、ROLLUPやCUBEにより算出された
　　データかを見分けるための関数！

SQL> SELECT b.dname, a.job, SUM(a.sal), COUNt(a.empno) emp_count,
  2         GROUPING(b.dname) "D", GROUPING(a.job) "S"
  3  FROM emp a, dept b
  4  WHERE a.deptno = b.deptno
  5  GROUP BY CUBE(b.dname, a.job);

DNAME          JOB       SUM(A.SAL)  EMP_COUNT          D          S
-------------- --------- ---------- ---------- ---------- ----------
                              27775         13          1          1
               CLERK           4150          4          1          0
               ANALYST         6000          2          1          0
               MANAGER         8275          3          1          0
               SALESMAN        4350          3          1          0
               PRESIDENT       5000          1          1          0
SALES                          8150          5          0          1
SALES          CLERK            950          1          0          0
SALES          MANAGER         2850          1          0          0
SALES          SALESMAN        4350          3          0          0
RESEARCH                      10875          5          0          1
RESEARCH       CLERK           1900          2          0          0
RESEARCH       ANALYST         6000          2          0          0
RESEARCH       MANAGER         2975          1          0          0
ACCOUNTING                     8750          3          0          1
ACCOUNTING     CLERK           1300          1          0          0
ACCOUNTING     MANAGER         2450          1          0          0
ACCOUNTING     PRESIDENT       5000          1          0          0