【MySQL練習問題】特定のカラムでグルーピングを行い、それぞれのグループの代表レコードを選ぶ

# 問

従業員への給与を管理するための `salaries` テーブルがあります。

| emp_no | salary | from_date | to_date |
| --- | --- | --- | --- |
| 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| ⋮ | ⋮ | ⋮ | ⋮ |
| 10002 | 65828 | 1996-08-03 | 1997-08-03 | 
| 10002 | 65909 | 1997-08-03 | 1998-08-03 | 
| ⋮ | ⋮ | ⋮ | ⋮ |
| 499999 | 74327 | 2000-11-29 | 2001-11-29 | 
| 499999 | 77303 | 2001-11-29 | 9999-01-01 | 

それぞれのレコードにおいて、IDが `emp_no` の従業員に対して `from_date` から `to_date` の期間に付与した給与を `salary` で表しています。

(テーブルのスキーマとレコードは [datacharmer/test_db](https://github.com/datacharmer/test_db) からお借りしました。)

さて、データベーススペシャリストであるあなたのもとに次のような依頼がきました。

```
従業員1人ごとに、その人への給与が最大だった期間を抽出してほしい。
もし、最大の期間が複数ある場合は、全て抽出してほしい。
```

MySQLのクエリのみを用いてこの依頼に応えるには、どうすればよいでしょうか？

# 誤答

## 誤答1. GROUP BYとMAXで給与が最大のレコードを抽出する

`emp_no` で `GROUP BY` し、それぞれのグループの最大値を `MAX` で出すと考えて、次のようなクエリはどうでしょう？

```sql
SELECT
  `emp_no`,
  MAX(`salary`) AS `max_salary`,
  `from_date`,
  `to_date`
FROM `salaries`
GROUP BY `emp_no`
;
```

残念ながら、これは誤答です。

そもそも、MySQL 5.7.5かそれ以降を使っている場合このクエリはエラーになります。
エラーになるのは、 [ONLY_FULL_GROUP_BY](https://dev.mysql.com/doc/refman/5.6/ja/sql-mode.html#sqlmode_only_full_group_by) という `sql_mode` がデフォルトで有効になっているためです。
この設定では、 `GROUP BY` でグルーピングの基準として指定していないカラムを `SELECT` 内に含める場合、 `SUM` や `MAX` などの集約関数を使う必要があるのです。
上のクエリの場合、 `from_date` と `to_date` がグルーピングの基準でないにも関わらず、単独で登場していますね。

もちろん、一時的に `sql_mode` から `ONLY_FULL_GROUP_BY` を取り除けばエラーは出なくなります:

```sql
SET SESSION sql_mode=(
  SELECT REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', '')
);
```

ただ、エラーが出なくなっただけで、誤答であることには変わりません。
依頼に次のような要件があったことを覚えていますか？

```
もし、最大の期間が複数ある場合は、全て抽出してほしい。
```

そうですね。
`GROUP BY` を使ってしまうと、`emp_no` が同じレコードはすべて1つのレコードに集約されてしまうのです。

では、仮に1つしか抽出できなくてもよいとしましょう。依然として誤答です。

根本的な問題は、このクエリで取れる `from_date` と `to_date` が正しいものであるという保証、つまり、給与が最大のレコードに対応する期間であるという保証がどこにもないということです。

「え、 `MAX` で取得したレコードの `from_date` と `to_date` が入るんじゃないの？」

[MySQLのリファレンスマニュアル](https://dev.mysql.com/doc/refman/5.6/ja/group-by-handling.html) によると、`GROUP BY` でグルーピングの対象になっていないカラムを単独で `SELECT` しようとした場合、グループ内のどのレコードから `SELECT` されるかは「不確定」なのです。

実際に僕の手元で実行した結果、 `SELECT` された `from_date` と `to_date` は、給与が最大のレコードのものではなく、グループ内で一番最初に出現したレコードのものでした。

クエリを一見すれば正答に思えるものの、 `GROUP BY` の挙動を正しく理解することで避けられる誤答です。

## 誤答2. ORDER BYしたサブクエリをGROUP BY

まずサブクエリで　`salaries` を降順に並び替えて、そのサブクエリを `emp_no` で `GROUP BY` すると考えて、次のようなクエリはどうでしょう？

```sql
SELECT *
FROM (
  SELECT *
  FROM `salaries`
  ORDER BY `salary` DESC
) AS `ordered_salaries`
GROUP BY `emp_no`
;
```

残念ながら、これも誤答です。

誤答1と同様に、MySQL 5.7.5かそれ以降を使っている場合 `ONLY_FULL_GROUP_BY` を無効にする必要があるので、その前提で続けます。

サブクエリで `salary` で `ORDER BY` をしておけば、外側の `GROUP BY` がそれぞれの`emp_no` ごとに最初に出現したレコード、
つまり、給与が最大のレコードをグループ内の代表レコードとしてくれるはず、という淡い期待が裏切られる形となりました。

誤答1と同様、複数の期間が該当する場合にも1つしか抽出できない上、結果に含まれる `from_date` と `to_date` が正しい保証がありません。
それどころか、 `salary` が最大であるという保証すらありません。

実際に僕の手元で実行した結果、 `SELECT` されたのは `ORDER BY` する前のテーブルでグループ内で一番最初に出現したレコードでした。

# 正答
## 正答1. サブクエリで取得した最大を使ってINNER JOIN 

まずサブクエリで従業員ごとの最大の給与を取得し、その結果と元のテーブルを `INNER JOIN` することで、元のテーブルから給与が最大でないレコードを除外することができます。

```sql
SELECT `salaries`.*
FROM `salaries`
INNER JOIN (
  SELECT
    `emp_no`,
    MAX(`salary`) AS `max_salary`
  FROM `salaries`
  GROUP BY `emp_no`
) AS `max_salaries`
  ON `salaries`.`emp_no` = `max_salaries`.`emp_no`
  AND `salaries`.`salary` = `max_salary`
;
```

まず、内側のクエリで `emp_no` とその従業員の最大の給与を1:1で対応させるテーブルを作っています。
このテーブルと `salaries` テーブルを `INNER JOIN` するのですが、その際の条件が2つありますね。

まず1つ目の条件では、 `salaries` テーブルのそれぞれのレコードについて、作成した対応テーブルから同じ `emp_no` を持つレコードを結合しています。

2つ目の条件を `AND` で合わせることで、`salary` が `emp_no` に対応する `max_salary` に一致する場合のみ結合の対象となるようになります。
結果として、それぞれの `emp_no` で最大の給与でないレコードは結合の対象外となり、結合結果には給与が最大であるレコードだけが残ります。

複数レコードが `max_salary` に一致する場合、その全てが結合の対象になるため、誤答であったような取り残しの心配もありませんね。

## 正答2. 自己結合を使う

自己結合を使うことで、さらにエレガントで実行効率の良いクエリに書き換えられます。

```sql
SELECT `left`.*
FROM `salaries` AS `left`
LEFT OUTER JOIN `salaries` AS `right`
  ON `left`.`emp_no` = `right`.`emp_no`
  AND `left`.`salary` < `right`.`salary`
WHERE `right`.`salary` IS NULL
;
```

サブクエリがなくなったのでお上品な雰囲気になりましたが、同時にやや近寄りがたい雰囲気も出ています。解読してみましょう。

まず1つめの条件では、 `salaries` テーブル内で `emp_no` が同じレコード同士の組み合わせが結合の結果となります。
仮に `emp_no = 10001` のレコードが5件あったとすると、それに対応するレコードは結合結果には `5 × 5 = 25` 件含まれる、という具合ですね。

これに2つ目の条件を `AND` で合わせることで、結合元のレコードの `salary` が結合先のレコードの `salary` より少ない場合のみ結合が成功するという条件が加わります。
よって、 `salary` が最大であるレコードは結合相手が見つからないため、結合に失敗します。
ここで、結合の種類が `LEFT OUTER JOIN` であるため、この場合 `right` のカラムがすべて`NULL` であるという扱いになります。

最後に、 `WHERE` を使って結合に失敗したレコードのみ抽出しています。
結合に失敗したレコードというのは、つまり `salary` が最大であるレコードのことなので、給与が最大のレコードのみが残ります。

正答1と比べると直感的とは言い難いですが、サブクエリが無い分実行効率の向上が期待できます。
レコード数2,844,047件の `salaries` テーブルで試したところ、正答1が平均で565 msかかったところ、正答2は平均で8 msでした。
パフォーマンスは総レコード数、グループ内の平均レコード数、インデックスの有無等に影響するため一概にどちらが良いとは言えないですが、クエリ最適化をする際のために頭の片隅に置くといいでしょう。

# 参考

- [mysql - Get records with max value for each group of grouped SQL results - Stack Overflow](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results#answer-28090544)
    - このaxiac氏の解答が本記事作成のきっかけになりました。ありがとうございます。