用avg()窗口函数计算组内均值并做差，需写为avg(val) over (partition by category)，确保与当前行对齐；默认忽略null，偏差结果遇null亦为null；推荐cte预计算、round控制小数位、校验分组键语义一致性。
用 AVG() 窗口函数计算组内均值并做差
直接在 SELECT 中用 AVG(column) OVER (PARTITION BY group_col) 得到每行所属分组的平均值，再与当前行值相减即可。关键不是“怎么算平均”，而是“怎么确保对齐”——窗口函数结果与当前行一一对应，天然支持逐行偏差计算。
常见错误是把 AVG() 写成聚合函数（没加 OVER），导致报错 ERROR: column "x" must appear in the GROUP BY clause 或直接语法失败。
- 必须写成
AVG(val) OVER (PARTITION BY category)，不能写成AVG(val) -
PARTITION BY的列要和业务分组逻辑一致，比如按product_type分组就别错写成region - 如果需要排除空值影响，
AVG()默认自动忽略NULL，但若字段含0且业务上需区分“无数据”和“零值”，得提前用CASE WHEN过滤
处理 NULL 值导致的偏差失真
当某行 val 是 NULL，而同组其他行有值时，val - AVG(...) 结果仍是 NULL——这不是 bug，是 SQL 的三值逻辑。但如果你希望缺失值也参与偏差分析（比如标记为“偏离未知”），就得显式干预。
- 用
COALESCE(val, 0)强制补零：适合业务允许用默认值替代的场景 - 用
CASE WHEN val IS NULL THEN 'N/A' ELSE CAST(val - AVG(...) OVER (...) AS NUMERIC(10,2)) END：保持类型安全，避免混合NULL和数值 - 注意：
AVG()自身跳过NULL，所以分母是“非空行数”，不是总行数；若需分母恒定，得改用SUM(val) / COUNT(*)并手动处理除零
性能敏感时避免重复计算窗口表达式
如果既要偏差值，又要偏差绝对值、标准化得分（如 (val - avg) / stddev），别反复写三遍 AVG(...) OVER (...)。数据库优化器不一定能自动复用，尤其在 PostgreSQL 14 之前或复杂嵌套查询中。
- 用 CTE 提前算好：
WITH stats AS ( SELECT *, AVG(val) OVER (PARTITION BY category) AS grp_avg FROM sales ) SELECT *, val - grp_avg AS diff FROM stats;
- 在支持列别名引用的引擎（如 PostgreSQL、BigQuery）中，也可用子查询包裹，外层直接引用别名
- MySQL 8.0+ 支持
WINDOW子句定义重用窗口框架，例如：WINDOW w AS (PARTITION BY category)，后续所有AVG(val) OVER w共享同一定义
用 ROUND() 控制小数位避免浮点误差干扰判断
原始偏差可能带多位小数（如 -0.0000000001），肉眼难识别是否真为 0；下游应用做阈值判断（如 ABS(diff) > 0.5）时，浮点误差可能导致误判。
- 统一用
ROUND(val - AVG(val) OVER (...), 2)截断到两位小数，既可读又防误差 - 不要用
CAST(... AS DECIMAL(10,2))替代ROUND()：前者是截断，后者是四舍五入，业务含义不同 - 若分组数据量极小（如每组仅 2–3 行），
AVG()计算本身精度足够，重点反而是检查原始数据是否已存在精度损失（如从 float 转入）
真正容易被忽略的是分组键的语义一致性——比如用 DATE(created_at) 分组时，时区未归一会导致同一天的数据散落在多个组里，算出来的“日均值偏差”就失去意义。先确认 PARTITION BY 的字段逻辑干净，再调窗口函数。
