MySQL
SQL

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

More than 3 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でもいける気がしてきたけど、現在検証できないため、後日検証して差し替えます。