Oracle中FIRST_VALUE嵌套CASE WHEN返回NULL问题求助

问题场景 #

查询最晚逾期日期时，将CASE WHEN直接嵌套在FIRST_VALUE函数内执行无语法错误，但返回值全为NULL；改用子查询先计算CASE WHEN的结果，再在外层调用FIRST_VALUE则能正常返回预期结果。

异常的嵌套写法 #

FIRST_VALUE(
    CASE
        WHEN inv.aging_period = 0
             AND is_tad_paid = 0
             AND is_mad_paid = 0
             AND inv.min_amount_due > 0 THEN
            inv.due_date
        ELSE
            NULL
    END
)
OVER(PARTITION BY inv.account_id
     ORDER BY inv.DUE_DATE DESC NULLS LAST
) AS latest_overdue_date,

正常的子查询写法 #

select sub.*,  
       first_value(ALL_OVER_DUE_DAY) over (partition by account_id order by ALL_OVER_DUE_DAY desc nulls last) as latest_over_due2
from (
    select 
        CASE
            WHEN inv.aging_period = 0
                 AND is_tad_paid = 0
                 AND is_mad_paid = 0
                 AND inv.min_amount_due > 0 THEN
                inv.due_date
            ELSE
                NULL
        END AS ALL_OVER_DUE_DAY 
    from t1 
) SUB

核心原因 #

两种写法的差异在于窗口排序逻辑与目标字段的匹配度：

• 嵌套写法中，窗口排序用的是原始的inv.due_date，但CASE WHEN生成的结果只有符合条件的行才返回非NULL值。如果按原始due_date排序后，排在分区最前面的行刚好是CASE WHEN返回NULL的行，FIRST_VALUE自然取到的就是NULL。
• 子查询写法中，排序字段是CASE WHEN生成的ALL_OVER_DUE_DAY，结合NULLS LAST规则，非NULL的有效逾期日期会被优先排在前面，FIRST_VALUE就能正确取到最晚的非NULL值。

修复方案 #

调整嵌套写法的窗口排序规则，让排序字段与FIRST_VALUE内的CASE WHEN逻辑保持一致：

FIRST_VALUE(
    CASE
        WHEN inv.aging_period = 0
             AND is_tad_paid = 0
             AND is_mad_paid = 0
             AND inv.min_amount_due > 0 THEN
            inv.due_date
        ELSE
            NULL
    END
)
OVER(
    PARTITION BY inv.account_id
    ORDER BY 
        CASE
            WHEN inv.aging_period = 0
                 AND is_tad_paid = 0
                 AND is_mad_paid = 0
                 AND inv.min_amount_due > 0 THEN
                inv.due_date
            ELSE
                NULL
        END DESC NULLS LAST
) AS latest_overdue_date

如果数据库支持在窗口ORDER BY中引用同层级的别名，也可以用更简洁的方式：

CASE
    WHEN inv.aging_period = 0
         AND is_tad_paid = 0
         AND is_mad_paid = 0
         AND inv.min_amount_due > 0 THEN
        inv.due_date
    ELSE
        NULL
END AS overdue_date,
FIRST_VALUE(overdue_date)
OVER(
    PARTITION BY inv.account_id
    ORDER BY overdue_date DESC NULLS LAST
) AS latest_overdue_date

关键总结 #

窗口函数的排序逻辑必须和要提取的目标字段逻辑对齐，否则会出现排序优先级与有效数据不匹配的情况。子查询方式本质是先预计算出有效数据集合，再基于该集合排序，从根源上避免了逻辑错位。

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