mysqlだけでランキングしてみる(同率考慮)

注意（というか免責）

※ノリと勢いだけで書いてます。実用では使わない方が良いと思います。
EXPLAIN するとわかるけどテーブルフルスキャンが２回走ります。

ツッコミどころ満載なので、まぁ、その、すいません。

何がしたいか

こんな感じのスコア的なものが入ってるテーブルを、
同率考慮してランク付けしたいっていうよくあるケース。

sql

CREATE TABLE `some_score` (
  `user_id` int(11) NOT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

普通なら裏で集計バッチ回したり、redis使ったりして実装すると思うけど、
コレを力技でsqlだけでやってみる。

sql

SELECT rank, some_score.score FROM (
        SELECT score, @rank AS rank, cnt, @rank := @rank + cnt FROM
        	(SELECT @rank := 1) AS Dummy,
        	(SELECT score, count(*) AS cnt FROM some_score GROUP BY score ORDER BY score DESC) AS GroupBy
        ) AS Ranking
JOIN some_score ON some_score.score = Ranking.score ORDER BY rank ASC;

結果(例)

+------+-------+
| rank | score |
+------+-------+
|    1 |    52 |
|    1 |    52 |
|    3 |    51 |
|    3 |    51 |
|    3 |    51 |
|    3 |    51 |
|    7 |    50 |
|    7 |    50 |
|    7 |    50 |
|    7 |    50 |
|    7 |    50 |
|   12 |    42 |
|   12 |    42 |
|   12 |    42 |
|   12 |    42 |
|   16 |    41 |
|   16 |    41 |
|   16 |    41 |
|   16 |    41 |
|   16 |    41 |
|   16 |    41 |
|   22 |    40 |
|   22 |    40 |
== 略 ==

何やってるか

• (SELECT @rank := 1) AS Dummy,
@rank変数初期化。
  SET @rank = 1;
  で外に出しても良いけど、１クエリで済ませるために。

• (SELECT score, count(*) AS cnt FROM some_score GROUP BY score ORDER BY score DESC) AS GroupBy
  スコア毎に集計。
  カーディナリティが高いとここもちょっとやばそう。

• SELECT score, @rank AS rank, cnt, @rank := @rank + cnt FROM

• SELECT内で count(*) を直接@rankに足し込めないので、いったんサブクエリで受ける。

• サブクエリとjoinすると変数が何度も足し込まれておかしな事になるので、一時テーブルで受ける。

• SELECT rank, some_score.score FROM ( 〜〜〜 JOIN some_score ON some_score.score = Ranking.score ORDER BY rank ASC;
  元のテーブルとjoinして表示。

要点

AS Ranking の部分のサブクエリでスコア毎に集計した件数が出てるので、
それを一時テーブルに受けて元のテーブルとjoinして表示する感じ。

Rankingのとこ

+-------+------+-----+----------------------+
| score | rank | cnt | @rank := @rank + cnt |
+-------+------+-----+----------------------+
|    52 |    1 |   2 |                    3 |
|    51 |    3 |   4 |                    7 |
|    50 |    7 |   5 |                   12 |
|    42 |   12 |   4 |                   16 |
|    41 |   16 |   6 |                   22 |
|    40 |   22 |  71 |                   93 |
|    21 |   93 |   2 |                   95 |
|    20 |   95 |   5 |                  100 |
|    10 |  100 |  32 |                  132 |
+-------+------+-----+----------------------+

もっと効率良いやり方あると思うけど、このへんが限界:(

追記＠2014/4/22

よく考えたら、スコアに対するランクが取得できれば、
{ :score => :rank }の形のハッシュに整形してアプリケーション側で使えるから、
GROUP BYの部分の集計で１回テーブル嘗めるだけでも十分な気がする。