SQL实现添加排除NULL值的行平均值列

问题原因 #

你当前的语句存在两个核心问题：

1. NULL值的算术特性：SQL中任何数值与NULL进行算术运算，结果都会变成NULL，所以jan+feb+mar+apr+may只要其中一列是NULL，整个表达式结果就是NULL。
2. 聚合函数误用：AVG()是列级聚合函数，作用是对整个列的所有行计算平均值，而非对单一行内的多个列计算均值，你的写法完全不符合需求。

正确解决方案 #

要实现行内忽略NULL计算平均值，需要分两步：

1. 将每个NULL转换为0，确保总和计算有效；
2. 统计当前行中非NULL的列数，用总和除以这个数量得到真实平均值。

通用SQL实现（兼容MySQL、PostgreSQL、SQL Server等大多数数据库）：

SELECT 
    name,
    jan,
    feb,
    march AS mar,
    april AS apr,
    may,
    -- 计算有效数值的总和
    (COALESCE(jan, 0) + COALESCE(feb, 0) + COALESCE(march, 0) + COALESCE(april, 0) + COALESCE(may, 0)) /
    -- 统计非NULL的列数
    (
        CASE WHEN jan IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN feb IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN march IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN april IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN may IS NOT NULL THEN 1 ELSE 0 END
    ) AS avg
FROM t;

结果验证 #

• 对于stan：有效数值总和为3+7+3=13，非NULL列数为3，平均值13/3≈4.3
• 对于dawn：有效数值总和为2+3+9+2=16，非NULL列数为4，平均值16/4=4
  完全符合你预期的结果。

补充说明 #

• COALESCE(col, 0)是标准SQL函数，作用是如果列值为NULL则返回0，否则返回列值。部分数据库有专属替代函数（如MySQL的IFNULL、SQL Server的ISNULL），但COALESCE兼容性最好。
• 如果所有列都是NULL，分母会变成0导致报错，可以添加NULLIF处理避免除以0的情况：

SELECT 
    name,
    jan,
    feb,
    march AS mar,
    april AS apr,
    may,
    (COALESCE(jan, 0) + COALESCE(feb, 0) + COALESCE(march, 0) + COALESCE(april, 0) + COALESCE(may, 0)) /
    NULLIF(
        CASE WHEN jan IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN feb IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN march IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN april IS NOT NULL THEN 1 ELSE 0 END +
        CASE WHEN may IS NOT NULL THEN 1 ELSE 0 END,
        0
    ) AS avg
FROM t;

这样当所有列都是NULL时，avg会返回NULL而非报错。

内容的提问来源于stack exchange，提问作者Dustin