🗒️

MySQL でグループごとの最大値を持つ行を取得する

5 min read

やりたいこと

  • group by 句で集計したグループごとに最大値(最小値)をもつ行を取得したい
  • group by 句で指定していないカラムの値も取得したい
  • 例:クラスごとに一番成績のよい生徒の名前を取得したい

結論

なんらかのサブクエリを使って2テーブル(元テーブルと、最大値カラムを付加した一時テーブル)を結合する必要がある。公式に4つ方法が書いてある。
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

こんな感じでクラスごとに生徒の名前と点数をもつテーブルがあるとし、ここから各クラスの点数1位の生徒の名前を抜きたい。つまり C, F, I を抜きたい。
なので、 classgroup 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

エラーにならない場合もあるがその場合でも正しい値は取れない。エラーになるかどうかは sql_mode による。
MySQL 8.0 Reference Manual 12.20.3 MySQL Handling of GROUP BY

ほしかった結果

class name score
1 C 30
2 F 60
3 I 90

対応方針

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
)

-- 順位が1位の行を抽出
SELECT class, name, score
  FROM t1
  WHERE `Rank` = 1
ORDER BY class;

Rank 関数を使用し、元テーブルに順位カラムを付与した一時テーブル( t1 )を作成する。この一時テーブルは順位カラムをもっているので、単純に where で条件指定し順位が1位の行を抜けばよい。
まず順位という意味のある(最初からあってほしかったが、存在していなかった)概念を一時テーブル上に表現し、次いで抽出処理を行っているため計算工程を理解しやすく感じる。

どれにするか

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

ログインするとコメントできます