avg()窗口函数可计算组内平均值并逐行做差，需省略group by；支持partition by分组和order by动态累计；注意null处理、索引优化及数据库版本兼容性。
用 AVG() 窗口函数计算组内平均值并做差
直接在 SELECT 中用 AVG(column) OVER (PARTITION BY group_col) 得到每行所属分组的平均值，再和当前行值相减即可。窗口函数不会压缩行数，所以能逐行对比。
常见错误是误写成聚合查询（比如加了 GROUP BY），那样就只剩一行结果，没法“当前行 vs 组均值”了。
- 必须省略
GROUP BY，否则窗口函数被忽略或报错 -
PARTITION BY的列要和业务分组逻辑一致，比如按部门、日期、用户ID等 - 若需排除空值影响，
AVG()默认自动跳过NULL，但原始列本身是NULL时，差值会变成NULL
SELECT id, score, dept, score - AVG(score) OVER (PARTITION BY dept) AS diff_from_dept_avg FROM exam_results;
处理排序依赖：当需要“截至当前行”的动态均值时
如果需求不是静态组均值，而是“按时间排序后，从第一行到当前行的累计平均”，就得加上 ORDER BY 和 ROWS UNBOUNDED PRECEDING。
这时候 AVG() 的行为变了：它只看当前行及之前所有行，不再是整个分组。不加 ORDER BY 时，ORDER BY 子句无效；加了但没指定 ROWS 或 RANGE，多数数据库（如 PostgreSQL、SQL Server）会默认为 RANGE UNBOUNDED PRECEDING，可能引发重复值合并问题。
- 显式写
ROWS UNBOUNDED PRECEDING更安全，语义清晰且跨库兼容性好 - MySQL 8.0+ 支持，但旧版不支持窗口函数，会直接报错
ERROR 1064 - 如果原始数据有相同排序键，
RANGE模式可能导致“同序多行同时纳入”，而ROWS严格按物理顺序
SELECT
log_time,
revenue,
AVG(revenue) OVER (
PARTITION BY DATE(log_time)
ORDER BY log_time
ROWS UNBOUNDED PRECEDING
) AS running_avg,
revenue - AVG(revenue) OVER (
PARTITION BY DATE(log_time)
ORDER BY log_time
ROWS UNBOUNDED PRECEDING
) AS diff_from_running_avg
FROM daily_sales;
避免 NULL 干扰差值计算的实操细节
只要参与运算的任一端是 NULL（比如某行 score 为 NULL，或分组全为 NULL 导致 AVG() 返回 NULL），差值就是 NULL。这不是bug，是SQL三值逻辑决定的。
业务上常需要把这种差值标为 0 或标记为异常，不能靠前端补救——因为窗口函数结果已定型。
- 用
COALESCE(score, 0)强制补零，但要注意：这会扭曲真实均值（0 参与了平均计算） - 更合理的是先过滤或标记：
CASE WHEN score IS NULL THEN 'missing' ELSE CAST(score - AVG(score) OVER (...) AS DECIMAL(10,2)) END - PostgreSQL 可用
AVG(COALESCE(score, 0))，但语义已变为“把缺失当0算均值”，和业务定义是否一致得确认
性能与索引注意事项
窗口函数本身不走索引，但 PARTITION BY 和 ORDER BY 列如果有对应复合索引，能显著加速排序和分组过程。特别是大数据量下，没索引时可能触发磁盘临时表甚至 OOM。
- 推荐建索引：
CREATE INDEX idx_dept_time ON exam_results(dept, log_time);（如果同时用于PARTITION BY dept ORDER BY log_time） - MySQL 对
PARTITION BY列无索引优化能力，仅ORDER BY部分受益；PostgreSQL 和 BigQuery 两者都可利用 - 避免在窗口函数里套复杂子查询或 UDF，它们会在每行重复执行，放大开销
差值本身只是减法，真正慢的永远是前面那个 AVG() OVER 的分组扫描和排序——别在最后一步优化，要从前置条件入手。
