タイトルを現実世界で言うほどの度量や技術はありませんが、度々 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 文を発行するだけで済みます。
SELECT *
FROM users
WHERE act = 1
ORDER BY id DESC
問題は残りの二項目、書いた記事数と獲得イイね数の二つです。
これらの存在で、我々の仕事は倍増、いや、もしかしたら三倍四倍になるかもしれません。
しかし必要とされている以上、実装しないという選択肢はありません。人力ではとても面倒なことを解決するのがエンジニアの本分です。
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 を改めて見てみましょう。
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 はどれを返すか分からないためです。
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 テーブルの結果は集計関数で括る必要があります。
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 無しに成立しない句です。
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 は書いた順ではなく、以下の順で実行されます。
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE または WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- TOP
SELECT (Transact-SQL) | Microsoft Docs
WHERE は GROUP BY より前に実行され、HAVING は後に実行されているのが分かるでしょう。
つまり、集計関数を使用して得られる情報は GROUP BY によって得られるので、その情報を絞り込みに使用したいのであれば WHERE のタイミングではなく、HAVING によって行われるべきなのです。
GROUP BY と ORDER BY
ここまでは様々な記事で紹介されているのですが、実務では GROUP BY と ORDER BY の合わせ技も頻発します。
ここで気を付けるべきなのは、SELECT 句に指定できない情報は ORDER BY 句にも指定できないという点です。
例えば、以下の例は失敗します。
SELECT 句で使用できない posts.id でソートしているためです。
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 にすると以下のようになります。
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 系からは 集計関数を通した結果にインデックスを張ることが可能になったので、実務で使用する場合は試してみてもいいかもしれません。
GROUP BY と DISTINCT
最後に、GROUP BY と似た作用を持つ DISTINCT との使い分けについて説明しましょう。
以下のコードは「削除されていない記事を最低一つは書いているが、users テーブルの情報しか必用ない」場合に僕が書くであろう 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 でも取得することができます。
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 ですが、使う場面は数多く、また大抵は、アプリケーション側での計算の何百何千倍も速く計算結果を返してくれます。
最初は直感的ではないエラーに悩まされることでしょうが、是非使いこなせるようになってクライアントの要望に応えてあげて下さい。