Oracle UNION ALL查询返回NULL转0，CASE语句失效如何处理

核心原因是Oracle的AVG()聚合函数在没有匹配数据时会返回NULL，只需用Oracle内置的NVL()函数对聚合结果做判空处理即可，写法比CASE语句更简洁。

直接修改原UNION ALL语句 #

在每个SELECT子句的结果外层包裹NVL()，设置NULL时返回0：

SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 06:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 06:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 07:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 07:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 08:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 08:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 09:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 09:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 10:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 10:05:00', 'dd/mm/yyyy HH24:MI:SS')
 UNION ALL
 SELECT NVL(ROUND(AVG(BATTERYVOLTAGE),2), 0) AVGVOLTAGE FROM MYTABLE WHERE trailerid = 3000 and DATADATE >= TO_DATE('25/08/2021 11:00:00', 'dd/mm/yyyy HH24:MI:SS') AND DATADATE <= TO_DATE('25/08/2021 11:05:00', 'dd/mm/yyyy HH24:MI:SS');

简化写法（可选优化） #

如果后续需要调整时间区间，可通过生成时间序列的方式避免重复书写SELECT语句：

SELECT NVL(ROUND(AVG(m.BATTERYVOLTAGE),2), 0) AVGVOLTAGE
FROM (
    -- 生成6点到11点共6个整点的5分钟统计区间
    SELECT TO_DATE('25/08/2021 ' || TO_CHAR(level + 5, 'fm00') || ':00:00', 'dd/mm/yyyy HH24:MI:SS') start_time,
           TO_DATE('25/08/2021 ' || TO_CHAR(level + 5, 'fm00') || ':05:00', 'dd/mm/yyyy HH24:MI:SS') end_time
    FROM dual
    CONNECT BY level <= 6
) t
LEFT JOIN MYTABLE m 
    ON m.trailerid = 3000
    AND m.DATADATE BETWEEN t.start_time AND t.end_time
GROUP BY t.start_time
ORDER BY t.start_time;

CASE语句等效实现 #

如果需要用CASE语句实现，写法如下，效果和NVL完全一致：

CASE WHEN ROUND(AVG(BATTERYVOLTAGE),2) IS NULL THEN 0 ELSE ROUND(AVG(BATTERYVOLTAGE),2) END

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