MS Access 使用 Group By 进行舍入精度
为什么员工每个月的平均得分相加后不等于员工(曾经)的平均得分?
平均
SELECT Avg(r.score) AS rawScore
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
回报 80.737
每月平均
SELECT Avg(r.score) AS rawScore, Format(submitDate, 'mmm yy') AS MonthText, month(r.submitDate) as mm, year(submitDate) as yy
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
GROUP BY month(r.submitDate), year(submitDate), Format(submitDate, 'mmm yy')
ORDER BY year(submitDate) DESC, month(r.submitDate) DESC
回报
Average Score : Month
81.000 : Oct 09
80.375 : Sep 09
82.700 : Aug 09
83.100 : Jul 09
75.625 : Jun 09
我知道 80.737 是正确的,因为我已经手工统计了记录,并且做了平均数。但该表的平均值(小数点后 3 位)为 80.56,相差太远。 group by 是否会扰乱每一步的舍入?
Why doesn't the average of the score of an employee of each month, when summed, equal the average of the employees score (ever)?
Average
SELECT Avg(r.score) AS rawScore
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
Returns 80.737
Average By Month
SELECT Avg(r.score) AS rawScore, Format(submitDate, 'mmm yy') AS MonthText, month(r.submitDate) as mm, year(submitDate) as yy
FROM (ET INNER JOIN Employee AS e ON ET.employeeId = e.id) INNER JOIN (Employee AS a INNER JOIN Review AS r ON a.id = r.employeeId) ON ET.id = r.ETId
WHERE (((e.id)=@employeeId))
GROUP BY month(r.submitDate), year(submitDate), Format(submitDate, 'mmm yy')
ORDER BY year(submitDate) DESC, month(r.submitDate) DESC
Returns
Average Score : Month
81.000 : Oct 09
80.375 : Sep 09
82.700 : Aug 09
83.100 : Jul 09
75.625 : Jun 09
I know 80.737 is correct because I have tallied up the records by hand and done the average. But the average of this table (at 3 decimal places), is 80.56 which is too far off. Does group by mess with the rounding at each step?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
平均值的平均值不会返回与所有值的单个平均值相同的结果,除非所有平均组具有相同数量的项目。
如果每个月的员工数量不同,则 rawScore 会返回 相同的结果。会扭曲你的结果。
考虑这个例子:如果我们计算数字 1 到 10 的平均值,则平均值为 5.5。
计算 1 到 5 的平均值为 3,计算 6 到 10 的平均值为 8。两组都有 5 项,因此 3 和 8 的平均值 = 5.5。
但是,如果将第一个平均值设为 1 和 2 = 1.5,将第二个平均值设为 3 到 10 = 6.5,则平均值 1.5 和 6.5 得出 4。这是有偏差的,因为第一组有 2 个项目,第二组有 8 个项目 。
除此之外,罗伯特·哈维还指出了舍入的累积效应
An average of average values will not return the same result as a single average over all values, unless all the groups averaged have the same number of items.
If there are different numbers of employees rawScore each month it will be skewing your results.
Consider this example: if we calculate the average of the numbers 1 through 10 the average is 5.5.
Calculating the average of the numbers from 1 through 5 the average is 3, and of 6 through 10 is 8. Both groups have 5 items so the average of 3 and 8 = 5.5.
However, if you take the first average as 1 and 2 = 1.5, and the second average as 3 through 10 = 6.5, then average 1.5 and 6.5 gives 4. This is skewed because the first group has 2 items, and the second has 8.
In addition to this will be the cumulative effects of rounding that Robert Harvey noted.
我不希望这两个结果相同,原因很简单,如果发生舍入,则每月分数将舍入五次,而每年仅舍入一次。
也就是说,我也会检查记录计数,看看它们是否一致。考虑到日期的格式等,每月查询的裂缝中可能会漏掉一两条记录。
I wouldn't expect the two results to be the same, for the simple reason that, if rounding is occurring, you're rounding five times in the monthly scores, and only once for the yearly.
That said, I would check the record counts also, and see if they jibe. It is possible given the formatting on the date and such, that a record or two is slipping through the cracks on the monthly queries.