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

## 困りました

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から使用するサンプルがあった方が使いやすいかと思って作りました。

```sql: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はオートナンバー的なあれです。

```sql: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はシンプルなラッパークラスを利用しました。
とても楽ちんでした。
新しいバージョンもあるっぽいけど追ってません。

+ Simple PHP PDO Wrapper : light, static, easy | ajaxray => Anis Ahmad's Tech Blog http://ajaxray.com/blog/simple-php-pdo-wrapper-light-static-easy-to-use
+ ajaxray/static-pdo · GitHub https://github.com/ajaxray/static-pdo

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

```php: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";
```

## 実行結果

```bash: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したためです。

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