SQL の GROUP BY 句 演算目的と SELECT 句に含められる属性
自分用のまとめ
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 にならない