ＭySQL でグループごとの最大値を持つ行を取得する
やりたいこと
-
group by句で集計したグループごとに最大値（最小値）をもつ行を取得したい -
group by句で指定していないカラムの値も取得したい - 例：クラスごとに一番成績のよい生徒の名前を取得したい
結論
なんらかのサブクエリを使って２テーブル（元テーブルと、最大値カラムを付加した一時テーブル）を結合する必要がある。公式に４つ方法が書いてある。
MySQL 8.0 Reference Manual 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
問題
サンプルデータ
| class | name | score |
|---|---|---|
| 1 | A | 10 |
| 1 | B | 20 |
| 1 | C | 30 |
| 2 | D | 40 |
| 2 | E | 50 |
| 2 | F | 60 |
| 3 | G | 70 |
| 3 | H | 80 |
| 3 | I | 90 |
こんな感じでクラスごとに生徒の名前と点数をもつテーブルがあるとし、ここから各クラスの点数１位の生徒の名前を抜きたい。つまり C, F, I を抜きたい。
なので、 class を group by して MAX(score) を取得してみるが、このままだと name が取得できない（ group by で指定されておらず、かつ集計関数ではないため）
やってみる
group by で指定しているカラムは select で指定できるが、
SELECT class, MAX(score)
FROM test
GROUP BY class;
| class | score |
|---|---|
| 1 | 30 |
| 2 | 60 |
| 3 | 90 |
今回の要求のように group by で指定していないカラムを含む場合は取得できない。
SELECT name, MAX(score)
FROM test
GROUP BY class;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'TEST.test.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
ほしかった結果
| class | name | score |
|---|---|---|
| 1 | C | 30 |
| 2 | F | 60 |
| 3 | I | 90 |
対応方針
- 以下に公式のドキュメントがある
MySQL 8.0 Reference Manual 3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column - 以上なのだが、バリエーションがいくつかあるようなのでもうすこし内容を見てみる
- いずれの方法でもほしかった結果が得られるので結果は割愛
1. 相関サブクエリを使う
SELECT class, name, score
FROM test t1
WHERE score=(SELECT MAX(t2.score)
FROM test t2
WHERE t1.class = t2.class)
ORDER BY class;
各行について score 列がクラスの中で最大値かどうかを where 句で判断している。クラスごとの最大値はサブクエリで取得している。
ただ性能に不安がある（相関サブクエリなので内部のクエリと外部のクエリの掛け算になる）
2. 非相関サブクエリを使う
SELECT t1.class, name, t1.score
FROM test t1
JOIN (
SELECT class, MAX(score) AS score
FROM test
GROUP BY class) AS t2
ON t1.class = t2.class AND t1.score = t2.score
ORDER BY class;
まずサブクエリで class ごとの最大 score をもつテーブルを作成し、元のテーブルとこれを結合して name を取得している。 INNER JOIN なので最大 score をもつ行のみが取得できる。
可読性も悪くない。
3. LEFT JOIN を使う
SELECT t1.class, t1.name, t1.score
FROM test t1
LEFT JOIN test t2 ON t1.class = t2.class AND t1.score < t2.score
WHERE t2.class IS NULL
ORDER BY t1.class;
結合条件に t1.score < t2.score を指定することで、自分の score より大きな値をもたない（すなわち自分が最大値である）場合は結合した t2 のカラムが null になる。それを条件に指定することで最大値を取得する。
（個人的に思うのは）計算の都合のために一時テーブルを用意しているせいもあり、 IS NULL の条件文の意図を直感的に読みづらく感じる。この条件は今回の目的である "クラスごとの最大スコアを得る" という趣旨に直感的に沿っておらず、あとで見返したときに意味がすぐに理解できるかどうか、という点に不安が残る。
4. window 関数を使う
-- 順位カラムをもつ一時テーブルを作成
WITH t1 AS (
SELECT class, name, score,
RANK() OVER (PARTITION BY class
ORDER BY score DESC
) AS `Rank`
FROM test
)
-- 順位が１位の行を抽出
SELECT class, name, score
FROM t1
WHERE `Rank` = 1
ORDER BY class;
Rank 関数を使用し、元テーブルに順位カラムを付与した一時テーブル（ t1 ）を作成する。この一時テーブルは順位カラムをもっているので、単純に where で条件指定し順位が１位の行を抜けばよい。
まず順位という意味のある（最初からあってほしかったが、存在していなかった）概念を一時テーブル上に表現し、次いで抽出処理を行っているため計算工程を理解しやすく感じる。
どれにするか
4で書こうかな。2でもいい。
備考
環境
MySQL 8.0.21
サンプルデータ用のSQL
-- DDL
CREATE TABLE test(
class int,
name varchar(4),
score int
);
-- insert
INSERT INTO test VALUES(1,'A', 10);
INSERT INTO test VALUES(1,'B', 20);
INSERT INTO test VALUES(1,'C', 30);
INSERT INTO test VALUES(2,'D', 40);
INSERT INTO test VALUES(2,'E', 50);
INSERT INTO test VALUES(2,'F', 60);
INSERT INTO test VALUES(3,'G', 70);
INSERT INTO test VALUES(3,'H', 80);
INSERT INTO test VALUES(3,'I', 90);
Discussion