加权平均是各数据点乘以其权重后求和再除以权重总和,不能直接用AVG()因其仅计算等权平均;SQL中须用SUM(value*weight)/SUM(weight)手动实现,注意处理NULL、零权重及精度问题。

什么是加权平均,为什么不能直接用 AVG()
加权平均不是所有值等比例相加再除以个数,而是每个值乘以对应权重后求和,再除以权重总和。比如学生成绩中“期末占60%、平时占40%”,就不能用 AVG(score),否则会把三次平时分和一次期末分全当1份处理,彻底失真。
SQL标准函数里没有内置加权平均聚合函数,必须手动组合 SUM() 和算术表达式实现。
标准写法:用 SUM(value * weight) / SUM(weight)
这是最通用、兼容性最好的方式,适用于 MySQL、PostgreSQL、SQL Server、Oracle 等主流数据库(注意:SQLite 默认不支持浮点除法精度,需显式转 REAL)。
- 必须确保
weight字段不含NULL或负值,否则SUM(weight)可能为 0 或负,导致除零错误或逻辑错误 - 如果
value或weight有NULL,整行会被SUM()自动忽略——这通常是期望行为,但需确认业务是否允许丢弃缺失数据 - 若需保留小数精度,建议在分子或分母显式转换类型,例如 PostgreSQL 中写
SUM(value::numeric * weight) / NULLIF(SUM(weight), 0)
SELECT category, SUM(score * weight) / NULLIF(SUM(weight), 0) AS weighted_avg FROM exam_scores GROUP BY category;
遇到 division by zero 怎么办
错误信息 division by zero(PostgreSQL)或类似提示(如 SQL Server 的“试图除以零”),说明某组的 SUM(weight) 为 0。常见于权重列存在全 0 值、全 NULL,或被 WHERE 过滤后无有效行。
- 用
NULLIF(SUM(weight), 0)替代直接SUM(weight),让分母为 0 时返回NULL,避免报错 - 更稳妥的做法是提前过滤:在
WHERE中加上weight > 0(假设权重非负) - 若业务上允许权重为 0 表示“该记录不参与计算”,则无需报错,但需确保结果字段可为空,且下游应用能正确处理
NULL
MySQL 8.0+ 可用窗口函数做动态权重归一化
当权重需要按组内重新标准化(比如把每组的权重缩放到和为 1),单纯 SUM() / SUM() 不够用,得借助窗口函数。
-
SUM(weight) OVER (PARTITION BY category)可获取每组总权重,用于归一化单个权重 - 此时加权平均可写为:
SUM(score * weight / SUM(weight) OVER (PARTITION BY category)) - 注意:这种写法等价于标准写法,但多了一次窗口计算,性能略低;仅在需要中间归一化值(如单独查各权重占比)时才值得引入
真实项目里,95% 的场景用第一种 SUM(value*weight)/SUM(weight) 就够了,别为了炫技加窗口函数——容易让同事看不懂,也增加执行计划复杂度。

















