【MySQL】グループごとの上位N件ずつを取得

Window 関数を使う方法

MySQL 8.0.2 で導入された Window 関数をまず使ってみます。

参考にしたのは次のページです。「ROW_NUMBER() - Oracle, SQL Server and PostgreSQL」と書かれているので MySQL 以外でも共通で使えるはずです。

www.sqlines.com

SELECT city, country, population
FROM
 (SELECT city, country, population, 
               ROW_NUMBER() OVER (PARTITION BY country ORDER BY population DESC) as country_rank
   FROM cities) ranked
WHERE country_rank <= 2;

欧米の都市人口を使って例示されていましたが、少しわかりにくく感じたので、クラスごとのテスト点数上位2名ずつを取得する例にしました。
正規化していないのは例をシンプルにするためなので、ここでは無視してください。

CREATE TABLE students (
  id int,
  class varchar(8),
  name varchar(16),
  score int
);

INSERT INTO students VALUES
(1, 'A組', '佐藤', 95),
(2, 'A組', '鈴木', 87),
(3, 'A組', '高橋', 80),
(4, 'B組', '田中', 100),
(5, 'B組', '渡辺', 72),
(6, 'B組', '伊藤', 53);

上記の参考ページを真似すると、クエリは次のようになります。

SELECT class, name, score
FROM (
    SELECT
        class, name, score,
        ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) as class_rank
    FROM students
) AS ranked
WHERE class_rank <= 2;

class	name	score
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

取得結果は4件になりますが、テーブルに存在する全6件を走査していると思われ、パフォーマンスは悪そうです。プライマリキーやインデックスを設定したら全然違う結果になるかもしれませんが未確認です。使えそうかどうか実行計画を確認しながら進めると良いと思います。

Window 関数を使わない方法（異常あり版）

MySQL 8 より古いバージョンを使うことがまだよくあります。 Window 関数を使わない方法も見ておきます。

先ほどの参考ページに載っているもの:
MySQL Query to Get Top 2

SELECT city, country, population
FROM
  (SELECT city, country, population, 
               @country_rank := IF(@current_country = country, @country_rank + 1, 1) AS country_rank,
               @current_country := country 
    FROM cities
    ORDER BY country, population DESC
  ) ranked
WHERE country_rank <= 2;

クラスの例に変えつつ少しだけ改善したもの：

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @current_class, @class_rank + 1, 1) AS class_rank,
        @current_class := class,
        class, name, score
    FROM students
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

初回実行

class	name	score
A組	佐藤	95
A組	鈴木	87
A組	高橋	80
B組	田中	100
B組	渡辺	72
B組	伊藤	53

あれ？全件取得してしまいました…。

2回目以降の実行

初回以外の実行では期待通りの結果になりました。実行計画も Window 関数を使ったときと同じです。

class	name	score
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

初回と2回目以降の違い

最初の実行で何が起こっているのか見るために、サブクエリ部分だけを実行してみました。

SELECT
    @class_rank := IF(class = @current_class, @class_rank + 1, 1) AS class_rank,
    @current_class := class AS curr_class,
    class, name, score
FROM students
ORDER BY class, score DESC;

初回

class_rank	curr_class	class	name	score
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

2回目以降

class_rank	curr_class	class	name	score
1	A組	A組	佐藤	95
2	A組	A組	鈴木	87
3	A組	A組	高橋	80
1	B組	B組	田中	100
2	B組	B組	渡辺	72
3	B組	B組	伊藤	53

2回目は class_rank にクラスごとの順位が入っています。一方、1回目は 1 ばかりになってしまっています。

もう一つの問題

レコードを追加したりしながらあれこれ見ていたら、問題点がもう一つ見つかりました。

INSERT INTO students VALUES
(7, NULL, '山本', 99),
(8, NULL, '中村', 96),
(9, NULL, '小林', 93);

グループとしてまとめる対象のカラム（ここでは class）に NULL が入っているレコードをこのように追加すると、次の結果になりました。

class	name	score
NULL	山本	99
NULL	中村	96
NULL	小林	93
A組	佐藤	95
A組	鈴木	87
B組	田中	100
B組	渡辺	72

A組とB組は上位2名ずつですが、class が NULL の分は3件全てを取得してしまっています。

サブクエリだけを実行すると次のようになっていました。

