Oracle SQL中Row_number与Case When交互异常问题排查

这不是SQL语言的bug，是你的用法错误。

核心原因 #

合并后的数据集里，同一Product+Range分区下存在多条记录（两个系统都有该组合的价格数据），row_number()按Valid_from倒序分配排名时，出现了以下两种导致异常的情况：

• 排序逻辑不严谨：当两个系统的同一Product+Range下有相同的Valid_from值时，row_number()缺少额外的唯一排序依据，导致排名分配不稳定——有时你预期的最新价格行被排为rwn=2，而聚合时只取rwn=1的价格作为新价格，若该rwn=1的行在聚合时未被正确取值，就会出现新价格为空。
• 聚合逻辑错误：比如分组时遗漏Product或Range字段、CASE WHEN逻辑写反（把旧价格当成新价格）、使用错误的聚合函数（如用SUM而非MAX），导致新价格字段取不到值。

修正方案 #

1. 优化排序逻辑，确保排名稳定 #

在row_number()的ORDER BY中，除了Valid_from DESC，增加额外的区分字段（比如系统来源标记、Valid_until或主键），保证分区内的排序唯一且符合预期：

WITH combined_data AS (
    -- 给每个系统的表添加来源标记
    SELECT Product, Price, Range, Valid_from, Valid_until, 'systemA' AS source
    FROM systemA_table
    UNION ALL
    SELECT Product, Price, Range, Valid_from, Valid_until, 'systemB' AS source
    FROM systemB_table
),
ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Product, Range 
            -- 增加额外排序字段，确保排名唯一稳定
            ORDER BY Valid_from DESC, Valid_until DESC, source
        ) AS rwn
    FROM combined_data
)

2. 修正聚合逻辑，正确提取新旧价格 #

按Product和Range分组，用MAX聚合函数提取对应排名的价格与有效期：

SELECT
    Product,
    Range,
    MAX(CASE WHEN rwn = 1 THEN Price END) AS latest_price,
    MAX(CASE WHEN rwn = 1 THEN Valid_from END) AS latest_valid_from,
    MAX(CASE WHEN rwn = 1 THEN Valid_until END) AS latest_valid_until,
    MAX(CASE WHEN rwn = 2 THEN Price END) AS previous_price,
    MAX(CASE WHEN rwn = 2 THEN Valid_from END) AS previous_valid_from,
    MAX(CASE WHEN rwn = 2 THEN Valid_until END) AS previous_valid_until
FROM ranked_data
GROUP BY Product, Range
ORDER BY Product, Range;

额外检查项 #

• 确认两个系统的Valid_from字段数据类型一致（均为日期/时间类型），避免字符串排序导致的顺序错误。
• 检查UNION ALL是否完整合并了所有数据，没有遗漏或错误过滤行。
• 若某个Product+Range只有一条记录，previous_price会显示为NULL，可根据需求用COALESCE(MAX(CASE WHEN rwn = 2 THEN Price END), 0)替换为默认值。

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