如何在Access中按员工分组计算TRIMMEAN值

我之前在处理Access的分组截尾均值时也遇到过一模一样的问题，毕竟Access确实没有内置的TRIMMEAN函数，不过咱们可以把你现有的全局查询改造成按员工分组的版本，一步步来就行：

1. 改造TOP 10%查询，实现按员工分组取首尾10%记录 #

你现有的qry_data_TOP10_ASC和qry_data_TOP10_DESC是全局的，现在要改成对每个员工单独计算首尾10%。我们用Access支持的窗口函数（需Access 2010及以上版本）来实现精准分组：

① 按员工取最低10%记录（qry_data_TOP10_ASC_Grouped） #

SELECT EmployeeID, YourValueField
FROM (
    SELECT 
        EmployeeID, 
        YourValueField,
        -- 按员工分组，对数值升序排号
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY YourValueField ASC) AS RowNum,
        -- 统计每个员工的总记录数
        COUNT(*) OVER (PARTITION BY EmployeeID) AS TotalRows
    FROM YourOriginalTable
) AS SubQuery
-- 取前10%（向上取整，比如11条记录就取2条）
WHERE RowNum <= CEILING(TotalRows * 0.1)

② 按员工取最高10%记录（qry_data_TOP10_DESC_Grouped） #

把上面的ORDER BY YourValueField ASC改成DESC即可：

SELECT EmployeeID, YourValueField
FROM (
    SELECT 
        EmployeeID, 
        YourValueField,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY YourValueField DESC) AS RowNum,
        COUNT(*) OVER (PARTITION BY EmployeeID) AS TotalRows
    FROM YourOriginalTable
) AS SubQuery
WHERE RowNum <= CEILING(TotalRows * 0.1)

2. 合并首尾10%的记录（unionqry_TOP10_ASCandDESC_Grouped） #

用UNION合并两个分组后的查询，加DISTINCT避免当员工记录数为10的倍数时，首尾记录重复的情况：

SELECT DISTINCT EmployeeID, YourValueField
FROM qry_data_TOP10_ASC_Grouped
UNION
SELECT DISTINCT EmployeeID, YourValueField
FROM qry_data_TOP10_DESC_Grouped

3. 筛选出中间80%的记录（qry_data_ALL_minus_union_qry_Grouped） #

通过左连接排除每个员工的首尾10%记录，得到用于计算均值的中间数据：

SELECT t.EmployeeID, t.YourValueField
FROM YourOriginalTable t
LEFT JOIN unionqry_TOP10_ASCandDESC_Grouped u 
    ON t.EmployeeID = u.EmployeeID 
    AND t.YourValueField = u.YourValueField
WHERE u.YourValueField IS NULL

4. 按员工计算截尾均值 #

最后对中间的80%记录按员工分组，用Avg()函数得到每个员工的TRIMMEAN值：

SELECT 
    EmployeeID, 
    Avg(YourValueField) AS TrimmedMean
FROM qry_data_ALL_minus_union_qry_Grouped
GROUP BY EmployeeID

注意事项 #

• 替换代码中的EmployeeID为你实际的员工唯一标识字段，YourValueField为需要计算截尾均值的数值字段，YourOriginalTable为你的原始数据表名。
• 如果你的Access版本不支持窗口函数（比如2007及更早），可以用子查询嵌套的方式实现分组TOP，原理是对每个员工单独统计记录数后再筛选。

内容的提问来源于stack exchange，提问作者Vedran