如何在SQL表中添加计算非空列平均值的新列？

由于不同数据库的生成列/计算列语法存在差异，以下是主流数据库对应的ALTER TABLE语句：

MySQL #

ALTER TABLE your_table_name
ADD COLUMN Avg DECIMAL(10,2) GENERATED ALWAYS AS (
    CASE 
        WHEN (IF(C1 IS NOT NULL, 1, 0) + IF(C2 IS NOT NULL, 1, 0) + IF(C3 IS NOT NULL, 1, 0)) = 0 THEN NULL
        ELSE (COALESCE(C1, 0) + COALESCE(C2, 0) + COALESCE(C3, 0)) / 
             (IF(C1 IS NOT NULL, 1, 0) + IF(C2 IS NOT NULL, 1, 0) + IF(C3 IS NOT NULL, 1, 0))
    END
) STORED;

PostgreSQL #

ALTER TABLE your_table_name
ADD COLUMN Avg NUMERIC(10,2) GENERATED ALWAYS AS (
    CASE 
        WHEN (C1 IS NOT NULL)::INT + (C2 IS NOT NULL)::INT + (C3 IS NOT NULL)::INT = 0 THEN NULL
        ELSE (COALESCE(C1, 0) + COALESCE(C2, 0) + COALESCE(C3, 0)) / 
             ((C1 IS NOT NULL)::INT + (C2 IS NOT NULL)::INT + (C3 IS NOT NULL)::INT)::NUMERIC
    END
) STORED;

SQL Server #

ALTER TABLE your_table_name
ADD Avg AS 
    CASE 
        WHEN (CASE WHEN C1 IS NOT NULL THEN 1 ELSE 0 END + 
              CASE WHEN C2 IS NOT NULL THEN 1 ELSE 0 END + 
              CASE WHEN C3 IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN NULL
        ELSE (ISNULL(C1, 0) + ISNULL(C2, 0) + ISNULL(C3, 0)) / 
             (CASE WHEN C1 IS NOT NULL THEN 1 ELSE 0 END + 
              CASE WHEN C2 IS NOT NULL THEN 1 ELSE 0 END + 
              CASE WHEN C3 IS NOT NULL THEN 1 ELSE 0 END) * 1.0
    END;

说明 #

• 将your_table_name替换为你的实际表名
• DECIMAL(10,2)/NUMERIC(10,2)可根据需求调整精度，比如需要更多小数位可改为DECIMAL(10,4)
• 语句处理了三列全为NULL的情况，此时Avg设为NULL，避免出现除以0的错误

内容的提问来源于stack exchange，提问作者The Moody Prince