ニフラムされて光の彼方へ消え去ろうとしている人に触れているとどうなるのだろう。

急にランキング集計することになってしまったデータの作成を極力MySQLだけでする。
とつぜん「この機能にユーザランキングが欲しいよ。なるはやで。もちろん更新間隔も短めなやつ。当日のランキングとデイリーランキングあるといいな。」みたいな依頼をされる事が多い業務をしております。
そんな時はとりあえず既存の資源だけで何とかしなきゃいけないので、頑張ってSQLとわずかなプログラムで対応したりしております。
とりあえずランキングで出したいデータ例

てな感じで同位がいたら、ちゃんと何か何これ、ほら、説明できないけど、アレっぽくする。

hogeをn個取得する度に insert でガンガン追加していく謎の user_hoge_nums テーブル

desc user_hoge_nums;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id    | int(10) unsigned    | NO   | MUL | NULL    |                |
| num        | int(10) unsigned    | NO   |     | 0       |                |
| created    | datetime            | NO   | MUL | NULL    |                |
| updated    | datetime            | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

とりあえず指定した期間(当日分とか前日分とか)を指定してスコアをユーザ毎に計算するSQL。
これくらいのSQLなら3000万レコードくらいでも30秒ほどで処理できました。

select user_id, sum(num) total_score from user_hoge_nums where created between ? and ? group by user_id;

上記SQLで取得したデータを一時的に格納する tmp_hoge_scores さん。

desc tmp_hoge_scores;
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id     | int(10) unsigned | NO   | UNI | NULL    |                |
| score       | int(10) unsigned | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

前回の処理データが残ってるので、皆殺しする。

truncate tmp_hoge_scores;

ひたすら普通に insert する。10件〜100件単位くらいで commit するのがいいと思います。全件まとめて insert して commit はヤバいかも。

insert into tmp_hoge_scores(user_id, score) values(?, ?);

一時的に集計したランキングを格納する tmp_hoge_rankings さん。

desc tmp_hoge_rankings;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id     | int(10) unsigned    | NO   | UNI | NULL    |                |
| score       | int(10) unsigned    | NO   |     | NULL    |                |
| rank        | int(10) unsigned    | NO   | MUL | NULL    |                |
| created     | datetime            | NO   |     | NULL    |                |
| updated     | datetime            | NO   |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

これも前回の処理データが残ってるので皆殺し。

truncate tmp_hoge_rankings;

ユーザ定義変数ってやつを使う。

set @rank:=0, @rownum:=0, @prevalue:=0;
insert into tmp_hoge_rankings(user_id, score, rank)
select user_id, score, rank from (
    select
        user_id,
        score,
        @rank:=if((@rownum:=@rownum+1) and (@prevalue <=> score), @rank, @rownum) as rank,
        @prevalue:=score as preval
    from tmp_hoge_score order by score desc
) a;

不思議な感じの部分だけ分解して解説すると、@prevalue(前のレコードのスコア)が score(現在のレコードのスコア)と等しければ @rank を、違えば @rownum に 1 足したものを @rank にセット。

@rank:=if((@rownum:=@rownum+1) and (@prevalue <=> score), @rank, @rownum)

こうする事で同位が続いた後に、@rownum にレコード数がカウントアップされているので、なんかアレな順位を取得できる。

@prevalue:=score as preval

↑現在のレコードのスコアを @prevalue にセット

最後に tmp_hoge_rankings を hoge_rankings に、 hoge_rankings を tmp_hoge_rankings に rename してをわり。

rename table hoge_rankings to hoge_rankings_backup, tmp_hoge_rankings to hoge_rankings, hoge_rankings_backup to tmp_hoge_rankings;

とまあ、こんな感じの方法で集計するデータによって若干変更をしつつやっております。集計にかかる時間は3000万レコードで1分前後ですね。バッチで5分おきや10分おきくらいでなら運用できるレベルです。
もうちょい早くしたければ集計部分は前回の集計時からのデータをとってきて、前回の集計データと足してやるなどすればよいかと。

をわり。