如何在SQL中计算分组数据的加权平均数

<p>SQL中加权平均的通用写法是SUM(value * weight) / SUM(weight)，需过滤NULL和零权重以避免错误，各数据库结果一致但类型处理有差异。</p>

SQL 中加权平均的通用写法是 SUM(value * weight) / SUM(weight)

这不是某个数据库的特有函数，而是数学定义的直接翻译。所有主流 SQL 引擎（PostgreSQL、MySQL 8.0+、SQL Server、Oracle、SQLite 3.35+）都支持这种写法，且结果一致。关键不是“有没有函数”，而是“权重是否为零或 NULL”——这会直接导致除零错误或结果失真。

常见错误现象：NULL 权重被 SUM() 忽略，但若整组权重全为 NULL，SUM(weight) 返回 NULL，整个表达式变成 NULL / NULL → 结果仍是 NULL；更危险的是权重含 0：分母为 0 时 PostgreSQL 报错 division by zero，MySQL 默认返回 NULL（开启严格模式则报错）。

• 务必用 WHERE weight IS NOT NULL AND weight > 0 过滤掉无效权重行（在 GROUP BY 前）
• 若业务允许权重为 0，改用 NULLIF(SUM(weight), 0) 避免除零，例如：SUM(value * weight) / NULLIF(SUM(weight), 0)
• 注意 value 本身为 NULL：value * weight 得 NULL，会被 SUM() 忽略——这通常符合预期，但需确认业务逻辑是否要求跳过整条记录

PostgreSQL 和 SQL Server 支持 AVG() OVER ()，但不支持加权

别被窗口函数名误导：AVG() 窗口函数只做简单平均，没有权重参数。试图写成 AVG(value * weight) OVER (PARTITION BY group_col) 是错的——它算的是“加权值的平均”，不是“加权平均数”。比如两行数据：(value=10, weight=1) 和 (value=20, weight=3)，正确加权平均是 (10×1 + 20×3)/(1+3) = 17.5；而 AVG(value * weight) 得到的是 (10 + 60)/2 = 35，完全错误。

实操建议：

• 放弃找“内置加权平均函数”，老实用 SUM(value * weight) / SUM(weight)
• 若需在窗口中计算分组加权平均，仍得配合 PARTITION BY 写完整表达式：SUM(value * weight) OVER (PARTITION BY group_col) / SUM(weight) OVER (PARTITION BY group_col)
• SQL Server 2022+ 的 APPROX_PERCENTILE_CONT 等新函数也不涉及加权，勿混淆

MySQL 用户注意隐式类型转换陷阱

MySQL 在 SUM() 中对混合类型（如 INT 权重 + DECIMAL 值）可能截断小数位，尤其当权重是 TINYINT 或 SMALLINT 时。SUM(value * weight) 可能先按整型运算再转浮点，导致精度丢失。例如 value = 1.99，weight = 100，理论上应得 199.00，但若列定义为 DECIMAL(3,2) × TINYINT，MySQL 可能返回 199（丢掉小数）。

解决方法：

• 显式转换权重：SUM(CAST(value AS DECIMAL(10,4)) * CAST(weight AS DECIMAL(10,4))) / SUM(CAST(weight AS DECIMAL(10,4)))
• 或提前在 SELECT 中统一类型：value * 1.0 AS weighted_value，再 SUM(weighted_value)
• 检查执行计划，确认 SUM() 的输出类型是否与预期一致（用 SHOW COLUMNS 或 SELECT @@sql_mode 查 strict mode 是否启用）

加权平均本身逻辑简单，但权重数据的质量和数据库对 NULL/0/类型的处理差异，才是实际跑不通的主因。别急着查函数文档，先看你的 weight 列里有没有 NULL、0、负数，以及它的定义类型是否足够容纳乘积结果。