MySQL 过去几天均值 当天_MYSQL-正确的查询以获得7天中前5天的平均值?

这是我当前使用的查询,但我发现结果不正确。这是平均7天,而不是选择有最多步骤的5天。根据我在sqlfiddle中发布的数据,它平均输出14122,而不是11606。

$stmt = $connection->prepare("

SELECT SUM(a.steps) as StepsTotal, AVG(a.steps) AS AVGSteps

FROM (SELECT * FROM activities

JOIN Courses

WHERE activities.encodedid=? AND activities.activitydate BETWEEN

DATE_ADD(Courses.Startsemester, INTERVAL $y DAY) AND

DATE_ADD(Courses.Startsemester, INTERVAL $x DAY)

ORDER BY activities.steps DESC LIMIT 5

) a

GROUP BY a.encodedid

");

下面是同一个查询,其中填写了用于测试的值:

SELECT SUM(a.steps) as StepsTotal, AVG(a.steps) AS AVGSteps

FROM (SELECT * FROM activities

JOIN Courses

WHERE activities.encodedid='42XPC3' AND activities.activitydate BETWEEN

DATE_ADD(Courses.Startsemester, INTERVAL 0 DAY) AND

DATE_ADD(Courses.Startsemester, INTERVAL 6 DAY)

ORDER BY activities.steps DESC LIMIT 5

) a

GROUP BY a.encodedid

任何帮助都将不胜感激。

谢谢,