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

SQL 合計値からランキングを取得する例

More than 5 years have passed since last update.

SQLが苦手な自分向けのメモ

遅い版

ユーザー毎user_idに合計値はtotal_score順にランキングを出力する例です。

SELECT user_id, total_score, (select count(total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a ORDER BY rank ASC

... 動作検証した結果、一レコードずつcountするため凄く遅いです汗.
計算量ではO(N^2)的な?

count(total_score)の場合の結果

user_id total_score rank
1191 10000 1
1 9555 2
5235 9555 2
62 9553 4
987 2222 5
SELECT user_id, total_score, (select count(DISTINCT total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a ORDER BY rank ASC

count(DISTINCT total_score)の場合の結果

user_id total_score rank
1191 10000 1
1 9555 2
5235 9555 2
62 9553 3
987 2222 4

参考

http://oshiete.goo.ne.jp/qa/8135897.html

余談

最初total_scoreをSUM(score)で求めてたけど、あきらめてtotal_score_viewを作成しました(ビューの中でユーザー毎の合計値を算出)。

イマイチ合計値をビューで表現すべきなのか、(処理負荷的に)テーブルに格納して良いのか今のところ判断ツキマセヌ...が遅くなるのが目に見えます。処理負荷を検証してみたいところです。
(追記。自分の環境で、数千件で1秒を超える程度の遅さでした.4万レコードでは7分まっても結果がでませんでした汗)

おまけ

特定のランキングの範囲を指定する例

SELECT user_id, total_score, (select count(DISTINCT total_score) FROM total_scores b WHERE a.total_score < b.total_score) + 1 rank FROM total_scores a having rank Between 70 and 80
ORDER BY rank

早い版

コメントして頂いたSQLを参考にしたバージョンです。
(コメントして頂いたものは同順位を考慮していますが、ここでは考慮していません。)

どの程度早いかというと、自分の環境で4万レコードで50ms以下程度。
計算量でいうとO(N)っいう比較的早いやつ?

全順位取得

set @c=0;
select @c:=@c+1 as rank, user_id, @p:=total_score as total_score 
from total_scores
order by total_score desc;

指定ユーザーの順位を取得する

set @c:=0; 
SELECT tmp.user_id, tmp.total_score, tmp.rank rank FROM 
(SELECT user_id, total_score, @c:=@c+1 rank FROM total_scores ORDER BY total_score DESC) tmp 
WHERE user_id=100

指定した順位を取得する

3000〜3010位を取得

set @c:=0;
SELECT tmp.user_id user_id, tmp.total_score total_score, tmp.rank rank FROM 
(SELECT user_id, total_score, @c:=@c+1 rank FROM total_scores ORDER BY total_score DESC) tmp
ORDER BY rank asc
LIMIT 3000, 10;

※ LIMITではなくてbetweenでもいける気がしてきたけど、現在検証できないため、後日検証して差し替えます。

hmuronaka
指針は「インターフェースに対してプログラミングするのであって、実装に対してプログラミングするのではない」。
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