时间戳

首页 > 解决方案 > 在 MySQL 中具有排名的加权 SUM

mysql - 在 MySQL 中具有排名的加权 SUM

问题描述

当总和根据某种顺序由每行的排名加权时，我一直在尝试为表的每个用户计算总和。这是在视频游戏的背景下，最高分值 100%，第二个值 95%，第三个值 (0.95 * 0.95) * 100%，依此类推。

该公式可以简单地描述为SUM(score * POW(0.95, score's position))，但我尝试的一切似乎都达到了 MySQL 的限制。

首先，此选项不起作用，因为它似乎在ORDER BY pls.Score DESCSUM 之后被调用（SUM 似乎没有通过更改顺序而改变）。

SELECT p.PlayerId, p.Username, 
        (SELECT SUM(pls.Score * POW(0.95, @i := if(@c = p.PlayerId, @i + 1, if(@c := p.PlayerId, 0, 0))))
        FROM player_level_stats pls, (SELECT @i := -1, @c := '') params
        WHERE pls.PlayerId = p.PlayerId
        ORDER BY pls.Score DESC) * 10 AS Score
FROM player p
ORDER BY 3 DESC;

我也必须使用这个变量 mess 因为我不能ROW_NUMBER() OVER ()在SUM().

现在好的，我接下来要尝试的是在外部查询中求和，这样我就可以在求和之前进行排序：

SELECT p.PlayerId, p.Username, 
        (SELECT SUM(t.Score)
        FROM (SELECT pls.Score * POW(0.95, @i := if(@c = p.PlayerId, @i + 1, if(@c := p.PlayerId, 0, 0)))
            FROM player_level_stats pls, (SELECT @i := -1, @c := '') params
            WHERE pls.PlayerId = p.PlayerId
            ORDER BY pls.Score DESC) AS t) * 10 AS Score
FROM player p
ORDER BY 3 DESC
LIMIT 50;

理论上听起来不错，但实际上有一个 MySQL 限制，您不能将参数传递给嵌套子查询（有关此问题的答案基本上告诉您尝试其他方法）。由于嵌套子查询位于 中FROM，因此似乎很难将某些内容传递给中间查询。

当然总会有慢的SUM(pls.Score * POW(0.95, (SELECT COUNT(*) FROM player_level_stats ipls WHERE pls.Score > ipls.Score)))，但这是 O(number of scores ^ 2) 而不是 O(number of scores * log(number of scores)) 使整个事情变得更慢。

有没有办法在 MySQL 中做到这一点？或者有什么办法可以用 MariaDB 做到这一点？

编辑：这是一个甚至最小的版本（也不起作用）

SELECT p.PlayerId, (SELECT SUM(pls.Score * (RANK() OVER (ORDER BY pls.Score DESC)))
        FROM player_level_stats pls
        WHERE pls.PlayerId = p.PlayerId
        ORDER BY pls.Score DESC) AS WeightedScore
FROM player p;

这只是将每个玩家的得分乘以该得分的排名（最佳得分 * 1 + 第二最佳得分 * 2 + ... n 最佳得分 * n）。这本质上是相同的问题，但简化了。

标签： mysqlsumrankrow-number

解决方案

如果我正确理解了这个问题，你可以做的是一个带有总和的子查询，然后row_number() over(order by)用来获得排名。像这样的东西：

SELECT playerID, SUM(score * weighted) AS totalscore
FROM
(
 SELECT 
  playerID, score, POW(0.95,(ROW_NUMBER() OVER(partition by playerID ORDER BY score desc)-1)) as weighted
  FROM player_level_stats
 ) AS totals
GROUP BY playerID

推荐阅读