加权平均值的sql标准写法是sum(value * weight) / sum(weight)，需用nullif防止除零，coalesce兜底，并强制浮点运算；权重为动态时须确保分子分母case逻辑一致。
加权平均值的SQL标准写法（兼容MySQL/PostgreSQL/SQL Server）
直接用 SUM(value * weight) / SUM(weight) 是最稳妥的方式，不需要依赖特定数据库的聚合函数。几乎所有主流SQL引擎都支持这个表达式，且能正确处理 NULL 值（只要参与计算的 value 或 weight 为 NULL，整行会被自动排除）。
常见错误是写成 AVG(value * weight)——这算的是“加权值的算术平均”，不是加权平均；或者漏掉分母的 SUM(weight)，误用 COUNT(*) 导致权重归一化失效。
- 确保
weight列非负，负权重会扭曲结果（部分数据库不报错但逻辑错误） - 若权重是百分比（如 0.2、0.5），可直接用；若是整数（如投票数、样本量），也直接用，无需额外除以100
- 在 PostgreSQL 中可封装为
CREATE AGGREGATE weighted_avg(...)...，但跨库项目建议坚持手写表达式
处理零权重或全NULL数据时的防错写法
当某组数据中所有 weight 都为 0 或 NULL，SUM(weight) 返回 0，除零会报错（如 PostgreSQL 报 division by zero，MySQL 默认返回 NULL 但行为不可靠）。必须显式拦截。
推荐用 NULLIF(SUM(weight), 0) 包裹分母，并配合 COALESCE 提供默认值：
SELECT COALESCE(SUM(score * votes) * 1.0 / NULLIF(SUM(votes), 0), 0) AS weighted_avg FROM reviews GROUP BY product_id;
-
* 1.0强制转为浮点，避免整数除法截断（尤其在 SQL Server 和旧版 MySQL 中） -
NULLIF(..., 0)在分母为 0 时返回NULL，使整个除法结果为NULL，再由COALESCE统一兜底 - 不要用
CASE WHEN SUM(votes) = 0 THEN 0 ELSE ... END——逻辑等价但更冗长，且易漏掉NULL分支
与窗口函数结合计算动态权重（如时间衰减）
真实场景中权重常非静态列，比如按评论日期加权：“3个月内评分权重为1，3–6个月为0.7，6个月以上为0.3”。这时需在 SUM() 内联用 CASE 构造运行时权重。
示例（计算每个产品的带时间衰减的加权平均分）：
SELECT
product_id,
COALESCE(
SUM(score *
CASE
WHEN review_date >= CURRENT_DATE - INTERVAL '3 months' THEN 1.0
WHEN review_date >= CURRENT_DATE - INTERVAL '6 months' THEN 0.7
ELSE 0.3
END
) / NULLIF(SUM(
CASE
WHEN review_date >= CURRENT_DATE - INTERVAL '3 months' THEN 1.0
WHEN review_date >= CURRENT_DATE - INTERVAL '6 months' THEN 0.7
ELSE 0.3
END
), 0), 0) AS time_weighted_avg
FROM reviews
GROUP BY product_id;
- 两个
CASE必须完全一致，否则分子分母权重不匹配——这是最容易抄错的地方 - 日期函数语法因数据库而异：
INTERVAL '3 months'是 PostgreSQL，MySQL 用DATE_SUB(NOW(), INTERVAL 3 MONTH)，SQL Server 用DATEADD(MONTH, -3, GETDATE()) - 如果衰减逻辑复杂，建议先用子查询或 CTE 算出每行的
dynamic_weight，再外层聚合，可读性更高
加权平均本身不难，难的是权重来源是否可信、分母是否真能代表总权重规模，以及当业务规则变化时，SQL里硬编码的 CASE 权重是否同步更新。别只盯着公式对不对，先确认那列 votes 或那个时间窗口定义，有没有被上游ETL悄悄改过口径。
