MySQL の GROUP BY 完全に理解した

タイトルを現実世界で言うほどの度量や技術はありませんが、度々 MySQL で ```GROUP BY``` を使用する機会に恵まれたので、同じような部分で躓いている人の助けになるよう記事を書いてみました。

元々は社内向けとして書いたものですが、転載許可が出たので Qiita 向けに加筆・修正した文章です。

# 前書き

個人的な、サイトのシステムを作る上で「ウ゛ッ」となる部分第1位は「決済周り」です。

今回は第6位辺りの話をしましょう。

あなたがエンジニアであれデザイナーであれディレクターであれ、Web制作に携わる者ならば以下のようなテーブルを星の数ほど作ってきたハズです。


|ユーザーID▼ |    名前▲           | メールアドレス▲ |書いた記事数▲ | 獲得イイね数▲ |
|:----------:|:------------------|:------------------|:------------------:|:------------------:|
| 1| 木島 | hoge@example.com | 10 | 5000 |
| 2 | 山田 | fuga@example.com | 1 | 7 |
| 3 | 本田 | piyo@example.com | 3709 | 6446131 | 

さて、バックエンドエンジニアがこのテーブルを見たときに「あぁ………」となるポイントがあります。
それは__書いた記事数__と__獲得イイね数__という項目です。

誤解を恐れずに言えば、大体の Web システムははデータベースを映し出す鏡であることが多いです。
より具体的には __MySQL__ や __PostgreSQL__ 等が裏で動いていることでしょう。

このテーブルにおいておそらくは、__ユーザー ID__ はプライマリーキー、__名前__や__メールアドレス__はプライマリーキーと同じ ```users``` テーブルのフィールドに存在する情報かと思われます。

これだけならなんの問題もありません。単純な ```SELECT``` 文を発行するだけで済みます。

```sql
SELECT * 
FROM users 
WHERE act = 1 
ORDER BY id DESC 
```

問題は残りの二項目、__書いた記事数__と__獲得イイね数__の二つです。

これらの存在で、我々の仕事は倍増、いや、もしかしたら三倍四倍になるかもしれません。

しかし必要とされている以上、実装しないという選択肢はありません。人力ではとても面倒なことを解決するのがエンジニアの本分です。
SQL は恐らく、以下のようになるでしょう。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY users.id DESC 
```

なんとややこしいことでしょう。__39文字__で構成されていた SQL が__261文字__に膨れ上がってしまいました。
__しかし人間はデータを計算するのは嫌がる癖に、計算結果を見るのは大好きです__。

前置きが長くなりましたが、今回は__集計という処理を SQL で実現する 「```GROUP BY```」句__の話をします。

※既に登場していますが、この文章中で使用する SQL は __MySQL__ を想定しています。他の RDB ではエラーになったり、そもそも概念からして間違っている可能性があるので注意してください。

# GROUP BY と集計関数

```GROUP BY``` を使用する際はどんな時でしょう？　それは__欲しいデータが「単一のデータ」だけではなく、「複数のデータを集計した計算結果」も含まれている場合__です。

SQL には様々な「集計関数」と呼ばれるものがあります。
一番馴染み深いのは ```COUNT()``` でしょう。
他にも、最大値と最小値を返す ```MAX()```, ```MIN()```、合計を返す ```SUM()``` や平均を返す ```AVG()``` はよく使う集計関数です。
これらは複数のデータをまとめて一つの値として返してくれます。

先ほどの SQL を改めて見てみましょう。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY users.id DESC 
```

```users``` テーブル以外にも ```posts```, ```favorites``` テーブルが```LEFT JOIN``` で結合されているので、通常であれば ```users``` テーブルのレコードは関連する ```posts * favorites``` テーブルの数だけ重複するハズです。

