如何基于多列平均值排序并忽略NULL值？附错误解法疑问

需求说明 #

需要对表数据按3列的平均值排序，要求计算平均值时忽略NULL列：即仅用非NULL列的总和除以非NULL列的数量（例如col1为NULL时，除数取2）。

原参考SQL（未处理NULL场景）：

select * from table order by ((col1+col2+col3)/3);

你尝试的SQL语句（无报错但未生效，始终除以3）：

select * from table order by (IFNULL(col1,0)+IFNULL(col2,0)+IFNULL(col3,0))/(IF(col1>0,1,0)+IF(col2>0,1,0)+IF(col3>0,1,0))

无效原因 #

问题出在除数的统计逻辑上：

• 你用IF(col>0,1,0)来计数有效列，但这个条件判断的是列值是否大于0，而非列是否为NULL。
• 当列是NULL时，col>0的结果为NULL，IF函数会返回ELSE部分的0；但如果列值是0、负数这类非NULL但不大于0的值时，同样会返回0，这会错误排除这些非NULL列。
• 如果你的表中所有非NULL列的值都大于0，那么IF(col>0,1,0)会始终返回1，除数就一直是3，这就是你看到的“始终除以3”的原因。

正确写法 #

要准确统计非NULL列的数量，直接判断列是否不为NULL即可——SQL中布尔表达式会被转换为1（true）或0（false）参与计算：

select * 
from table 
order by (IFNULL(col1, 0) + IFNULL(col2, 0) + IFNULL(col3, 0)) / 
         ((col1 IS NOT NULL) + (col2 IS NOT NULL) + (col3 IS NOT NULL));

也可以用COUNT()函数（单行中，COUNT(col)对非NULL列返回1，NULL列返回0）：

select * 
from table 
order by (IFNULL(col1, 0) + IFNULL(col2, 0) + IFNULL(col3, 0)) / 
         (COUNT(col1) + COUNT(col2) + COUNT(col3));

特殊情况处理 #

如果三列全为NULL，除数会变成0导致报错，可通过NULLIF避免：

select * 
from table 
order by (IFNULL(col1, 0) + IFNULL(col2, 0) + IFNULL(col3, 0)) / 
         NULLIF((col1 IS NOT NULL) + (col2 IS NOT NULL) + (col3 IS NOT NULL), 0);

此时全NULL的行排序结果为NULL，会被放在排序结果的末尾或开头（取决于数据库规则）。

内容的提问来源于stack exchange，提问作者S.ov