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

我看过很多关于这个话题的文章，比如mysql-get-rank-from-leaderboards .

然而，没有一种解决方案能够有效地从数据库中获取一系列排名。

问题很简单。假设我们有一个Postgres表，其中有一个"id"列和另一个值不唯一的INTEGER列，但是我们有这个列的索引。

。表可以是:

CREATE TABLE my_game_users (id serial PRIMARY KEY, rating INTEGER NOT NULL);

为用户定义一个等级，按照"rating"列降序排序
能够查询由这个新"排名"排序的约50个用户的列表，以任何特定用户为中心
例如，我们可能返回排名为{15,16，…， 64, 65}，其中中心用户的排名为#40
性能必须可伸缩，例如，100,000个用户的性能低于80毫秒。

尝试#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;

这个"工作"，但是在一台快速的笔记本电脑上，100,000个用户的查询平均为550毫秒，而没有完成任何其他实际工作。

我尝试添加索引，并重新措辞这个查询不使用"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;

这是不错的，这个查询大约需要120毫秒，100000个用户随机评分。但是，这只返回具有特定id(2000)的用户的排名。

我看不出任何有效的方法来扩展这个查询来获得排名的范围。任何扩展它的尝试都会导致查询非常慢。

我只知道"center"用户的ID，因为在我们知道哪些用户在范围内之前，必须按排名对用户进行排序!

尝试#3:内存中有序树

我最终使用Java TreeSet来存储排名。每当有新用户插入数据库或用户评分发生变化时，我都可以更新TreeSet。

这非常快，100,000个用户大约25毫秒。

然而，它有一个严重的缺点，即它只在服务请求的Webapp节点上更新。我正在使用Heroku，并将为我的应用部署多个节点。所以，我需要为服务器添加一个计划任务，每小时重新构建这个排名树，以确保节点不会太不同步!

如果有人知道一个有效的方法来做到这一点在Postgres与完整的解决方案，那么我洗耳恭听!

您可以使用order by rating desc, offset和limit来获得一定等级之间的用户，从而获得相同的结果。

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

谢谢@FuzzyTree !你的解决方案并没有完全满足我的需要，但它把我推向了正确的方向。这是我现在使用的完整解决方案

您的解决方案的唯一限制是无法获得特定用户的唯一排名。具有相同评级的所有用户将具有相同的排名(或者至少在SQL标准中没有定义)。如果我提前知道偏移量，那么你的排名就足够好了，但我必须先获得特定用户的排名。

我的解决方案是执行以下查询以获得排名范围:

SELECT * FROM my_game_users ORDER BY rating DESC, id ASC LIMIT ? OFFSET ?

这基本上是通过评级来唯一地定义排名，然后是谁首先加入游戏(低id)。为了提高效率，我在(评级DESC, id)

上创建一个索引

然后，我得到一个特定的用户的排名插入到这个查询:

SELECT COUNT(*) FROM my_game_users WHERE rating > ? OR (rating = ? AND id < ?)

我实际上使这个更有效率:

SELECT (SELECT COUNT(*) FROM my_game_users WHERE rating > ?) + (SELECT COUNT(*) FROM my_game_users WHERE rating = ? AND id < ?) + 1

现在，即使使用这些查询，也需要大约78ms的平均和中位数时间来获得用户周围的排名。如果有人有什么好主意，我洗耳恭听!

例如，获取一系列排名大约需要60ms，并解释它产生:

EXPLAIN SELECT * FROM word_users ORDER BY rating DESC, id ASC LIMIT 50 OFFSET 50000;

"Limit (cost=6350.28..6356.63 rows=50 width=665)" " -> Index Scan using idx_rating_desc_and_id on word_users (cost=0.29..12704.83 rows=100036 width=665)"

所以，它使用评级和id指数，但它仍然有这个高度可变的成本从0.29…12704.83。有什么改进的办法吗?

如果你按desc顺序排列，你就有了正确的顺序。使用rownumber()函数。Select Row number in postgres

还可以使用内存缓存来将内容存储在内存中。比如redis。它是一个独立的应用程序，可以为多个实例服务，甚至可以远程服务。

相关内容

最新更新

热门标签：