Help us understand the problem. What is going on with this article?

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回テーブル嘗めるだけでも十分な気がする。

arc279
フリーランスだけどわりとどこ行っても「いい感じにする」のに定評があるおっさん
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away