Y's note

Web技術・プロダクトマネジメント・そして経営について

本ブログの更新を停止しており、今後は下記Noteに記載していきます。
https://note.com/yutakikuchi/

mysqlでgroup毎のTop-K行を取得する方法

How to select the first/least/max row per group in SQL · Baron Schwartz's Blog はてなブックマーク - 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;