| users.id | users.name | users.mail | posts.id | favorites.id |
| :------: |  :------- |  :------- |  :------: |  :------: | 
| 1 | 木島 | hoge@example.com | 1 | 1 |
| 1 | 木島 | hoge@example.com | 1 | 2 |
| 1 | 木島 | hoge@example.com | 2 | 3 |
| 1 | 木島 | hoge@example.com | 2 | 4 |
| 2 | 山田 | fuga@example.com | 3 | NULL |
| 2 | 山田 | fuga@example.com | 4 | 5 |
| 2 | 山田 | fuga@example.com | 4 | 6 |
| 2 | 山田 | fuga@example.com | 4 | 7 |
| 2 | 山田 | fuga@example.com | 4 | 8 |
| 2 | 山田 | fuga@example.com | 4 | 9 |
| 2 | 山田 | fuga@example.com | 5 | 10 |
| 3 | 本田 | piyo@example.com | NULL | NULL |

しかし、この SQL には ```GROUP BY users.id``` という一句が存在します。

これは users.id 毎に纏めた情報を1レコードとして返すという意味ですので、この SQL が返すレコードは ```users.id``` で重複することは無くなりました。
```users.name```, ```users.mail``` は ```users.id``` と同じテーブルにあるフィールドなので問題なく取得可能です。

反面、```posts```, ```favorites``` テーブルは一つのレコードに複数のデータが混ざっている状態となります。

| users.id | users.name | users.mail | posts.id | favorites.id | 
| :------: | :------ | :------ | :------ | :------ | 
| 1 | 木島 | hoge@example.com | 1, 2 | 1, 2, 3, 4 |
| 2 | 山田 | fuga@example.com | 3, 4 | 5, 6, 7, 8, 9, 10 |
| 3 | 本田 | piyo@example.com | NULL | NULL |

例として、以下の SQL はエラーとなります。
```users.id``` でグループ化してしまうと ```posts.id``` や ```favorites.id``` は複数件のデータが該当する可能性があり、SQL はどれを返すか分からないためです。

```sql
SELECT users.id, users.name, users.mail, posts.id, favorites.id 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY users.id DESC 
```

なので、```users.id``` と一対一になっていない、この場合は ```posts``` テーブルと ```favorite``` テーブルの結果は集計関数で括る必要があります。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY users.id DESC 
```

| users.id | users.name | users.mail | COUNT(posts.id) | COUNT(favorites.id) | 
| :------: | :------- | :------- | :------: | :-------: | 
| 1 | 木島 | hoge@example.com | 2 | 4 |
| 2 | 山田 | fuga@example.com | 2 | 6 |
| 3 | 本田 | piyo@example.com | 0 | 0 |

```GROUP BY``` と集計関数は切っても切れない関係です。 

```GROUP BY``` を使用してエラーが出てしまう場合は真っ先に ```SELECT``` 句に書いてあるフィールドを疑ってみて下さい。集計関数を使うべきなのに素のフィールドを取得しようとしている場合がほとんどでしょう。
	
# GROUP BY と HAVING

集計関数以外にも ```GROUP BY``` と関係の深いものがあります。

```HAVING``` は ```GROUO BY``` 無しに成立しない句です。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
HAVING COUNT(favorites.id) > 100 
ORDER BY users.id DESC 
```

上記 SQL は無情にも獲得イイね数が100以下のユーザーを結果から弾いてしまいます。
弾くという意味では ```WHERE``` と同じ機能を持っていると言って差し支えないでしょう。

では、一体なぜ ```WHERE``` ではなく ```HAVING``` を使用する必要があるのでしょうか。

その答えは SQL が実行される順番にあります。
SQL は書いた順ではなく、以下の順で実行されます。

> 1. FROM
> 2. ON
> 3. JOIN
> 4. WHERE
> 5. GROUP BY
> 6. WITH CUBE または WITH ROLLUP
> 7. HAVING
> 8. SELECT
> 9. DISTINCT
> 10. ORDER BY
> 11. TOP

[SELECT (Transact-SQL) | Microsoft Docs](https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2012/ms189499(v=sql.110))

```WHERE``` は ```GROUP BY``` より前に実行され、```HAVING``` は後に実行されているのが分かるでしょう。

