10-02.集計関数（Aggregate function）【GROUP BY、 HAVING、GROUPING SETS 】

■GROUP BY
　-GROUP BYはデータを条件付きのグループとして設定できる
　-設定するグループのコラム名をSELECTとGROUP BYの後に追加すれば良い

--部署別の社員数検索
SQL> SELECT '2013年' year, deptno 部署番号, count(*) 社員数
  2  FROM emp
  3  GROUP BY deptno
  4  ORDER BY COUNT(*) DESC;

YEAR     部署番号     社員数
------ ---------- ----------
2013年         30          5
2013年         20          5
2013年         10          3

下記例は部署別にグループ化し、部署番号、人数、給与の平均、給与の合計を検索

SQL> SELECT deptno, COUNT(*), ROUND(AVG(sal)) "給与平均",
  2         ROUND(SUM(sal)) "給与合計"
  3  FROM emp
  4  GROUP BY deptno;

    DEPTNO   COUNT(*)   給与平均   給与合計
---------- ---------- ---------- ----------
        30          5       1630       8150
        20          5       2175      10875
        10          3       2917       8750

下記例は業務別にグループ化して業務、人数、平均給与、最高給与、最低給与、合計を検索

SQL> SELECT job "業務", COUNT(empno) "人数", ROUND(AVG(sal),1) "平均給与",
  2         MAX(sal) "最高給与", MIN(sal) "最低給与",
  3         SUM(sal) "給与合計"
  4  FROM emp
  5  GROUP BY job;

業務            人数   平均給与   最高給与   最低給与   給与合計
--------- ---------- ---------- ---------- ---------- ----------
CLERK              4     1037.5       1300        800       4150
SALESMAN           3       1450       1600       1250       4350
PRESIDENT          1       5000       5000       5000       5000
MANAGER            3     2758.3       2975       2450       8275
ANALYST            2       3000       3000       3000       6000

6行が選択されました。

　-GROUP BYは集計関数無しでも使える（DISTINCTと似た感じ…）
　-下記例はGROUP BYのグループ化のみの役割

SQL> SELECT deptno
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO
----------
        30
        20
        10

■DISTINCTとGROUP BY
　-DISTINCTは主にUNIQUE（重複解消）なコラム、レ-コードを検索する場合使用する。
　-GROUP BYはデータをグループ化して結果を検索する時に使用する。
　-DISTINCTでGROUP BYも処理できるクエリ―もある。
　-２つともOracle9iまではsortを利用しデータを作ったが、Oracle10gからはHashを利用して処理している。

--DISTINCTでのUNIQUE化処理
SQL> SELECT DISTINCT deptno FROM emp;

    DEPTNO
----------
        30
        20
        10


--GROUP BYでのUNIQUE化処理
SQL> SELECT deptno FROM emp GROUP BY deptno;

    DEPTNO
----------
        30
        20
        10

--結果は同じ！

しかし、GROUP BYとDISTINCTは各自特有の機能がある

集計関数を使用して特定グループに区分する時はGROUP BY、特定グループに区分しないで重複したデータを除去する場合はDISTINCTを使用するのが望ましい。

--下記の例はDISTINCTを使用するのが効率的
SQL> SELECT COUNT(DISTINCT d.deptno) "重複除去数",
  2         COUNT(d.deptno) "全体数"
  3  FROM emp e, dept d
  4  WHERE e.deptno = d.deptno;

重複除去数     全体数
---------- ----------
         3         13


--集計関数が必要な場合はGROUP BYを使用する
SQL> SELECT deptno, MIN(sal)
  2  FROM emp
  3  GROUP BY deptno;

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        20        800
        10       1300

■HAVING
　-WHEREでは集計関数を使用出来ない
　-HAVINGは集計関数を持って条件比較をする時に使用する
　-HAVINGはGROUP BYと一緒に使用される

下記例は社員数が3名を超える部署と社員数を検索する例

SQL> SELECT b.dname, COUNt(a.empno) "社員数"
  2  FROM emp a, dept b
  3  WHERE a.deptno = b.deptno
  4  GROUP BY dname
  5  HAVING COUNT(a.empno) > 3;

DNAME              社員数
-------------- ----------
RESEARCH                5
SALES                   5

下記例は給与合計が5000を超えるJOBに関してJOBと給与の合計を検索する例、
但しSALESは除外して給与合計の降順で整列した。

SQL> SELECT job, SUM(sal) "給与合計"
  2  FROM emp
  3  WHERE job != 'SALES'　　　　　　--SALESは除外
  4  GROUP BY job　　　　　　　　　　--業務別にGROUP BY
  5  HAVING SUM(sal) > 5000　　　　　--給与合計が5000を超える条件
  6  ORDER BY SUM(sal) DESC;　　　　 --給与合計の降順で整列

JOB         給与合計
--------- ----------
MANAGER         8275
ANALYST         6000

■GROUPING SETS
　-GROUPING SETS関数はGROUP BYの拡張した形でGROUP BYに複数のグループ化条件を設定できる。
　-GROUPING SETS関数の結果は各GROUP条件を実施した結果をUNION ALLした結果と同じ
　-GROUPING SETS関数でUNION ALL等を使用して複雑に作成されたSQL文をシンプルにまとめられる。

下記はGROUPING SETS関数とGROUP BY, UNION ALLを使用して同じ結果を出力する例

--GROUPING SETS使用
SQL> SELECT deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY GROUPING SETS(deptno, job);

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        30                 8150
        20                10875
        10                 8750
           CLERK           4150
           SALESMAN        4350
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000


--GROUP BYとUNION ALL使用
SQL> SELECT NULL deptno, job, SUM(sal)
  2  FROM emp
  3  GROUP BY job
  4  UNION ALL
  5  SELECT deptno, NULL job ,SUM(sal)
  6  FROM emp
  7  GROUP BY deptno;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
           CLERK           4150
           SALESMAN        4350
           PRESIDENT       5000
           MANAGER         8275
           ANALYST         6000
        30                 8150
        20                10875
        10                 8750