SQL中CASE函数结合avg聚合返回NULL的原因咨询

示例数据 #

两个查询的对比 #

第一个查询 #

SELECT
    ListID,
    CASE 
        WHEN date>'2022-07-22' 
            THEN avg(value)
        ELSE NULL
    END AS 'Value_Before_Rate_Change'
FROM 
    TB01 where date like '2022%' and ListID=1;

第一个查询输出

第二个查询 #

select avg(value)  
from TB01 
where date like '2022%' 
  and ListID=1 
  and date>'2022-07-22';

第二个查询输出

补充：添加GROUP BY后的查询 #

SELECT
        ListID,
        CASE 
            WHEN date>'2022-07-22' 
                THEN avg(value)
            ELSE NULL
        END AS 'Value_Before_Rate_Change'
    FROM 
        TB01 where date like '2022%' and ListID=1 group by ListID;

结果依然返回NULL。

问题原因 #

你的CASE语句搞反了聚合函数和条件判断的执行顺序：

• 聚合函数AVG()是对整个分组的数据计算平均值，执行优先级远高于CASE里的行级判断。
• 当你写WHEN date>'2022-07-22' THEN avg(value)时，数据库会先算出整个分组的AVG(value)，再去检查date>'2022-07-22'这个行级条件——但分组后无法确定用哪一行的date来做判断，最终只能返回NULL。

简单说：你想先筛选符合日期条件的行再算平均值，但写法却是先算所有行的平均值，再用单一行的日期决定要不要返回这个值，逻辑顺序完全错了。

正确写法 #

要实现类似第二个查询的效果，得把条件放到聚合函数内部，先对符合条件的value保留，不符合的用NULL代替，再让AVG()忽略NULL值计算平均值：

SELECT
    ListID,
    AVG(CASE WHEN date>'2022-07-22' THEN value ELSE NULL END) AS 'Value_Before_Rate_Change'
FROM 
    TB01 where date like '2022%' and ListID=1
GROUP BY ListID;

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