直接用avg()窗口函数计算每行与组内均值的差，核心是column_name - avg(column_name) over (partition by group_col)，避免group by+join；需注意null处理、分组字段含null、老版本mysql不支持及order by误导致滚动平均等问题。
用窗口函数 AVG() 直接减就行
SQL里算每行和组内平均值的差，核心就是别用聚合+JOIN，直接上窗口函数。窗口函数能保留原行结构，同时把组内统计结果“广播”到每一行，再做减法就完事了。
常见错误是先 GROUP BY 求平均，再用 JOIN 回原表——这不仅写起来麻烦，还容易因重复键或 NULL 值出错，性能也差。
- 语法结构统一：
column_name - AVG(column_name) OVER (PARTITION BY group_col) -
PARTITION BY必须明确指定分组字段，否则就是全表平均 - 如果分组字段有
NULL，默认会被归为同一组；需要排除时得加WHERE group_col IS NOT NULL - 浮点精度问题：
AVG()返回DECIMAL或FLOAT，和整数列相减可能触发隐式转换，建议显式CAST保持类型一致
PostgreSQL / MySQL 8.0+ / SQL Server 都支持，但老版本 MySQL 不行
窗口函数在 PostgreSQL 8.4+、SQL Server 2005+、Oracle 2003+ 和 MySQL 8.0+ 中都可用。如果你用的是 MySQL 5.7 或更早版本，AVG() OVER 会报错 ERROR 1064: You have an error in your SQL syntax。
此时只能退化方案：用子查询 + 关联（不推荐），或者导出数据用 Python/Pandas 处理。
- MySQL 5.7 替代写法示例：
t1.value - (SELECT AVG(t2.value) FROM table_name t2 WHERE t2.category = t1.category) - 注意子查询必须相关（带
WHERE关联外层），否则变成全表平均 - 性能差：每行都执行一次子查询，数据量稍大就明显变慢
AVG() 默认忽略 NULL，但你要小心分组后整组都是 NULL
AVG() 窗口函数天然跳过 NULL 值计算，这点和聚合函数行为一致。但如果你的某一分组里所有目标列全是 NULL，AVG() 返回 NULL，最终差值也会是 NULL —— 这不是 bug，是标准行为。
- 验证方式：运行
SELECT COUNT(*), COUNT(col), AVG(col) FROM t GROUP BY group_col，看哪些组COUNT(col) = 0 - 想把全
NULL组的差值设为 0，得加COALESCE(AVG(col) OVER (...), 0) - 如果原始列本身允许
NULL，而你希望把它当作 0 参与平均，得先COALESCE(col, 0)再套窗口函数
别漏掉 ORDER BY 在窗口定义里的影响
虽然算平均值通常不需要排序，但如果你写了 ORDER BY 在 OVER 子句里（比如为了后续累积计算），AVG() 就会变成「从第一行到当前行」的滚动平均，而不是整组平均。
- 正确写法：
AVG(value) OVER (PARTITION BY category)（无ORDER BY） - 错误写法：
AVG(value) OVER (PARTITION BY category ORDER BY id)→ 每行算的是「该组内排在它前面（含自己）的所有行」的平均 - 查数据时可以加
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING显式强调全组范围，但非必需
实际用的时候，最常被忽略的是分组字段含 NULL 和老版本 MySQL 兼容性。写完先 EXPLAIN 看执行计划，确认没走嵌套循环。
