Csol本部所属の福濵です。2023年4月に入社し、SQLに関する知識を基礎から学んでいます。
本記事では、GROUP BY句の概要と実装について紹介します。
概要
GROUP BY句とは
GROUP BY句は、テーブル内で選択された列のうち、同じ値を持つ行をまとめてグループ化する機能を持ちます。
これにより出力の冗長性の排除や、集約関数（SUM、AVGなど）を用いてグループごとの集約値を取得することができます。
基本構文
以下がGROUP BY句の構文です。
SELECT select_list
FROM table_name
[WHERE ...]
GROUP BY colum_name ;
[HAVING ...]
WHERE句でテーブルにフィルタをかけてからグループ化を行うことや、グループ化した後にHAVING句で不要なグループを除くことも可能です。
注意点
GROUP BY句では集約式を除き、グループ化に指定されていない列は参照することができません。
以下のテーブルで考えてみます。
学籍名簿テーブルの「性別」でグループ化した場合、他の「学籍番号」、「名前」、「点数」といった列は参照できなくなります。同じ性別からどの行の値を出力すればよいのか曖昧になるためです。
以下は「性別」でグループ化し、「点数」を参照しようとした例です。参照する点数が性別ごとに複数あるため、結果を出力させることができません。
実際にクエリを実行すると、以下のエラーが出力されます。
ERROR: 列"学籍名簿.点数"はGROUP BY句で指定するか、集約関数内で使用しなければなりません
行 1: SELECT 性別, 点数
点数を出力させるためには、1,性別と一緒にグループ化するか、2,集約関数を用いて出力させる方法があります。
1. 一緒にグループ化させる際は、性別と点数のペアをひとつの値（ユニークなグループの識別子）とみなします。今回は重複する値がないため、GROUP BYを実行しても変化はありません。
2. 集約関数を用いて表示させる際は、グループ化に対応した点数の値の範囲で集約されます。以下はAVG関数を使用した例です。
グループ化せずにAVG関数を実行すると、指定した列の全体の平均値を参照しますが、グループ化したテーブルに対しAVG関数を実行すると指定した列のグループごとの平均値を参照することができます。
グループ化していない場合
「性別」をグループ化した場合
具体的な実装については次章で紹介します。
実装
以下の会員情報テーブル(members)を使用し、GROUP BY句を実装します。
環境はPostgreSQL(バージョン16)です。
| id | name | age | address | point |
|---|---|---|---|---|
| 1 | tanaka | 25 | 東京都 | 500 |
| 2 | takahashi | 40 | 神奈川県 | 100 |
| 3 | suzuki | 27 | 東京都 | 1000 |
| 4 | watanabe | 33 | 埼玉県 | 300 |
| 5 | ito | 35 | 千葉県 | 800 |
| 6 | sato | 44 | 埼玉県 | 300 |
| 7 | tanaka | 56 | 東京都 | 700 |
| 8 | yamada | 28 | 神奈川県 | 400 |
| 9 | hayashi | 38 | 神奈川県 | 900 |
| 10 | fujioka | 35 | 千葉県 | 500 |
addressをグループ化
addressを指定し都道府県をグループ化します。
SELECT address
FROM members
GROUP BY address ;
これによりaddress内に何パターンのデータが入っているのかがわかります。
複数の列をグループ化
GROUP BY句では複数の列を指定しグループ化することができます。
ここではnameとaddressをグループ化します。
SELECT name, address
FROM members
GROUP BY name, address ;
複数の列をグループ化に指定すると、指定した列の値の組み合わせをひとつの値とみなしてデータが出力されます。
今回は「'tanaka', '東京都'」のデータのみ重複したため、そこだけがひとつにまとめられました。
集約関数を使用
GROUP BY句で指定した列以外の列は集約関数を使用し参照することができます。
addressをグループ化し、データ件数と、ageの平均値を出力します。
SELECT address, count(address), round(avg(age), 1)
FROM members
GROUP BY address ;
集約関数を組み合わせることでグループ化させた列の各データに対応した数値を出力させることができます。
これにより各都道府県の会員人数と、平均年齢を参照することができました。
WHERE句、HAVING句との併用
WHERE句とHAVING句はどちらも指定した検索条件と照合し、結果が真の行を出力する機能を持ちますが、処理のタイミングが異なります。
SELECT文を使用したSQL文の場合、以下の順で処理されます。
SELECT→FROM→WHERE→GROUP BY→HAVING
WHERE句がFROM句で処理されたテーブルに対し処理を行うのに対し、HAVING句はGROUP BY句で処理されたテーブルに対し処理を行います。
そのため、HAVING句はGROUP BYで作成したグループごとの集約関数結果の値を使用して、検索条件を指定し照合できるという特徴があります。
以下はageが40以下のテーブルのaddressをグループ化し、グループごとのデータ件数が2以上のデータを出力した結果です。
SELECT address, COUNT(address), ROUND(AVG(age), 1)
FROM members
WHERE age <= 40
GROUP BY address
HAVING count(address) >= 2;
各都道府県の40歳以下の会員人数が2人以上のデータが出力されました。
このように、WHERE句はグループ化する前にテーブルを整理し、HAVING句ではグループ化後に集約関数を用いてさらに不要なデータを除くことができます。
試しにWHERE句で集約関数を使用してみます。
SELECT address, COUNT(address), ROUND(AVG(age), 1)
FROM members
WHERE COUNT(address) >= 3
ERROR: WHEREでは集約関数を使用できません
行 3: WHERE COUNT(address) >= 3
WHERE句では集約関数を使用できない旨のエラーが出力されました。
GROUP BYでグループ化したテーブルに対し集約関数を使用したい場合は、HAVING句を使用する必要があります。
また、グループ化せずにWHERE句内で集約関数を使用したい場合はサブクエリ（副問い合わせ）を用いることで処理ができます。
おわりに
本稿では、GROUP BY句の概要と実装についてご紹介しました。
GROUP BY句はSQLを扱う上での基礎知識となります。初学者の方の参考になれば幸いです。
