mysql - 如何为 SELECT 语句中的非特定键选择 GROUPED BY 平均值

Question

我正在寻找一种方法来在 select 语句中为特定用户选择平均值，然后根据先前计算的平均值计算当前行中的方差。

User 和 Attmept_Number 是复合键。此外，这应该在 SELECT 或 WHERE 子句中完成。我知道使用 FROM (SELECT...) 的解决方案，但在这种情况下，我正在寻找不同的解决方案。

SELECT 
    User, 
    Attmept_Number, 
    (User_Score - AVG(User_Score)) As Variance
FROM Scores

通过上述语句，我得到的是列平均值，而不是用户的平均值。当我添加 GROUP BY 子句时，它会因 Composite 键而导致问题。

结果:

User   Score   Variance
1      5       -6
1      10      -1
1      15      4
3      14      3

目标:

User   Score   Variance
1      5       -5
1      10      0
1      15      5
3      14      0

Answer 1

在 MySQL 8.0 中，就像使用窗口函数 AVG(...) OVER(...) 一样简单:

SELECT
   user,
   score,
   score - AVG(score) OVER(PARTITION BY user) AS variance
FROM scores

在早期版本中，您可以使用聚合子查询来计算每个用户的平均值，然后将其与表JOIN:

SELECT
   s.user,
   s.score,
   s.score - t.avg_score AS variance
FROM scores s
INNER JOIN (
    SELECT user, AVG(score) avg_score FROM scores GROUP BY user
) t ON s.user = t.user