如何在SQL中计算存在NULL值的行内多列数值平均值？

针对按标识符、日期分组，计算每行内多列数值的平均值（自动忽略NULL），以下是不同SQL数据库的实现方案：

通用手动计算法（所有数据库适用） #

核心逻辑：计算非NULL值的总和，除以非NULL值的数量，同时避免全NULL时除以0的错误。

SELECT
  identifier,
  date_col,
  -- 计算非NULL值的总和
  (COALESCE(col1, 0) + COALESCE(col2, 0) + COALESCE(col3, 0) + COALESCE(col4, 0))
  -- 除以非NULL值的数量，全NULL时返回NULL
  / NULLIF(
    (col1 IS NOT NULL) + (col2 IS NOT NULL) + (col3 IS NOT NULL) + (col4 IS NOT NULL),
    0
  ) AS row_average
FROM your_table;

• COALESCE(col, 0)：将NULL值转为0，不影响总和计算
• (col IS NOT NULL)：返回1（非NULL）或0（NULL），累加得到非NULL值的数量
• NULLIF(..., 0)：当所有列都是NULL时，分母为0，此时返回NULL，避免报错

MySQL/MariaDB 简洁法 #

利用UNNEST和VALUES将列转为行，直接用AVG()函数自动忽略NULL：

SELECT 
  identifier,
  date_col,
  AVG(val) AS row_average
FROM your_table,
UNNEST(VALUES (col1), (col2), (col3), (col4)) AS vals(val)
GROUP BY identifier, date_col;

PostgreSQL 简洁法 #

通过array和unnest将列转为行，再用AVG()计算：

SELECT
  identifier,
  date_col,
  AVG(unnest(array[col1, col2, col3, col4])) AS row_average
FROM your_table
GROUP BY identifier, date_col;

SQL Server 简洁法 #

使用CROSS APPLY结合VALUES构造行集，再求平均：

SELECT
  identifier,
  date_col,
  AVG(val) AS row_average
FROM your_table
CROSS APPLY (VALUES (col1), (col2), (col3), (col4)) AS vals(val)
GROUP BY identifier, date_col;

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