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

SELECT
  COUNT( A ) OVER( PARTITION BY A,B,C ) AS SEQ
  A,
  B
FROM
  TESTTABLE

RANK() OVER()

※順位付け
–ランキング(担当者別に売上額の高い順にソートして順位を付ける)

SELECT
  RANK() OVER(ORDER BY ~ DESC, ORDER BY ~ ASC) AS 'ランク',
  ~
FROM
  ~
ORDRER BY
  'ランク'
  
SELECT
  RANK() OVER( ORDER BY SUM(Uriagegaku) DESC ) AS 'ランク',
  TantosyaCD AS '担当者',
  SUM(Uriagegaku) AS '売上額'
FROM
  TESTTABLE
GROUP BY
  TantosyaCD

ROW_NUMBER() OVER()

※連番を振る

SELECT
  ROW_NUMBER() OVER( PARTITION BY A,B,C ORDER BY A,B,C ) AS SEQ
  A,
  B
FROM
  TESTTABLE