SQL の GROUP BY 句 演算目的と SELECT 句に含められる属性

DB PostgreSQL

プログラマのための SQL 第4版 より

自分用のまとめ

GROUP BY 演算の目的

  • 複数行を集約キーでグループごとにまとめ
    各グループの特性を示す行を返すこと
    • 返ってきた行は そのグループの特性を示すものであり(*1)
    • グループに存在する特定行の性質を示すものではない(*2)

GROUP BY 句を使った時 SELECT 句に含められる属性

  • GROUP BY 句の集約キー、集約関数およびスカラ値
    • 取得した属性が「グループ内で単一」でなければいけない(*3)
    • GROUP BY 句の部分集合である(*4)

例

CREATE TABLE populations (
  prefid INTEGER
    NOT NULL,
  cityid INTEGER
    NOT NULL,
  population INTEGER
    NOT NULL,
  PRIMARY KEY(prefid, cityid)
);
SELECT
  *
FROM
  populations;

  prefid | cityid | population 
 --------+--------+------------
       1 |      1 |        500
       1 |      2 |        300
       1 |      3 |        400
       2 |      1 |        700
       2 |      2 |        800
       2 |      3 |        900
       3 |      1 |        100
       3 |      2 |       1000

(*1) 返ってきた行はそのグループの特性を示すもの

SELECT
  prefid,
  SUM(population)
FROM
  populations
GROUP BY
  prefid;

prefid | sum  
--------+------
     1 | 1200
     3 | 1100
     2 | 2400
  • prefid = 1 の行は prefid = 1 グループの特性を示すもの
  • prefid = 2 〃 prefid = 2 〃
  • prefid = 3 〃 prefid = 3 〃
prefid | cityid | population 
--------+--------+------------
     1 |      1 |        500
     1 |      2 |        300
     1 |      3 |        400
============================== prefid = 1 のグループ

     2 |      1 |        700
     2 |      2 |        800
     2 |      3 |        900
============================== prefid = 2 のグループ

     3 |      1 |        100
     3 |      2 |       1000
============================== prefid = 3 のグループ

(*2)グループに存在する特定行の性質を示すものではない

SELECT
  prefid,
  cityid,
  SUM(population)
FROM
  poupulations
GROUP BY
  prefid;

=> error
  • prefid = 1 のグループは、どの cityid を基にして「グループの特性」とすれば良いか
  • prefid = 2 〃
  • prefid = 3 〃
    • → RDBMS が判断できないのでエラー
prefid | cityid | population 
--------+--------+------------
     1 |      1 |        500
     1 |      2 |        300
     1 |      3 |        400
============================== prefid = 1 のグループ

     2 |      1 |        700
     2 |      2 |        800
     2 |      3 |        900
============================== prefid = 2 のグループ

     3 |      1 |        100
     3 |      2 |       1000
============================== prefid = 3 のグループ

(*3)取得した属性が「グループ内で単一」でなければいけない

SELECT
  prefid,
  SUM(population) -- 集約関数も単一の値としてみなすことができる
FROM
  populations
GROUP BY
  prefid;

  prefid | sum  
  --------+------
       1 | 1200
       3 | 1100
       2 | 2400
  • prefid = 1 のグループにて prefid = 1, sum = 1200 はそれぞれ単一の値
  • prefid = 2 〃　　　　　 prefid = 2, sum = 1100 〃
  • prefid = 3 〃　　　　　 prefid = 3, sum = 2400 〃

(*4)GROUP BY 句の部分集合である

SELECT
  prefid,
  SUM(population)
FROM
  populations
GROUP BY
  prefid, cityid;

  prefid | sum  
 --------+------
       1 |  400
       2 |  800
       3 |  100
       1 |  300
       2 |  900
       2 |  700
       1 |  500
       3 | 1000
  • 集合(prefid) は 集合(prefid, cityid) の部分集合
    • 従って上記の SELECT 文は error にならない