Edited at

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

More than 5 years have passed since last update.


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


注意(というか免責)

※ノリと勢いだけで書いてます。実用では使わない方が良いと思います。

EXPLAIN するとわかるけどテーブルフルスキャンが2回走ります。

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


何がしたいか

こんな感じのスコア的なものが入ってるテーブルを、

同率考慮してランク付けしたいっていうよくあるケース。


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;

    で外に出しても良いけど、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の部分の集計で1回テーブル嘗めるだけでも十分な気がする。