class_rank	curr_class	class	name	score
1	NULL	NULL	山本	99
1	NULL	NULL	中村	96
1	NULL	NULL	小林	93
1	A組	A組	佐藤	95
2	A組	A組	鈴木	87
3	A組	A組	高橋	80
1	B組	B組	田中	100
2	B組	B組	渡辺	72
3	B組	B組	伊藤	53

おそらく NULL の場合に class = @current_class の比較が正しくできていないのが原因です。
NULL との比較は IS NULL としないといけませんね。

Window 関数を使わない方法（完成版と思ったのにダメだった版）

mysql - Selecting first N rows for each group - Database Administrators Stack Exchange
このベストアンサーに書かれているのが下記です。

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number, 
              @file_type := file_type as file_type, 
              file_name, id
      FROM 
          (SELECT @type_number := 1) x, 
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number <= 2;

これを参考にして次のようにしました。
~~これが自分的なファイナルアンサーです。~~
　↓
2019/6/16 異常が見つかりました（後述）。

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @current_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
        @current_class := class,
        class, name, score
    FROM (
        SELECT @class_rank := 0, class, name, score
        FROM students
    ) AS x
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

2つのサブクエリを CROSS JOIN している点など気になる部分は改善し、NULL の対策もしました。*1

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
|  3 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

処理が一つ増えてしまっていますが、サブクエリを追加したのでやむを得ません。

異常発覚（2019/6/16）

初回実行時に正しく取得されないことに気づきました…。
NULL 以外のクラスでは3人ずつ取得されてしまいます。

class	name	score
NULL	山本	99
NULL	中村	96
A組	佐藤	95
A組	鈴木	87
A組	高橋	80
B組	田中	00
B組	渡辺	72
B組	伊藤	53

サブクエリだけを初回実行した結果は下記です。

class_rank	curr_class	class	name	score
1	NULL	NULL	山本	99
2	NULL	NULL	中村	96
3	NULL	NULL	小林	93
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

Window 関数を使わない方法（真・完成版）

2019/6/16 考え直しました。

@current_class に入っているのは何か

参考サイトを真似して @current_class という変数を使ってきました。
訳すと「現在のクラス」ですが、変数の中身は「一つ前のクラス」に思えます。
これを検証するために、サブクエリ内で @current_class への代入より前に @current_class の取得を追加し、そのサブクエリだけを実行してみました。

