动态用户排名

发布于 2024-10-16 09:43:04 字数 452 浏览 0 评论 0原文

我有一个基本的业力/代表系统，根据用户的活动（问题、答案等）奖励用户。我想根据用户的积分来排名（标题）。不同的等级有不同的限制和授予的权力。

排名表

id    rankname   points   questions_per_day
 1    beginner   150      10
 2    advanced   300      30

我不确定是否需要有下限和上限，但为了简单起见，我只留下了最高分限制，也就是说，低于 150 的用户是“初学者”，低于或高于300，他是‘高级’了。

例如，拥有 157 分的鲍勃的用户名将显示“高级”标签。

如何确定并显示用户的等级/头衔？我是否循环遍历每一行并比较值？

如果我将其扩展到数千个以这种方式计算排名的用户，可能会出现什么问题？每次请求用户的排名时，它肯定会给系统带来查询和循环的负担，不是吗？

原文

I have a basic karma/rep system that awards users based on their activities (questions, answers, etc..). I want to have user ranks (title) based on their points. Different ranks have different limitations and grant powers.

ranks table

id    rankname   points   questions_per_day
 1    beginner   150      10
 2    advanced   300      30

I'm not sure if I need to have a lower and upper limit, but for the sake of simplicity I have only left a max points limit, that is, a user below 150 is a 'beginner' and below or higher than 300, he's an 'advanced'.

For example, Bob with 157 points would have an 'advanced' tag displayed by his username.

How can I determine and display the rank/title of an user? Do I loop through each row and compare values?

What problems might arise if I scale this to thousands of users having their rank calculated this way? Surely it will tax the system to query and loop each time a user's rank is requested, no?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

北城半夏 2024-10-23 09:43:04

您可以更好地缓存排名和分数。如果用户的分数仅在执行某些活动时发生变化，您可以对该活动设置触发器。当分数发生变化时，您可以重新计算排名并将其保存在用户记录中。这样，检索排名就很简单了，您只需要在分数发生变化时进行计算即可。

你可以这样获取匹配的排名id；查询最接近（但低于或等于）用户分数的排名。将此排名 ID 存储在用户记录中。
我添加了伪变量 {USERSCORE} 因为我不知道您是否使用参数或任何其他方式在查询中输入值。

select r.id
from ranks r
where r.points <= {USERSCORE}
order by r.points desc
limit 1

You could better cache the rank and the score. If a user's score only changes when they do certain activities, you can put a trigger on that activity. When the score changes, you can recalculate the rank and save it in the users record. That way, retreiving the rank is trivial, you only need to calculate it when the score changes.

You can get the matching rank id like this; query the rank that is closest (but below or equal to) the user schore. Store this rank id in the user's record.
I added the pseudovariable {USERSCORE} because I don't know if you use parameters or any other way to enter values in a query.

select r.id
from ranks r
where r.points <= {USERSCORE}
order by r.points desc
limit 1

回复收藏 0 原文

昔日梦未散 2024-10-23 09:43:04

如果不知道你的模式，有点困难。尝试：

SELECT user.id, MIN(ranks.id) AS rankid FROM user JOIN ranks ON (user.score <= ranks.points) GROUP BY user.id;

现在您知道了排名 ID。

但这并不简单（GROUP BY 和 MAX 是管道破坏者，因此是相当重量级的操作），因此 GolezTrol 的建议很好；您应该缓存此信息并仅在用户分数发生变化时更新它。触发器听起来很适合这个。

A little difficult without knowing your schema. Try:

SELECT user.id, MIN(ranks.id) AS rankid FROM user JOIN ranks ON (user.score <= ranks.points) GROUP BY user.id;

Now you know the ranks id.

This is non-trivial though (GROUP BY and MAX are pipeline breakers and so quite heavyweight operations), so GolezTrol advice is good; you should cache this information and update it only when a users score changes. A trigger sounds fine for this.

回复收藏 0 原文

~没有更多了~