【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 からお借りしました。)

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

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

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

誤答

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

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

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 という sql_mode がデフォルトで有効になっているためです。
この設定では、 GROUP BY でグルーピングの基準として指定していないカラムを SELECT 内に含める場合、 SUM や MAX などの集約関数を使う必要があるのです。
上のクエリの場合、 from_date と to_date がグルーピングの基準でないにも関わらず、単独で登場していますね。

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

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のリファレンスマニュアル によると、GROUP BY でグルーピングの対象になっていないカラムを単独で SELECT しようとした場合、グループ内のどのレコードから SELECT されるかは「不確定」なのです。

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

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

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

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

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 することで、元のテーブルから給与が最大でないレコードを除外することができます。

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. 自己結合を使う

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

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
  • このaxiac氏の解答が本記事作成のきっかけになりました。ありがとうございます。