NULL值影响CASE逻辑：SQL左连接AR字段空值导致站点分级错误

问题根因 #

• 你的SQL中SELECT层级定义的ar别名无法被同层级后续计算station_ref的CASE语句识别，CASE语句中用到的ar依然是LEFT JOIN后原始的、包含NULL值的raw_station_extended_attribute.ar字段，不是你转成'0'后的值。
• 当原始ar为NULL时，ar <> 'YES'的判断结果为UNKNOWN，所有带该条件的分支都不会触发，ar = 'YES'的条件也不满足，最终直接走到ELSE分支返回'ds small'，和volume_ref的数值无关。

修复方案 #

直接用COALESCE函数统一处理NULL的ar值，替换原来的CASE判断，且后续计算station_ref时也使用处理后的ar值即可，修正后的SQL如下：

SELECT
    station,
    COALESCE(ar, '0') AS ar,
    del_date,
    sum(volume) / 7 AS volume_ref,
    DATE_PART("week", del_date) AS week_num,
    DATE_PART("year", del_date) AS year,
    CASE 
        WHEN volume_ref between 0 AND 20000 AND COALESCE(ar, '0') <> 'YES' THEN 'ds x-small'
        WHEN volume_ref between 20000 AND 36000 AND COALESCE(ar, '0') <> 'YES' THEN 'ds small'
        WHEN volume_ref between 36000 AND 42000 AND COALESCE(ar, '0') <> 'YES' THEN 'ds standard'
        WHEN volume_ref between 42000 AND 72000 AND COALESCE(ar, '0') <> 'YES' THEN 'ds large'
        WHEN volume_ref > 72000 THEN 'ds x-large'
        WHEN COALESCE(ar, '0') = 'YES' THEN 'ds x-large'
        ELSE 'ds small'
    END AS station_ref
FROM
    prophecy_na.na_topology_lrp
    LEFT JOIN wbr_global.raw_station_extended_attribute 
        ON prophecy_na.na_topology_lrp.station = wbr_global.raw_station_extended_attribute.ds 
WHERE
    week_num IN (16, 20, 40, 48)
GROUP BY
    na_topology_lrp.station,
    raw_station_extended_attribute.ar,
    na_topology_lrp.del_date;

如果觉得重复写COALESCE(ar, '0')太冗余，也可以先用子查询/CTE把ar的空值处理完，再在外层计算station_ref，逻辑更清晰。

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