GROUP BY分组处理全Null值：聚合计算或替换指定值的实现疑问

表结构与需求 #

现有表结构如下：

id val
1 ...
.
.
2 ...
.
.
3 null
3 null
3 null
4 ...
.
.

规则：每个id对应多条记录，同一id的val要么全为整数，要么全为null。
需求：按id分组对val执行聚合（如AVG），若该id的val全为null则返回5。

尝试的语句及问题 #

尝试执行语句#1：

SELECT id, (CASE SUM(val) WHEN null THEN 5 ELSE AVG(val) END) AS ac FROM tt GROUP BY id

执行结果：

即使是id=3的分组，也会执行ELSE分支

单独查询SELECT SUM(val) FROM tt WHERE id = 3确实返回null，但主语句中的判断不生效。尝试改用WHEN IS NULL时出现语法错误。

疑问 #

1. 如何正确修正CASE语句的判断逻辑？
2. 有没有比使用SUM或MAX更标准的方式，判断分组内的val是否全为null？

解决方案 #

1. 修正CASE语句的判断逻辑 #

SQL中不能直接用WHEN null判断null值，必须用IS NULL语法。正确的写法如下：

SELECT id, 
       CASE 
           WHEN SUM(val) IS NULL THEN 5 
           ELSE AVG(val) 
       END AS ac 
FROM tt 
GROUP BY id

另一种更直观的方式是用COUNT(val)：因为COUNT(val)只会统计非null的记录数，当分组内全为null时，结果为0，判断逻辑更清晰：

SELECT id, 
       CASE 
           WHEN COUNT(val) = 0 THEN 5 
           ELSE AVG(val) 
       END AS ac 
FROM tt 
GROUP BY id

2. 更标准的全Null判断方式 #

• 通用方案：COUNT(val)
  COUNT(val)是兼容性最好的方式，所有主流数据库都支持。它直接统计分组内非null的val数量，若结果为0则说明全是null，逻辑简单易懂，不受val的数值类型影响。

• 数据库特定方案：EVERY函数（部分数据库支持）
  比如PostgreSQL支持EVERY(val IS NULL)，可以直接判断分组内所有val是否都是null：

  SELECT id, 
         CASE 
             WHEN EVERY(val IS NULL) THEN 5 
             ELSE AVG(val) 
         END AS ac 
  FROM tt 
  GROUP BY id
  

  注意：不同数据库的类似函数有差异，比如MySQL可以用GROUP_CONCAT(val) IS NULL替代，但兼容性不如COUNT方案。

内容的提问来源于stack exchange，提问作者mr.loop