我需要从tblQAScore获取表格中过去365天内前90%记录的平均得分 .
表格(仅限选定字段):
|Technician |Date |Score |
|1 |1/5/17 |8 |
|1 |1/6/17 |9 |
|1 |1/7/17 |7 |
|1 |1/9/17 |8 |
|1 |1/1/18 |10 |
|1 |1/3/18 |9 |
|1 |1/1/17 |8 |
|1 |1/5/17 |9 |
|1 |1/6/17 |7 |
|1 |1/9/17 |8 |
|2 |1/1/17 |8 |
|2 |1/2/17 |7 |
|2 |1/6/17 |9 |
|2 |1/9/17 |8 |
|2 |1/1/18 |10 |
|3 |1/3/18 |9 |
|3 |1/1/17 |8 |
|3 |1/5/17 |9 |
|3 |1/6/17 |7 |
|3 |1/9/17 |8 |
预期的查询结果
|Technician |AvgOfScore |
|1 |8.44 | 'drop lowest of 10 scores before calculating avg
|2 |9 | 'only 3 scores in last 365 days so none dropped
|3 |8.2 | 'only 5 scores total so none dropped
最近我添加了两个在tblQAScore中查找的新技术人员,两人都有记录,所有人都以“10”(最大值)作为他们的分数 . 出于某种原因,这些不出现在我的前90%平均查询中......
SELECT TOP 90 PERCENT tblQAScore.Technician, Avg(tblQAScore.Score) AS AvgOfScore
FROM tblQAScore
WHERE (((tblQAScore.Exclude)=False) AND ((tblQAScore.QADate)>Date()-365))
GROUP BY tblQAScore.Technician
ORDER BY Avg(tblQAScore.Score);
这意味着平均值也不会出现在用户表单上,这就是重点 .
问题是因为这两个人是新技术人员,所以他们的ID是一个更高(更晚)的数字,从最高的90%降到最高?如果是这样,我如何确保它按分数排在前90%的tblQAScore记录中?
编辑:经过进一步的游戏,部分受到评论的启发,很明显,10%的技术人员的底部(以前最高的)是被淘汰的 . 如何降低每个技术人员得分的最低10%?