我已经阅读了许多关于这个话题的帖子,比如
mysql-get-rank-from-leaderboards.
mysql-get-rank-from-leaderboards.
问题很简单假设我们有一个带有“id”列和另一个INTEGER列的Postgres表,其值不唯一,但是我们有一个此列的索引.
例如表可以是：
CREATE TABLE my_game_users (id serial PRIMARY KEY,rating INTEGER NOT NULL);
目标
>定义用户在“评级”列下降订购用户的排名
>能够查询以任何特定用户为中心的新“排名”排序的〜50位用户的列表
>例如,我们可能会返回排名为{15,16,…,64,65}的用户,其中中心用户的排名是#40
性能必须扩大,例如10万用户不到80 ms.
尝试#1：row_number()窗口函数
WITH my_ranks AS (SELECT my_game_users.*,row_number() OVER (ORDER BY rating DESC) AS rank FROM my_game_users) SELECT * FROM my_ranks WHERE rank >= 4000 AND rank <= 4050 ORDER BY rank ASC;
这个“工作”,但查询平均550ms与快速笔记本电脑上的100,000用户没有任何其他真正的工作完成.
我尝试添加索引,并重新使用该查询以不使用“WITH”语法,没有任何工作来加速.
尝试#2 – 计算具有更高评级值的行数
我尝试过这样的查询：
SELECT t1.*,(SELECT COUNT(*) FROM my_game_users t2 WHERE (t1.rating,-t1.id) <= (t2.rating,-t2.id) ) AS rank FROM my_game_users t1 WHERE id = 2000;
这是体面的,这个查询需要大约120ms,100,000用户具有随机评分.但是,这只能返回具有特定id(2000)的用户的排名.
我看不到任何有效的方法来扩展这个查询以获得一系列的排名.任何尝试扩展这个查询都是非常缓慢的.
我只知道“中心”用户的ID,因为用户必须按照排序顺序排列,才知道哪个用户在范围内！
尝试#3：内存中有序树
我最终使用Java TreeSet来存储这个排名.每当将新用户插入数据库或用户的评级更改时,我可以更新TreeSet.
这是超快的,大约25毫秒与100,000用户.
但是,它仅在服务请求的Webapp节点上更新.我使用Heroku,并将为我的应用程序部署多个节点.因此,我需要为服务器添加一个计划任务,以便每小时重新构建此排名树,以确保节点不会太同步！
您可以通过使用order by rating和offset来获得相同的结果,并限制使用户在某个等级之间.
WITH my_ranks AS
(SELECT my_game_users.*,row_number() OVER (ORDER BY rating DESC) AS rank FROM my_game_users)
SELECT * FROM my_ranks WHERE rank >= 4000 AND rank <= 4050 ORDER BY rank ASC;
上面的查询是一样的
select *,rank() over (order by rating desc) rank from my_game_users order by rating desc limit 50 offset 4000
如果你想选择排名#40的用户,你可以选择排名#15-#65
select *,rank() over (order by rating desc) rank from my_game_users order by rating desc limit 50 offset 15
