MySQLでカテゴリー（グループ）ごとのTOP3（ランキング上位3つ）ずつ抽出する
MySQLでグループごと上位N件を取得するのは無理という話
MySQLに限らずOracle、PostgreなどのRDBMSにおいて、命題に対する実装は１クエリーでは不可能ということは有名な話です。
あるカラムに設定されているグループコードごとのスコアや数値順に並べた時、そのグループごとにN件抽出する場合、少なからず複数のクエリーを発行することになるのですが、その手法はいくつかあります。
要はサブクエリーを書いたりInnerJoinする方法などの中で、もっとも負荷やパフォーマンス的にこれだ！というものを採用するのがベストプラクティスなのですが、調査するには実行計画をみるしかないですね。
まずは準備段階として、下記のような欧米の町ごとの人口を管理するテーブルを例に作成して話を解決してみます。
CREATE TABLE cities
(
city VARCHAR(80),
country VARCHAR(80),
population INT
);
INSERT INTO cities VALUES ('New York', 'United States', 8175133);
INSERT INTO cities VALUES ('Los Angeles', 'United States', 3792621);
INSERT INTO cities VALUES ('Chicago', 'United States', 2695598);
INSERT INTO cities VALUES ('Paris', 'France', 2181000);
INSERT INTO cities VALUES ('Marseille', 'France', 808000);
INSERT INTO cities VALUES ('Lyon', 'France', 422000);
INSERT INTO cities VALUES ('London', 'United Kingdom', 7825300);
INSERT INTO cities VALUES ('Birmingham', 'United Kingdom', 1016800);
INSERT INTO cities VALUES ('Leeds', 'United Kingdom', 770800);
// 確認
mysql> select * from cities;
+-------------+----------------+------------+
| city | country | population |
+-------------+----------------+------------+
| New York | United States | 8175133 |
| Los Angeles | United States | 3792621 |
| Chicago | United States | 2695598 |
| Paris | France | 2181000 |
| Marseille | France | 808000 |
| Lyon | France | 422000 |
| London | United Kingdom | 7825300 |
| Birmingham | United Kingdom | 1016800 |
| Leeds | United Kingdom | 770800 |
+-------------+----------------+------------+
9 rows in set (0.00 sec)
ここで、アメリカ、フランス、イギリスそれぞれの市区の人口を多い順に並べた時、それぞれの国の市区を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;
このクエリの結果は下記のようになります。
| city | country | population |
|---|---|---|
| Paris | France | 2181000 |
| Marseille | France | 808000 |
| London | United Kingdom | 7825300 |
| Birmingham | United Kingdom | 1016800 |
| New York | United States | 8175133 |
| Los Angeles | United States | 3792621 |
動作説明
MySQLでは行番号を取得するROW_NUMBER()が用意されていません。（Oracle、Postgreなどはこれを利用する方が効率が良い） 一方MySQLではユーザ変数やセッション変数などが利用できます。
この変数は初期化が不要で、なおかつ計算結果取得のためにも利用できます。また、中間結果の保持も可能です。
@current_country := country
この行は、実行途中のrow(行)ごとに（foreach的なこと）そのcountryカラムの値を保持しておくことができます。
次に
@country_rank := IF(@current_country = country, @country_rank + 1, 1)
この式の意味するところは、すでに取得した@current_countryが行ごとに実行されたcountryカラムと一致した時に@country_rank + 1した結果を返します。それ以外の場合は1を返却します。
MySQLのIF(a, b, c)の補足
MySQLのIF文は三項演算子的な見え方に近いです。aは条件式で、真の場合bを返却。偽の場合cを返却。
話を戻しますと、 実際にこのサブクエリー部分のみを実行すると下記のような結果になります。
mysql> 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
-> ;
+-------------+----------------+------------+--------------+-----------------------------+
| city | country | population | country_rank | @current_country := country |
+-------------+----------------+------------+--------------+-----------------------------+
| Paris | France | 2181000 | 1 | France |
| Marseille | France | 808000 | 2 | France |
| Lyon | France | 422000 | 3 | France |
| London | United Kingdom | 7825300 | 1 | United Kingdom |
| Birmingham | United Kingdom | 1016800 | 2 | United Kingdom |
| Leeds | United Kingdom | 770800 | 3 | United Kingdom |
| New York | United States | 8175133 | 1 | United States |
| Los Angeles | United States | 3792621 | 2 | United States |
| Chicago | United States | 2695598 | 3 | United States |
+-------------+----------------+------------+--------------+-----------------------------+
9 rows in set (0.01 sec)
このcountry_rankが最後のWHERE句の中でWHERE country_rank <= 2とすることで、取得したい上位N件を取得することになります。
今回はグループの数が不定で量が多い場合にもっとも最適化されたクエリーの紹介でしたが、グループの数が2とか3とかの場合は、シンプルに一つ一つの上位N件の結果をUNION ALLで繋いだ方が速度が出るようです。
