Edited at

MySQLでグループ内Top3を抽出する

More than 3 years have passed since last update.


困りました

mysql - Get top n records for each group of grouped results - Stack Overflow http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results

調べました。

Stack Overflowさんありがとう。

bluefeet♦ さんありがとう。

詳しいことはSQLを解読してください。

これのまんまコピペですが分かりやすいようにサンプルも作りました。

PHPでPDOから使用するサンプルがあった方が使いやすいかと思って作りました。


Ansers19

set @num := 0, @group := '';

select person, `group`, age
from
(
select person, `group`, age,
@num := if(@group = `group`, @num + 1, 1) as row_number,
@group := `group` as dummy
from mytable
order by `group`, age desc, person
) as x
where x.row_number <= 2;


自分で読んでて???だったので、、、

よくある都道府県を市町村名コードで抽出するサンプルを追加しました。

県コードで縛って、市町村コード(sort)の大きい順に3つ取り出すSQLです。

ついでに並びを昇順に戻して出力しています。

idはオートナンバー的なあれです。


todoufuken

set @num := 0, @pref := '';

select id, pref, sort, name
from
(
select id, pref, sort, name,
@num:=if(@pref=pref, @num+1, 1) as row_number,
@pref:=pref as dummy
from city
where pref in (16,17,18) -- 県コード
order by pref, sort desc
) as x
where x.row_number <= 3
order by pref, sort, id


ソース

PDOはシンプルなラッパークラスを利用しました。

とても楽ちんでした。

新しいバージョンもあるっぽいけど追ってません。

実際にはDB.phpを継承するユーティリティクラスを作成し、$connectMySQLを引数にコンストラクタを呼んでます。コンストラクタ内で初期化もやってるのをぺろっと持ってきたので一部歪な形となっています。


sqltest.php

<?php

require_once './DB.php';
/*
* A simple wrapper for PDO.
* Inspired by the sweet PDO wrapper from http://www.fractalizer.ru
*
* @author Anis uddin Ahmad <anisniit@gmail.com>
* @link http://www.fractalizer.ru/frpost_120/php-pdo-wrapping-and-making-sweet/
* @link http://ajaxray.com
*/

// DB接続情報
unset($connectMySQL);
$connectMySQL = array(
'user' => 'root',
'password' => 'root',
'schema' => 'mytest',
'hostname' => '127.0.0.1',
);

function getRows($sqls)
{
global $connectMySQL;
Db::setConnectionInfo($connectMySQL['schema'], $connectMySQL['user'], $connectMySQL['password'], 'mysql', $connectMySQL['hostname']);
Db::setDriverOptions(array(PDO::MYSQL_ATTR_INIT_COMMAND=>"SET CHARACTER SET `utf8`"));
foreach($sqls as $sql){
unset($rows);
$rows = Db::getResult($sql);
}
return $rows;
}

unset($sql);
$sql[] = 'set @num := 0, @group := \'\'';
$sql[] = <<< SQLHEREDOC
SELECT *
FROM
(
SELECT *,
@num := if(@group = `グループ`, @num + 1, 1) AS row_number,
@group := `グループ` AS dummy
FROM
(
SELECT `id`, `点数`, `名前`, `グループ`
FROM tes
ORDER BY `グループ`, `点数` DESC, `id`
) AS ranking
) AS limitter
where limitter.row_number <= 3
SQLHEREDOC;

$arr = getRows($sql);
// ヘッダー
echo "+-------+-------+-------+-------+-------+-------+\n";
echo "| id | 点数 | 名前 | GROUP | row | dummy |\n";
echo "+-------+-------+-------+-------+-------+-------+\n";
foreach ($arr as $v)
{
foreach ($v as $w)
{
echo '| '.$w."\t";
}
echo "|\n";
}
echo "+-------+-------+-------+-------+-------+-------+\n";



実行結果


CygWinBash

ekaneko@hoge /cygdrive/c/var/www/sqltest

$ php sqltest.php
+-------+-------+-------+-------+-------+-------+
| id | 点数 | 名前 | GROUP | row | dummy |
+-------+-------+-------+-------+-------+-------+
| 5 | 93 | 天響 | A | 1 | A |
| 1 | 91 | 希星 | A | 2 | A |
| 8 | 55 | 葵絆 | A | 3 | A |
| 11 | 99 | 美俺 | B | 1 | B |
| 14 | 80 | 祈愛 | B | 2 | B |
| 10 | 60 | 泡姫 | B | 3 | B |
| 20 | 95 | 杏奴 | C | 1 | C |
| 31 | 92 | 杏奴 | C | 2 | C |
| 32 | 81 | 類 | C | 3 | C |
| 23 | 95 | 姫星 | D | 1 | D |
| 24 | 89 | 宝物 | D | 2 | D |
| 22 | 68 | 黄熊 | D | 3 | D |
+-------+-------+-------+-------+-------+-------+


テーブルデータ

mysql> SELECT * FROM mytest.tes;

+----+------+--------+----------+
| id | 点数 | 名前 | グループ |
+----+------+--------+----------+
| 1 | 91 | 希星 | A |
| 2 | 32 | 絆星 | A |
| 3 | 28 | 来桜 | A |
| 4 | 25 | 月 | A |
| 5 | 93 | 天響 | A |
| 6 | 27 | 緑輝 | A |
| 7 | 12 | 火星 | A |
| 8 | 55 | 葵絆 | A |
| 9 | 37 | 姫星 | A |
| 10 | 60 | 泡姫 | B |
| 11 | 99 | 美俺 | B |
| 12 | 35 | 姫凜 | B |
| 13 | 46 | 恋恋愛 | B |
| 14 | 80 | 祈愛 | B |
| 15 | 33 | 今鹿 | B |
| 16 | 60 | 七音 | B |
| 17 | 56 | 於菟 | C |
| 18 | 76 | 茉莉 | C |
| 19 | 65 | 不律 | C |
| 20 | 95 | 杏奴 | C |
| 21 | 37 | 類 | C |
| 22 | 68 | 黄熊 | D |
| 23 | 95 | 姫星 | D |
| 24 | 89 | 宝物 | D |
| 25 | 35 | 心愛 | D |
| 26 | 56 | 美望 | D |
| 27 | 45 | 姫奈 | D |
| 28 | 30 | 皇帝 | D |
| 29 | 52 | 男 | D |
| 30 | 17 | 本気 | D |
| 31 | 92 | 杏奴 | C |
| 32 | 81 | 類 | C |
+----+------+--------+----------+
32 rows in set (0.00 sec)

mysql>


memo

適当にデータを作りすぎですね。

フィールド名も漢字が分かりやすいだろうと気を利かせたけどSQLが読みにくくなっただけのような気もしますし…。

点数の適当っぷりは乱数UPDATEしたためです。

UPDATE `mytest`.`tes` SET `点数`=FLOOR(1+RAND()*9)*10+FLOOR(RAND()*10);

-- SELECT FLOOR(1+RAND()*9)*10+FLOOR(RAND()*10);