MS访问舍入精度与分组 [英] MS Access Rounding Precision With Group By

问题描述

为什么每个月的员工总分平均值等于员工平均分数（有史以来）？为什么每个月的员工得分平均值不变（？）？b
$ b

平均值

  SELECT平均值（r.score）AS rawScore 
 FROM（ET INNER JOIN Employee AS e ON ET.employeeId = e.id）INNER JOIN（作为内部加入的员工查看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（雇员作为内部联接查看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'）
 OR DER BY year（submitDate）DESC，month（r.submitDate）DESC 
  

 平均分数：月
 81.000：10月09 
 80.375：09年9月
 82.700：Aug 09 
 83.100：Jul 09 
 75.625：Jun 09 
  

I知道80.737是正确的，因为我手工记录了记录并完成了平均值。但这张表的平均值（小数点后三位）是80.56，这太离谱了。是不是每组都乱成一团？

解决方案

平均值不会返回相同的结果作为所有值的单一平均值，除非所有组的平均值相同。

如果每月有不同数量的员工，将会扭曲你的结果。考虑这个例子：如果我们计算数字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。因为第一组有两项，第二组有8项。除此之外，罗伯特哈维指出的四舍五入的累积效应。

>

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?

解决方案

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.

这篇关于MS访问舍入精度与分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！