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 |
参考
余談
最初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でもいける気がしてきたけど、現在検証できないため、後日検証して差し替えます。