因此,我有一个排行榜,我有效地获取每个用户的分数,每天使用以下查询:
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
最佳答案
仅基于您提供的内容,并假设activity_weight是特定用户在单个事件上的得分,然后从以下内容开始:
SELECT userid,
time,
AVG(activity_weight) AS daily_average
FROM activity_entries
GROUP BY userid, time
此查询返回每个用户的平均每日分数。现在,我们需要定义“最一致”。如果一个可接受的定义是“日平均值的最高平均值”,那么在上面的查询中选择inner join作为子查询,选择
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
数学的实际细节由你决定,但这里有一个加权平均数的例子,在这个例子中,一个竞争对手参加大量比赛的天数比懒惰的天数更值钱。