Oracle SQL计算两日期平均值遇NULL值异常问题求助

首先得明确问题的根源：Oracle的AVG()函数（包括窗口聚合版本）会自动忽略NULL值。所以即使你的分区里存在appt.CONTACT_DATE为NULL的行，只要有其他非NULL的行，AVG()还是会基于那些有效数据计算出平均值，这就是为什么你预期NULL但仍得到结果的原因。

接下来根据你的实际需求，分两种场景给出解决方案：

场景1：当前行的CONTACT_DATE为NULL时，该行的平均值字段返回NULL #

如果你希望只要单条记录的CONTACT_DATE是NULL，这条记录对应的平均值就显示NULL（其他有值的行仍显示分区平均值），可以用CASE语句判断当前行的NULL状态：

CASE 
    WHEN appt.CONTACT_DATE IS NULL THEN NULL
    ELSE TRUNC ( 
            AVG ( 
                -- 注意：如果CONTACT_DATE和ENTRY_DATE本身是DATE类型，不需要TO_DATE转换！
                TO_DATE(appt.CONTACT_DATE, 'DD-MM-YYYY') - TO_DATE(REF.ENTRY_DATE, 'DD-MM-YYYY')
            ) OVER (PARTITION BY ser2.PROV_NAME), 
            1
         ) 
END AS "AVG BY PROVID..."

场景2：分区内只要有任何一行CONTACT_DATE为NULL，整个分区的平均值都返回NULL #

如果你的需求是更严格的：只要某个分区里存在至少一条CONTACT_DATE为NULL的记录，整个分区的平均值字段都显示NULL，那么需要先检查分区内的NULL存在情况：

CASE 
    -- 统计分区内CONTACT_DATE为NULL的行数，大于0则返回NULL
    WHEN COUNT(CASE WHEN appt.CONTACT_DATE IS NULL THEN 1 END) OVER (PARTITION BY ser2.PROV_NAME) > 0 THEN NULL
    ELSE TRUNC ( 
            AVG ( 
                TO_DATE(appt.CONTACT_DATE, 'DD-MM-YYYY') - TO_DATE(REF.ENTRY_DATE, 'DD-MM-YYYY')
            ) OVER (PARTITION BY ser2.PROV_NAME), 
            1
         ) 
END AS "AVG BY PROVID..."

额外提示：避免不必要的日期转换 #

如果appt.CONTACT_DATE和REF.ENTRY_DATE本身已经是Oracle的DATE类型字段，完全不需要用TO_DATE()转换——直接相减就能得到两个日期的天数差（Oracle中DATE类型相减的结果是数值型的天数）。强行转换反而可能引发格式不匹配的错误，简化后的代码更高效：

-- 以场景1为例，简化日期转换后的版本
CASE 
    WHEN appt.CONTACT_DATE IS NULL THEN NULL
    ELSE TRUNC ( 
            AVG (appt.CONTACT_DATE - REF.ENTRY_DATE) 
            OVER (PARTITION BY ser2.PROV_NAME), 
            1
         ) 
END AS "AVG BY PROVID..."

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