LoginSignup
26

More than 5 years have passed since last update.

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

Last updated at Posted at 2014-04-21

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
26