集約を有効活用してSQLマスターになろう

# GROUP BY句

## 仕様
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1221536/f0774cef-450c-6136-b8bc-7cd51359478a.png)
テーブルを全体集合とした場合に、指定した列に基づいて部分集合を作る。
この指定する列を**集約キー**と呼ぶ。
集約キーは複数の列を指定することも可能。

## 構文
```sql
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句を用いる。

```sql
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の評価順序の関係でエラーになるので、スカラサブクエリで書く必要があります)
```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 |

```sql
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の評価順序](https://qiita.com/kekkepy/items/112f71a2f0b95ec84f51)

### WHERE句に集約関数を書く
先ほどの例で福岡の埼玉の人口が250であった。
これを利用して人口が250である県名を表示したいと思って、以下のようなSQLを記述してもエラーとなる。
```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句によってグループ化された部分集合に対しても同様。

## 構文
```sql
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 |

```sql
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の評価順序](https://qiita.com/kekkepy/items/112f71a2f0b95ec84f51)

またWHERE句はレコードに対する条件を指定し、HAVING句は部分集合に対する条件を指定する役割を持つため。