mysql - MYSQL如何查找 "Most Consistent"执行者

Question

所以我有一个排行榜，我每天使用以下查询有效地获取每个用户的分数:

SELECT DATE(a.time) as time, a.userid, SUM(activity_weight) as weight

FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid 

WHERE competitionId = '$competitionId' GROUP BY a.userid, DATE(time) 

ORDER BY time ASC

我想知道，什么是找到“最稳定”表现者的有效方法，即在整个比赛期间每天平均得分最高的用户。

非常感谢!

编辑:测试这个，但有问题:

SELECT a.userid,
DATE(a.time) as time,
AVG(activity_weight) AS daily_average,

(SELECT a.userid, DATE(a.time) as time,
AVG(AVG(daily_average)) as topAverage
FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid
WHERE competitionId = '$competitionId'
)

FROM activity_entries a INNER JOIN users1 u ON u.id = a.userid
WHERE competitionId = '$competitionId'
GROUP BY userid, time

Answer 1

完全基于您提供的内容，并假设 activity_weight 是特定用户在单个事件中的得分，然后从类似以下内容开始:

SELECT userid,
       time,
       AVG(activity_weight) AS daily_average
FROM activity_entries
GROUP BY userid, time

此查询返回每个用户的平均每日得分。现在，我们需要定义“最一致”。如果可接受的定义是“日均值的最高平均值”，则将上述查询作为子查询进行内部联接，选择 AVG(daily_average)。

如果“最一致”需要涉及检查标准偏差，或任何比平均值更复杂的数学运算，那么您应该在前端程序中执行此操作，而不是在数据库中执行。

编辑:尝试这个查询(或在这里尝试一下:SQLFiddle link):

SELECT davg.userid,
   SUM(davg.daily_average*davg.num_of_activities)/SUM(davg.num_of_activities) AS weighted_total_average
FROM (
  SELECT userid,
         time,
         AVG(activity_weight) AS daily_average,
     COUNT(*) AS num_of_activities
  FROM activity_entries
  GROUP BY userid, time
) AS davg
GROUP BY davg.userid
ORDER BY AVG(davg.daily_average) DESC

数学的实际细节由您决定，但这里有一个加权平均值的示例，其中竞争对手参加大量比赛的日子比懒惰的日子更有值(value)。