SQLとRedisでのランキングの扱い方

SQL Redis

今日はランキングの話を書いてみたいと思います。

サンプルデータは以下です。プレミアリーグの昨シーズン（2011-12シーズン）の得点データの一部です。

name score
Kun Agüero 23
Mario Balotelli 13
Edin Džeko 14
Wayne Rooney 27
Robin van Persie 30
Emmanuel Adebayor 17
Demba Ba 16
Papiss Demba Cissé 13
Clint Dempsey 17
Grant Holt 15
Yakubu Ayegbeni 17
Steven Fletcher 12

ここで今シーズンの今のところの得点王であるDemba Baさんが昨シーズン得点ランキング何位だったかを知りたいとします。

危険なほどのスピードで動作するといわれるRedisの「ソート済みセット型」を使う場合は以下のような手順で求めます。

  1. 自分の得点を取得
  2. 自分の得点より高得点のユーザー数をカウント
  3. それを＋１したものが自分の順位

データは以下のようにして投入します。

redis-cli ZADD scores 23 "Kun Agüero"
redis-cli ZADD scores 13 "Mario Balotelli"
redis-cli ZADD scores 14 "Edin Džeko"
redis-cli ZADD scores 27 "Wayne Rooney"
redis-cli ZADD scores 30 "Robin van Persie"
redis-cli ZADD scores 17 "Emmanuel Adebayor"
redis-cli ZADD scores 16 "Demba Ba"
redis-cli ZADD scores 13 "Papiss Demba Cissé"
redis-cli ZADD scores 17 "Clint Dempsey"
redis-cli ZADD scores 15 "Grant Holt"
redis-cli ZADD scores 17 "Yakubu Ayegbeni"
redis-cli ZADD scores 12 "Steven Fletcher"

ではやってみましょう。

まずDemba Baさんの得点を取得します。16点ですね。

redis 127.0.0.1:6379> zscore scores "Demba Ba"
"16"

16点より高得点のユーザー数をカウントします。6人いますね。

redis 127.0.0.1:6379> zcount scores (16 +inf
(integer) 6

6人だったので+1して7位となります。

SQLだとこんな感じですな。

SELECT
        COUNT(name)+1 AS ranking
FROM
        scores
WHERE
        score > (
                SELECT
                        score
                FROM
                        scores
                WHERE
                        name='Demba Ba'
                )
;


では次にDemba Baさんだけじゃなくて全体のランキングを求めてみましょう。

こんなんを求めたいわけです。

name score ranking
Robin van Persie 30 1
Wayne Rooney 27 2
Kun Agüero 23 3
Yakubu Ayegbeni 17 4
Clint Dempsey 17 4
Emmanuel Adebayor 17 4
Demba Ba 16 7
Grant Holt 15 8
Edin Džeko 14 9
Papiss Demba Cissé 13 10
Mario Balotelli 13 10
Steven Fletcher 12 12


考え方は同じです。自分の得点より高得点の人が何人いるかをカウントします。

こんな感じです。

SELECT
        s1.name,
        s1.score,
        (SELECT
                COUNT(s2.score)
        FROM
                scores s2
        WHERE
                s2.score > s1.score) + 1 AS ranking
FROM
        scores s1
ORDER BY
        ranking
;

MySQLでは使えないですがPostgreSQLなら使えるOLAP関数を用いるともっと簡単に書けます。

SELECT
        name,
        score,
        RANK() OVER (ORDER BY score DESC) AS ranking
FROM
        scores
;


Redisだとzrangebyscoreの降順バージョンであるzrevrangebyscoreを使うとランキングが取れますね。

redis 127.0.0.1:6379> zrevrangebyscore scores +inf -inf
 1) "Robin van Persie"
 2) "Wayne Rooney"
 3) "Kun Ag\xc3\xbcero"
 4) "Yakubu Ayegbeni"
 5) "Emmanuel Adebayor"
 6) "Clint Dempsey"
 7) "Demba Ba"
 8) "Grant Holt"
 9) "Edin D\xc5\xbeeko"
10) "Papiss Demba Ciss\xc3\xa9"
11) "Mario Balotelli"
12) "Steven Fletcher"