SQL查询与存储：为每行计算排除自身外其余行的积分平均值

核心计算逻辑 #

• 无需通过自连接逐行关联全表做嵌套聚合，可通过全局统计值直接推导目标结果，查询效率远高于常规自连接写法
• 数学推导公式：排除当前行的积分平均值 = (全表所有用户总积分 - 当前行自身积分) / (全表用户总数 - 1)
• 以上公式和题目要求的“剔除当前行后对剩余行求平均”的计算结果完全等价

输出查询结果的SQL实现 #

基于示例user_points表结构，直接查询输出包含AVG列的结果集：

SELECT
    u.USER_ID,
    u.POINTS,
    -- 可根据精度需求调整ROUND的小数保留位数
    ROUND((global_stat.total_points - u.POINTS) * 1.0 / (global_stat.total_user_cnt - 1), 0) AS AVG
FROM user_points u
CROSS JOIN (
    -- 一次全表扫描计算全局统计值
    SELECT
        SUM(POINTS) AS total_points,
        COUNT(USER_ID) AS total_user_cnt
    FROM user_points
) global_stat;

兼容说明：如果使用的数据库不支持CROSS JOIN语法，可将全局统计值作为标量子查询嵌入SELECT子句实现完全相同的逻辑：

SELECT
    u.USER_ID,
    u.POINTS,
    ROUND(
        ((SELECT SUM(POINTS) FROM user_points) - u.POINTS) * 1.0 
        / ((SELECT COUNT(USER_ID) FROM user_points) - 1),
        0
    ) AS AVG
FROM user_points u;

示例数据验证 #

按题目给出的7条示例数据计算：

• 全表总积分 = 134 + 40 + 149 + 67 + 124 + 85 + 172 = 771
• 全表用户总数 = 7
• 对user_id = a14e43e4f851的行计算：(771 - 134) / (7 - 1) ≈ 106，和示例给出的参考计算结果完全匹配

将计算结果存入表的SQL实现 #

如果需要把计算得到的平均值作为独立列存储到原表中，可执行如下更新语句：

UPDATE user_points u
CROSS JOIN (
    SELECT
        SUM(POINTS) AS total_points,
        COUNT(USER_ID) AS total_user_cnt
    FROM user_points
) global_stat
-- 为表中提前新增的avg_points列赋值
SET u.AVG_POINTS = (global_stat.total_points - u.POINTS) * 1.0 / (global_stat.total_user_cnt - 1);

方案优势 #

• 仅需一次全表扫描即可完成全局统计，不存在自连接带来的笛卡尔积计算开销，数据量越大性能优势越明显
• 计算逻辑无偏差，完全匹配业务规则

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