SQL DML：集計(グループ化)
目次
グループ化
概要
指定列における、各行の同じ値でグループを作成。
更にグループ関数を用いる事でグループ毎の集計を行う事もできる。
グループ関数については「SQLグループ関数」参照
| EMPNO | ENAME | DEPTNO | SAL | COMM | HIREDATE | CITYNAME |
| 1 | ‘C#’ | 1 | 2000000 | 1000000 | 2000/1/1 | ‘西宮’ |
| 2 | ‘C++’ | 2 | 1000000 | 2000000 | 2000/1/1 | ‘西宮’ |
| 3 | ‘VB.NET’ | 3 | 1000000 | 1000000 | 2000/1/1 | ‘西宮’ |
| 4 | ‘VB6’ | 2 | 1000000 | 5000000 | 2000/1/1 | ‘西宮’ |
| 5 | ‘JAVA’ | 3 | 1500000 | 1000000 | 2000/1/1 | ‘西宮’ |
| 6 | ‘JS’ | 1 | 2000000 | 1000000 | 2000/1/1 | ‘神戸’ |
| 7 | ‘PERL’ | 3 | 1000000 | 100000 | 2000/1/1 | ‘神戸’ |
CITYNAMEのグループ化
SELECT CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
CITYNAME
－－－－－
神戸
西宮
DEPTNOのグループ化
SELECT DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
DEPTNO
－－－－－
1
2
3
グループ関数
CITYNAMEのグループ化
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME;
AVG CITYNAME
－－－－－ －－－－－
1,500,000 神戸
1,300,000 西宮
DEPTNOのグループ化
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO;
AVG DEPTNO
－－－－－ －－－－－
2,000,000 1
1,000,000 2
1,166,667 3
グループ列の省略
SELECT TO_CHAR(AVG(SAL),'99,999,999') AS AVG FROM EMPLOYEE;
AVG
－－－－－
1,357,143
グループ化する列を指定しない場合、グループ化せずに集計のみを行う。
各行でグループ化しているとも言える。
グループ結果の条件付き抽出
SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, CITYNAME FROM EMPLOYEE GROUP BY CITYNAME HAVING AVG(SAL) >= 1500000;
AVG CITYNAME
－－－－－ －－－－－
1,500,000 神戸
SELECT TO_CHAR(AVG(SAL),’99,999,999′) AS AVG, DEPTNO FROM EMPLOYEE GROUP BY DEPTNO HAVING AVG(SAL) >= 1500000;
AVG DEPTNO
－－－－－ －－－－－
2,000,000 1
複数列のグループ化
SELECT CITYNAME, DEPTNO FROM EMPLOYEE
ORDER BY CITYNAME, DEPTNO;
CITYNAME DEPTNO
－－－－－ －－－－－
神戸 1
神戸 3
西宮 1
西宮 2
西宮 2
西宮 3
西宮 3
SELECT CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CITYNAME DEPTNO
－－－－－ －－－－
神戸 1
神戸 3
西宮 1
西宮 2
西宮 3
CITYNAME DEPTNO
－－－－－ －－－－－
神戸 1 → 神戸 1
－－－－－ －－－－－
神戸 3 → 神戸 3
－－－－－ －－－－－
西宮 1 → 西宮 1
－－－－－ －－－－－
西宮 2 → 西宮 2
西宮 2
－－－－－ －－－－－
西宮 3 → 西宮 3
西宮 3
CITYNAMEでグループ分けし(神戸/西宮)、
このグループを更にDEPTNOでグループ分けする(1/2/3)。
グループ化＆集計
SELECT COUNT(*) AS CNT, CITYNAME, DEPTNO FROM EMPLOYEE
GROUP BY CITYNAME, DEPTNO
ORDER BY CITYNAME, DEPTNO;
CNT CITYNAME DEPTNO
－－ －－－－－ －－－－
1 神戸 1
1 神戸 3
1 西宮 1
2 西宮 2
2 西宮 3
CNT CITYNAME DEPTNO
－－ －－－－－ －－－－－
1 神戸 1 → 1 神戸 1
－－ －－－－－ －－－－－
1 神戸 3 → 1 神戸 3
－－ －－－－－ －－－－－
1 西宮 1 → 1 西宮 1
－－ －－－－－ －－－－－
1 西宮 2 → 2 西宮 2
1 西宮 2
－－ －－－－－ －－－－－
1 西宮 3 → 2 西宮 3
1 西宮 3
グループ化SQLの書式(順番)
○：SELECT ～ FROM ～ GROUP BY ～ HAVING ～ ORDER BY ～
○：SELECT ～ FROM ～ HAVING ～ GROUP BY ～ ORDER BY ～
GROUP BY とHAVING はどちらが先でも良い
×：SELECT ～ FROM ～ ORDER BY ～ GROUP BY ～ HAVING ～
×：SELECT ～ FROM ～ GROUP BY ～ ORDER BY ～ HAVING ～
ORDER BY は最後
列単位のGROUP化
COUNT() OVER()
※簡易GROUP BY
COUNT( A ) OVER( PARTITION BY A,B,C ) AS SEQ
A,
B
FROM
TESTTABLE
RANK() OVER()
※順位付け
–ランキング（担当者別に売上額の高い順にソートして順位を付ける）
RANK() OVER(ORDER BY ～ DESC, ORDER BY ～ ASC) AS 'ランク',
～
FROM
～
ORDRER BY
'ランク'
RANK() OVER( ORDER BY SUM(Uriagegaku) DESC ) AS 'ランク',
TantosyaCD AS '担当者',
SUM(Uriagegaku) AS '売上額'
FROM
TESTTABLE
GROUP BY
TantosyaCD
ROW_NUMBER() OVER()
※連番を振る
ROW_NUMBER() OVER( PARTITION BY A,B,C ORDER BY A,B,C ) AS SEQ
A,
B
FROM
TESTTABLE
