如何编写SQL计算分组内各成员对应其余成员的平均值

架构老林

2026-8-29

分组排除当前行计算其余成员均值的SQL实现

这个需求完全可以通过标准窗口函数实现，不需要写低效的自连接逻辑，核心计算逻辑为：
排除当前行的组内均值 = (分组内所有数据总和 - 当前行数据值) / (分组内总记录数 - 1)

实现步骤

第一步：通过窗口函数按group字段分区，计算每个分组的总数据和、分组总记录数，这一步不需要提前排除当前行
第二步：用分组总和扣减当前行的data值、分组总记录数扣减1（排除当前行），两者相除就得到排除当前成员后的组内均值
边界处理：如果某个分组只有1条记录，分母会为0，需要用CASE语句做兼容，可根据业务需求返回NULL或者0

可直接运行的SQL代码

以下写法兼容所有支持标准窗口函数的SQL引擎（MySQL 8.0+、PostgreSQL、Hive、Spark SQL、ClickHouse等）：

SELECT
    item,
    CASE
        WHEN group_cnt = 1 THEN NULL -- 分组仅1条数据时无其余成员，默认返回NULL，可按需调整
        ELSE (group_sum - data) / (group_cnt - 1)
    END AS groupMean
    -- 如果需要计算当前值和组内其余均值的差值，直接加下面这行即可
    -- , data - (group_sum - data) / (group_cnt - 1) AS diff
FROM (
    SELECT
        item,
        `group`,
        data,
        SUM(data) OVER (PARTITION BY `group`) AS group_sum,
        COUNT(1) OVER (PARTITION BY `group`) AS group_cnt
    FROM your_source_table -- 替换成你的实际表名
) t;