怎样在SQL中用AVG OVER计算每个产品的偏离均值-SQL-PHP中文网

AVG() OVER() 不能直接计算“偏离均值”，必须先用 AVG() OVER(PARTITION BY ...) 计算分组均值，再用当前行值减去该均值；加 ORDER BY 会导致累计均值而非分组均值，引发逻辑错误。

怎样在sql中用avg over计算每个产品的偏离均值

AVG OVER 不能直接算“偏离均值”，得拆成两步

SQL 中 AVG() OVER() 只能计算滚动均值或分组均值，它本身不输出“该行值减去均值”的结果。所谓“偏离均值”，本质是：当前行的值 − 整体（或分组）均值。必须用窗口函数组合实现，不能只靠一个 AVG() OVER()。

正确写法：先算均值，再做减法

以按产品类别（category）计算各产品售价（price）偏离本类均值为例：

SELECT
  product_name,
  category,
  price,
  price - AVG(price) OVER (PARTITION BY category) AS deviation_from_category_avg
FROM products;

关键点：

AVG(price) OVER (PARTITION BY category) 先为每行算出其所属类别的平均价
用当前行的 price 减去这个窗口均值，得到偏离量
若想算全局偏离（不按类），去掉 PARTITION BY 即可：AVG(price) OVER ()
注意：AVG() 会自动忽略 NULL，但减法中只要任一操作数为 NULL，结果就是 NULL

常见错误：误用 ORDER BY 导致逻辑错乱

如果在 AVG() OVER() 里加了 ORDER BY（比如 OVER (PARTITION BY category ORDER BY sale_date)），得到的是“截至当前行的累计均值”，不是整个分组的均值 —— 这会导致每个 deviation_from_category_avg 都不同，且无法反映真实偏离。

除非你明确要计算“时间序列上的滚动偏离”，否则 不要加 ORDER BY。验证方法：检查同一 category 下多行的 AVG(price) OVER (...) 值是否完全一致；不一致就说明被 ORDER BY 干扰了。