最近的项目连续遇到了对用户排名的需求,一个要求不用并列,一个要求同分并列,因为用户体量的考虑,拒绝了实时更新(即每个用户查询排名时获取排名),改为定时更新,在网上查了大量的MySQL排名算法之后,发现大部分的文章是以查询时获取来实现,而非定时更新所有人的排名,所以经过一番研究之后自己写了一个算法,欢迎斧正

首先我们假设有一张user表,其中有四个字段,score,rank,score_rank,join_time分别代表用户的分数,排名,获取排名时的分数,及用户加入时间

首先是不考虑并列的情况

SET @r = 0
UPDATE user
SET `rank` = @r := (@r + 1), `score_rank` = `score`
WHERE `score` != 0
ORDER BY `score` DESC, `join_time` DESC // 因为没有并列,所以增加一个排序

然后是考虑并列的情况

SET @r = 0;
SET @l = 0;
SET @s = 1;
UPDATE `user` SET `rank` = (
CASE
WHEN `score` = @l THEN @r := @r + (@s := @s + 1) - @s
ELSE @r := (@r + @s) + (@s := 1) - 1 END), `score_rank` = @l := (`score`)
WHERE `score` != 0
ORDER BY `score` DESC

这里唯一需要注意的事情就是MySQL中的变量问题,变量只能在两个地方赋值,SET或SELECT时,其余的地方只允许在变量赋值给其他字段时才允许被修改,所以就是在这里我们开始取巧,来解释一下并列时的思想:

r,l,s分别是当前排名,当前排名的分数,和当前同分的人数

当某一行的数据与上一行的分数相同时,即 score = @l,那么这时应该给rank赋值为当前的排名,但同时应该更新同分人数,所以我们这样来实现:@r := @r + (@s := @s + 1) – @s,这样s实现了自增,但对返回结果r没有影响

当某一行的数据与上一行的分数不同时,则应该给rank赋值r+1,但同时我们希望给l赋值当前的分数,并把同分人数s归为1,则选择这样实现:@r := (@r + @s) + (@s := 1) – 1

关于速度

这个算法在我这边的模拟测试中,三万条的数据排序用时0.4秒,所以肯定不能作为实时处理,计划是每天定时更新一回,操作时间也算是可以接受了。不知道其他算法的时间是会是多少,欢迎提供下对比。