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

10-01.集計関数（Aggregate function）【集計関数とは？ 】

■集計関数とは？
　-複数行またはテーブル全体行から１つの結果値を返す関数
　-GROUP BYを使用してグループ毎、１つの結果に纏められる。
　-HAVINGを使用して集計関数を利用した条件比較ができる。
　-MIN、MAX関数はすべてのタイプに利用できる。
　-一般的に使われている集計関数はAVG、COUNT、MAX、MIN、SUM等がある。

■COUNT：検索された行の数を返す。

SQL> SELECT COUNT(deptno) FROM dept;

COUNT(DEPTNO)
-------------
            4

■MAX：コラム値の中で最大値を返す

--salコラム値の中で最大値を返す
SQL> SELECT MAX(sal) salary FROM emp;

    SALARY
----------
      5000

■MIN：コラム値の中で最小値を返す

--salコラム値の中で最小値を返す
SQL> SELECT MIN(sal) salary FROM emp;

    SALARY
----------
       800

■AVG：平均値を返す

--30部署の社員平均給与を小数点1桁以下で四捨五入
SQL> SELECT ROUND(AVG(sal),1) salary
  2  FROM emp
  3  WHERE deptno = 30;

    SALARY
----------
      1630

■SUM：検索されたコラムの合計を返す。

--30部署の社員給与の合計
SQL> SELECT SUM(sal) salsry
  2  FROM emp
  3  WHERE deptno = 30;

    SALSRY
----------
      8150

■STDDEV：標準偏差を返す。

--30部署社員の給与標準偏差を返す
SQL> SELECT ROUND(STDDEV(sal), 3) salary
  2  FROM emp
  3  WHERE deptno = 30;

    SALARY
----------
   726.808

■集計関数の例

--部署別社員数、最大給与、最小給与、給与合計、平均給与を検索する
QL> SELECT deptno 部署番号, COUNT(*) 社員数,
 2         MAX(sal) 最大給与, MIN(sal) 最小給与,
 3         SUM(sal) 給与合計, ROUND(AVG(sal)) 平均給与
 4  FROM emp
 5  GROUP BY deptno
 6  ORDER BY SUM(sal) DESC;

 部署番号     社員数   最大給与   最小給与   給与合計   平均給与
--------- ---------- ---------- ---------- ---------- ----------
       20          5       3000        800      10875       2175
       10          3       5000       1300       8750       2917
       30          5       2850        950       8150       1630