SELECT
    @class_rank := IF(class = @current_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
    @current_class,
    @current_class := class AS curr_class,
    class, name, score
FROM (
    SELECT @class_rank := 0, class, name, score
    FROM students
) AS x
ORDER BY class, score DESC;

2回目以降の実行時の結果

class_rank	@current_class	curr_class	class	name	score
1	B組	NULL	NULL	山本	99
2	NULL	NULL	NULL	中村	96
3	NULL	NULL	NULL	小林	93
1	NULL	A組	A組	佐藤	95
2	A組	A組	A組	鈴木	87
3	A組	A組	A組	高橋	80
1	A組	B組	B組	田中	100
2	B組	B組	B組	渡辺	72
3	B組	B組	B組	伊藤	53

@current_class には一つ上の行の curr_class と同じ値が入っています。
やはり「一つ前のクラス」のようです。
先頭行の B組 は、前回実行時の最終行で代入したものが残っているものと思われます。

代入の時点では「現在」ですが、各行の処理時には「一つ前」ということですね。
ちょっとした呼び方の違いですが、「一つ前」と捉えるほうがわかりやすそうです。
ここから先は変数名を @prev_class にします。

@class_rank を 0 で初期化している理由

0 を代入しないでサブクエリを実行してみます（初回実行）。

SELECT
    @class_rank := IF(class = @prev_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,
    @prev_class := class AS prev_class,
    class, name, score
FROM students
ORDER BY class, score DESC;

class_rank	prev_class	class	name	score
NULL	NULL	NULL	山本	99
NULL	NULL	NULL	中村	96
NULL	NULL	NULL	小林	93
1	A組	A組	佐藤	95
1	A組	A組	鈴木	87
1	A組	A組	高橋	80
1	B組	B組	田中	100
1	B組	B組	渡辺	72
1	B組	B組	伊藤	53

@class_rank + 1 という計算ができていないようです。
何も値を入れていない変数の中身は NULL なので、NULL + 1 という不正な計算になったと考えられます。

@class_rank の設定

@class_rank := IF(class = @prev_class OR class IS NULL, @class_rank + 1, 1) AS class_rank,

ここで行っているのは

次の条件に該当していれば @class_rank を一つ増やす
　　・一つ前のクラスが現在と同じ場合（NULL 以外）
　　　または
　　・現在のクラスが NULL の場合
そうでなければ @class_rank を 1 にする

ということです。

しかし、これでは NULL の考慮が不十分です。
次のようになっていないといけません。

次の条件に該当していれば @class_rank を一つ増やす
　　・一つ前のクラスが現在と同じ場合（NULL 以外）
　　　または
　　・現在のクラスも一つ前のクラスも NULL の場合
そうでなければ @class_rank を 1 にする

つまり、次のようになります。

@class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,

@prev_class の初期化

@prev_class には class の値が代入されます。
そのため、初期化時には class に含まれるどの値にも該当しないものを入れておかないといけません。

ただし、class に含まれない値なら何でも良いわけではありません。
試しに -1 という数値で初期化してみると正しい結果になりませんでした。
class の値を入れる変数なので、それと同じ型の値（文字列）にしておかないといけないようです。

それを考慮して空文字を入れておくとうまくいきました。

@prev_class := ''

最終形①

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,
        @prev_class := class,
        class, name, score
    FROM (
        SELECT
            @class_rank := 0,    # NULL+1という計算はできないので先に0にしておく
            @prev_class := '',   # classと同じ文字列型かつclassに存在しない値で初期化
            class, name, score
        FROM students
    ) AS x
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

実行計画

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | <derived3> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
|  3 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | NULL           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

最終形②

最終型①との違いは、一つのクエリで済まそうとせずに先に変数の初期化を行っている点だけです。

SET @class_rank = 0;
SET @prev_class = '';

SELECT class, name, score
FROM (
    SELECT
        @class_rank := IF(class = @prev_class OR (class IS NULL AND @prev_class IS NULL), @class_rank + 1, 1) AS class_rank,
        @prev_class := class,
        class, name, score
    FROM students
    ORDER BY class, score DESC
) AS ranked
WHERE class_rank <= 2;

実行計画
サブクエリが一つ少ない分、実行計画でも DERIVED が一つ少なく済んでいます。

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |    33.33 | Using where    |
|  2 | DERIVED     | students   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    9 |   100.00 | Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------+

インデックスの効果

class と score の複合インデックスを設定

ALTER TABLE students ADD INDEX idx_class_score(class, score);

しかし、実行計画に変化はありませんでした…。

まとめ

Window 関数を使わないとかなり強引になります。
強引ゆえに、これで本当に正しいの？という不安が生じます。
最初の参考ページのクエリにも問題点が見つかったように、正しく書けたと思っても何か落とし穴が残っているかもしれません。

当たり前すぎる結論になりますが、Window 関数を利用できるバージョンならそれを使いましょう。

2019/6/16 追記

ユーザ定義関数を使わない方法の記事を書きました。
そちらの方法は MySQL 以外の RDBMS でも使えると思います。

kabochapo.hateblo.jp

*1:class IS NULL の条件を加えました。また、変数が 1 で初期化されていると NULL の分の class_rank が 2 以上になってしまうため 0 にしました。

のんびり精進

調べた情報などをおすそ分けできれば。

【MySQL】グループごとの上位N件ずつを取得

Window 関数を使う方法

Window 関数を使わない方法（異常あり版）

初回実行

2回目以降の実行

初回と2回目以降の違い

もう一つの問題

Window 関数を使わない方法（完成版と思ったのにダメだった版）

異常発覚（2019/6/16）

Window 関数を使わない方法（真・完成版）

@current_class に入っているのは何か

@class_rank を 0 で初期化している理由

@class_rank の設定

@prev_class の初期化

最終形①

最終形②

インデックスの効果

まとめ

2019/6/16 追記

Window 関数を使う方法

Window 関数を使わない方法（異常あり版）

初回実行

2回目以降の実行

初回と2回目以降の違い

もう一つの問題

Window 関数を使わない方法（完成版 と思ったのにダメだった版）

異常発覚（2019/6/16）

Window 関数を使わない方法（真・完成版）

@current_class に入っているのは何か

@class_rank を 0 で初期化している理由

@class_rank の設定

@prev_class の初期化

最終形①

最終形②

インデックスの効果

まとめ

2019/6/16 追記

Window 関数を使わない方法（完成版と思ったのにダメだった版）