如何使用Postgresql有效地获得一系列排名用户(排行榜)

前端之家收集整理的这篇文章主要介绍了如何使用Postgresql有效地获得一系列排名用户(排行榜)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我已经阅读了许多关于这个话题的帖子,比如
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,并将为我的应用程序部署多个节点.因此,我需要为服务器添加一个计划任务,以便每小时重新构建此排名树,以确保节点不会太同步!

如果有人知道在Postgres中有效的方法可以全面解决问题,那么我都是耳朵!

您可以通过使用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
原文链接:https://www.f2er.com/postgresql/191821.html

猜你在找的Postgre SQL相关文章