mysqlでgroup毎のTop-K行を取得する方法
How to select the first/least/max row per group in SQL · Baron Schwartz's Blog
mysqlを用いた特定のgroupに所属する行を一定数ずつ(もしくは何かしらでsortされたTop-K行)取り出したいという問題がある。これしきの事、単純なgroup byを使って簡単に解きたい内容であるがちょっとしたテクニックを必要とする。調べたところ以下の解決方法がある。
1. union allを使ってgroup毎に抽出した結果を結合。
2. tableをgroupで自己結合し特定行数取得。
3. session固有のユーザ定義変数を使って特定group内の行をcountしていく。因みにposgre/sql serverはrow_number()というgroup毎に数を採番してくれる便利関数が存在してこれを利用するらしいが、mysqlにはまだ無い様子。
解決方法1の場合は各group毎に特定行抽出した結果をunionするのでgroupが増えるとQueryが冗長でダサい。2の場合はgroupのcross結合を行うのでgroupに紐づくデータが膨大だとつらい。よってここでは 3.session固有のユーザー定義変数を使って...について簡単に紹介する。
下はidの昇順にてTop-10を出している。最初の行でsession固有の変数を定義している。SQL中の@group = media_idがGroupの指定。group変数が未定義の場合は1を同一の場合にはnumをincrementしている。subquery内のrow_numberがincrement数なので最後のwhereにてrow_numberが10以下を指定するとTop-10を抽出できる。rand()にてgroup内からrandomで特定行数抽出したい場合はsubquery内のsubqueryで最初にrand()しておくと良い。
set @K := 10, @num := 0, @group := ''; select id, media_id from ( select id, media_id, @num := if(@group = media_id, @num + 1, 1) as row_number, @group := media_id as mid from contents -- randomに切り替えたい場合はfromとして下記を利用 -- from (select id, media_id from contents order by rand()) as c order by media_id ) as t where t.row_number <= @K;ただし上の例だとsubqueryを使っていてtemporary tableになってしまうのと、whereによる絞り込みなのでデータ数に依存して計算コストが掛かってしまうことが懸念される。これらの問題解決とSQLをすっきりさせるためにもgroup byとhavingを使った例が以下のもの。しかし実行速度パフォーマンスは手元の環境では10000件程のテストデータだとどちらもほとんど差は無かった。
set @K := 10, @num := 0, @group := ''; select id, media_id, @num := if(@group = media_id, @num + 1, 1) as row_number, @group := media_id as mid from contents -- from (select id, media_id from contents order by rand()) as c group by id, media_id having row_number <= @K;