ユーザーデータを集計して、ランキングを取りたかった時のお話し。つづき

前回のjoinとgroup byを使った方法は、対象件数が増えると乗算で処理に時間が掛かるため、数万件の処理にはとても耐えられなかったので、新しくsqlを作り直した時のお話し。つまり、前回の続きです。

select-insert（なんて呼ぶのかよく分からないが、insertのvalueをselect文にする事）を使って、やる場合はとても簡単シンプル＆超高速。

INSERT INTO rank
SELECT null, uid FROM `user` ORDER BY `user`.`count` DESC

rankテーブルは、keyにAUTO_INCREMENTかけて、これが順位。「null, uid」でnull指定で連番を挿入させて簡単にランキングを作成できた。5万件でも0.00何秒の世界。

で、これをアップデート文にすればよいかと単純に考えたはいいが、できない。やはり、Mysqlのupdate構文に問題ありな予感？それとも僕の頭？

仕方ないのでhttp://dev.mysql.com/doc/refman/5.1/ja/user-variables.html:ユーザ変数なる物を引っ張り出して、順位を計算する方法を編み出した。いや、oracleなら普通に関数であるんだけどね。

set @rownum = 0;
select @rownum := @rownum + 1 rownum, uid, count
from user order by count;

これで順位を一発で取得できる。しかも超早。
http://dev.mysql.com/doc/refman/5.1/ja/user-variables.html:ユーザ変数すごいよ。ちょっとした小細工なら何でもこなせそう。

次は、これをupdateする。

set @rownum = 0;
update rank
 inner join (
  select @rownum := @rownum + 1 rownum, uid
  from user order by count DESC
 ) b
 ON rank.uid = b.uid
set rank.battle = b.rownum

またまた変なところで結合させなくちゃいけないMysqlの罠に嵌りながら作ってみたところ、5万件のデータでも0.1秒以下という何の問題もない結果が出てくれました。
しかもこれ、limitで制限掛けて回せば、たぶん億単位のデータでも可能なんじゃないかな？