つまり、集計関数を使用して得られる情報は ```GROUP BY``` によって得られるので、その情報を絞り込みに使用したいのであれば ```WHERE``` のタイミングではなく、```HAVING``` によって行われるべきなのです。
	
# GROUP BY と ORDER BY

ここまでは様々な記事で紹介されているのですが、実務では ```GROUP BY``` と ```ORDER BY``` の合わせ技も頻発します。

ここで気を付けるべきなのは、__```SELECT``` 句に指定できない情報は ```ORDER BY``` 句にも指定できない__という点です。

例えば、以下の例は__失敗します__。

```SELECT``` 句で使用できない ```posts.id``` でソートしているためです。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY posts.id DESC 
```

このようなコードを書いてしまう場合、きっと頭の中では「一番最近記事を書いた人順に並べたい」という構想があったことでしょう。

恐らく、その要望を正しく SQL にすると以下のようになります。

```sql
SELECT users.id, users.name, users.mail, COUNT(posts.id) AS post_number, COUNT(favorites.id) AS favorites_number 
FROM users 
LEFT JOIN posts 
ON users.id = posts.user
LEFT JOIN favorites 
ON users.id = favorites.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY MAX(posts.id) DESC 
```

大抵の場合、```posts.id``` は ```AUTO_INCREMENT``` 設定で正の方向へ増えることでしょう。

```ORDER BY``` に集計関数を使うことに違和感を感じるかもしれませんが、ユーザーごとの最新記事は ```posts.id``` が最大の記事のことを指すので```、MAX(posts.id)``` でソートするのが実態に近いと考えられます。

余談ですが、__MySQL 8 系からは 集計関数を通した結果にインデックスを張ることが可能になった__ので、実務で使用する場合は試してみてもいいかもしれません。

[日々の覚書: MySQL 8.0.13の式インデックス](https://yoku0825.blogspot.com/2018/10/mysql-8013_25.html)
	
# GROUP BY と DISTINCT
最後に、```GROUP BY``` と似た作用を持つ ```DISTINCT``` との使い分けについて説明しましょう。

以下のコードは__「削除されていない記事を最低一つは書いているが、```users``` テーブルの情報しか必用ない__」場合に僕が書くであろう SQL です。

```sql
SELECT DISTINCT users.id, users.name, users.mail
FROM users 
INNER JOIN posts 
ON users.id = posts.user
WHERE users.act = 1 
AND posts.act = 1 
ORDER BY users.id DESC 
```

欲しいのは ```users``` テーブルの情報だけですが、絞り込みの条件として ```posts``` テーブルの情報が必要なので ```INNER JOIN``` で結合を行っています。
しかし、このそままでは結果が重複してしまうので ```DISTINCT``` を使用しています。

が、同じ結果は下記のように ```GROUP BY``` でも取得することができます。

```sql
SELECT users.id, users.name, users.mail
FROM users 
INNER JOIN posts 
ON users.id = posts.user
WHERE users.act = 1 
AND posts.act = 1 
GROUP BY users.id 
ORDER BY users.id DESC 
```

どちらがいいかはケースバイケースです。

筆者としては「重複行を弾く」という本来の目的と合致している ```DISTINCT``` を使用する方が好みですが、一方で ```GROUP BY``` を用いた方が高速な場面もあるようです。

あまり速度が変わらない場合は ```DISTINCT``` を、速度に顕著な差がある場合や、後々「記事を二本以上書いている」というような条件も視野に入れる場合は ```GROUP BY``` を採用するなど、臨機応変に対応できるといいでしょう。
	
# 後書き

先述したように、__人間はデータを計算するのは嫌がる癖に、計算結果を見るのは大好きです。__

計算を素早く正確にこなせるのがコンピューターの真骨頂なのですから、Web サービスに集計結果がつきものなのはある意味当然のことでしょう。
やや理解が難しい ```GROUP BY``` ですが、使う場面は数多く、また大抵は、アプリケーション側での計算の何百何千倍も速く計算結果を返してくれます。

最初は直感的ではないエラーに悩まされることでしょうが、是非使いこなせるようになってクライアントの要望に応えてあげて下さい。