GROUP BY句
仕様
テーブルを全体集合とした場合に、指定した列に基づいて部分集合を作る。
この指定する列を集約キーと呼ぶ。
集約キーは複数の列を指定することも可能。
構文
GROUP BY
<colume_name1>, <column_name2>, ...
例
GROUP BYを使う場合
県別性別人口テーブルを考える(1=男, 2=女)。
pop_sex
| pref_name | sex | population |
|---|---|---|
| 東京 | 1 | 250 |
| 東京 | 2 | 250 |
| 神奈川 | 1 | 200 |
| 神奈川 | 2 | 100 |
| 福岡 | 1 | 150 |
| 福岡 | 2 | 100 |
| 埼玉 | 1 | 100 |
| 埼玉 | 2 | 150 |
| 長崎 | 1 | 20 |
| 長崎 | 2 | 30 |
| 千葉 | 1 | 120 |
| 千葉 | 2 | 130 |
| 山形 | 1 | 50 |
| 山形 | 2 | 50 |
県名(pref_name)ごとの人口が欲しい場合にGROUP BY句を用いる。
SELECT
pref_name,
SUM(population) as pop
FROM
pop_sex
GROUP BY
pref_name
;
| pref_name | pop |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
| 福岡 | 250 |
| 埼玉 | 250 |
| 長崎 | 50 |
| 千葉 | 250 |
| 山形 | 100 |
県名ごとの部分集合に分割(GROUP BY)された後、そのグループに対してSUM関数が適用される。
GROUP BYを使わない場合
GROUP BYを削除した以下のSQLを実行した場合では、各行にテーブル全体の集約値が取得される。
(実際はSQLの評価順序の関係でエラーになるので、スカラサブクエリで書く必要があります)
SELECT
pref_name,
SUM(population) as pop
FROM
pop_sex
;
| pref_name | pop |
|---|---|
| 東京 | 1,700 |
| 神奈川 | 1,700 |
| 福岡 | 1,700 |
| 埼玉 | 1,700 |
| 長崎 | 1,700 |
| 千葉 | 1,700 |
| 山形 | 1,700 |
集約キーにNULLが含まれている場合
NULLという一つの部分集合として集約される。
pop_sex
| pref_name | sex | population |
|---|---|---|
| 東京 | 1 | 250 |
| 東京 | 2 | 250 |
| 神奈川 | 1 | 200 |
| 神奈川 | 2 | 100 |
| NULL | 1 | 150 |
| 福岡 | 2 | 100 |
| 埼玉 | 1 | 100 |
| NULL | 2 | 150 |
SELECT
pref_name,
SUM(population) as pop
FROM
pop_sex
GROUP BY
pref_name
;
| pref_name | pop |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
| 福岡 | 100 |
| 埼玉 | 100 |
| NULL | 300 |
GROUP BY句の注意点
SELECT句に指定可能な列
- 定数
- 集約関数
- GROUP BY句で指定した列名(集約キー)
SELECT句で付けた別名は使えない
ASで別名を付けることができるが、これは参照できない。
理由については単純で評価順序がGROUP BYの方がSELECTよりも優先であるため。
つまりSELECTで付けようと思っている名前が、GROUP BYの評価時点では存在しない。
SQLの評価順序
WHERE句に集約関数を書く
先ほどの例で福岡の埼玉の人口が250であった。
これを利用して人口が250である県名を表示したいと思って、以下のようなSQLを記述してもエラーとなる。
SELECT
pref_name,
SUM(population) as pop
FROM
pop_sex
WHERE
SUM(population) = 250
GROUP BY
pref_name
;
WHERE句はあくまでも単体レコードに対する条件指定。
SUMなどの集約関数を書けるのは、SELECT句とHAVING句、またORDER BY句のみ。
DISTINCTのような使い方をする
重複を削除する目的でGROUP BYを用いない。
GROUP BYはあくまでも集約した結果を求めたい場合に用いる。
重複を削除したい場合はDISTINCTを用いる。
HAVING句
仕様
集合に対して条件を指定してデータを絞り込む場合に用いる。
GROUP BY句によってグループ化された部分集合に対しても同様。
構文
GROUP BY
<colume_name1>, <column_name2>, ...
HAVING
<condition>
例
県別性別人口テーブルを考える(1=男, 2=女)。
pop_sex
| pref_name | sex | population |
|---|---|---|
| 東京 | 1 | 250 |
| 東京 | 2 | 250 |
| 神奈川 | 1 | 200 |
| 神奈川 | 2 | 100 |
| 福岡 | 1 | 150 |
| 福岡 | 2 | 100 |
| 埼玉 | 1 | 100 |
| 埼玉 | 2 | 150 |
| 長崎 | 1 | 20 |
| 長崎 | 2 | 30 |
| 千葉 | 1 | 120 |
| 千葉 | 2 | 130 |
| 山形 | 1 | 50 |
| 山形 | 2 | 50 |
SELECT
pref_name,
SUM(population) as pop
FROM
pop_sex
GROUP BY
pref_name
HAVING
SUM(population) >= 300
;
| pref_name | pop |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
県名ごとの部分集合に分割(GROUP BY)された後、そのグループに対してSUM関数が適用され、その値を条件に絞り込み(HAVING)を行っている。
HAVING句を考えるときは、一度集約が終わった段階の中間テーブルを想像するとわかりやすい(以下例)。
| pref_name | SUM(population) |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
| 福岡 | 250 |
| 埼玉 | 250 |
| 長崎 | 50 |
| 千葉 | 250 |
| 山形 | 100 |
これに対して、SUM(population) >= 300であるような行のみを表示する。
| pref_name | SUM(population) |
|---|---|
| 東京 | 500 |
| 神奈川 | 300 |
HAVING句の注意点
HAVING句に指定可能な列
- 定数
- 集約関数
- GROUP BY句で指定した列名(集約キー)
WHERE句に書けるものをHAVING句に書かない
WHERE句に書けるのはGROUP BY句で使っている集約キーです。
理由は単純明快でパフォーマンスに差があるため。
WHERE句の方がHAVING句よりも先に評価されるため、絞り込みが早い。
つまりそれ以降のステップで扱うレコード数が少ない。
GROUP BYによるグルーピングをする際はソートが行われるが、なるべく少ないレコードに対してソートをすることでパフォーマンス向上が見込める。
またWHERE句ではインデックスが利用できるが、中間テーブルに対してはインデックス自体が存在しないため、その部分も大きくパフォーマンスに影響を与える。
SQLの評価順序
またWHERE句はレコードに対する条件を指定し、HAVING句は部分集合に対する条件を指定する役割を持つため。