Oracle SQL按合同和字段获取日期最晚的修订记录

你需要按合同分组，获取每个合同Price和Quantity字段的最新修订值（日期最晚的OLD_VALUE和NEW_VALUE），原SQL存在分组逻辑和函数使用的问题，以下是修正方案：

原SQL的问题分析 #

• 分组粒度错误：原SQL的SELECT子句包含FIELD和DATE_OF_CHANGE，但GROUP BY仅写了CONTRACT，Oracle会隐式将这两个字段加入分组条件，导致每个合同按字段+日期拆分成多行，无法得到单合同一行的结果。
• KEEP函数逻辑错误：原写法中KEEP的排序依据是整个分组的DATE_OF_CHANGE，而非对应字段的日期，会错误取到合同内所有修订中最晚日期的记录，而非该字段自身的最新修订。

正确解法一：条件聚合+KEEP子句 #

直接按合同分组，针对Price和Quantity分别筛选并取对应字段的最新值：

SELECT 
    CONTRACT,
    -- 获取Price字段的最新NEW_VALUE
    MAX(CASE WHEN FIELD = 'PRICE' THEN NEW_VALUE END) 
        KEEP (DENSE_RANK LAST ORDER BY CASE WHEN FIELD = 'PRICE' THEN DATE_OF_CHANGE END) 
        AS LAST_NEW_PRICE,
    -- 获取Price字段的最新OLD_VALUE
    MAX(CASE WHEN FIELD = 'PRICE' THEN OLD_VALUE END) 
        KEEP (DENSE_RANK LAST ORDER BY CASE WHEN FIELD = 'PRICE' THEN DATE_OF_CHANGE END) 
        AS LAST_OLD_PRICE,
    -- 获取Quantity字段的最新NEW_VALUE
    MAX(CASE WHEN FIELD = 'QUANTITY' THEN NEW_VALUE END) 
        KEEP (DENSE_RANK LAST ORDER BY CASE WHEN FIELD = 'QUANTITY' THEN DATE_OF_CHANGE END) 
        AS LAST_NEW_QUANTITY,
    -- 获取Quantity字段的最新OLD_VALUE
    MAX(CASE WHEN FIELD = 'QUANTITY' THEN OLD_VALUE END) 
        KEEP (DENSE_RANK LAST ORDER BY CASE WHEN FIELD = 'QUANTITY' THEN DATE_OF_CHANGE END) 
        AS LAST_OLD_QUANTITY
FROM dim_amendments
GROUP BY CONTRACT;

逻辑说明 #

• 按CONTRACT分组后，用CASE筛选出对应字段的记录。
• KEEP (DENSE_RANK LAST ORDER BY ...)针对每个字段的DATE_OF_CHANGE取最晚的那条记录，再通过MAX聚合得到对应值。

正确解法二：窗口函数+行转列 #

先筛选每个合同每个字段的最新修订记录，再将多行转成一行：

WITH latest_amendments AS (
    SELECT 
        CONTRACT,
        FIELD,
        OLD_VALUE,
        NEW_VALUE,
        -- 按合同+字段分组，日期倒序排号，1为最新记录
        ROW_NUMBER() OVER (PARTITION BY CONTRACT, FIELD ORDER BY DATE_OF_CHANGE DESC) AS rn
    FROM dim_amendments
    WHERE FIELD IN ('PRICE', 'QUANTITY') -- 仅筛选目标字段
)
SELECT 
    CONTRACT,
    MAX(CASE WHEN FIELD = 'PRICE' THEN NEW_VALUE END) AS LAST_NEW_PRICE,
    MAX(CASE WHEN FIELD = 'PRICE' THEN OLD_VALUE END) AS LAST_OLD_PRICE,
    MAX(CASE WHEN FIELD = 'QUANTITY' THEN NEW_VALUE END) AS LAST_NEW_QUANTITY,
    MAX(CASE WHEN FIELD = 'QUANTITY' THEN OLD_VALUE END) AS LAST_OLD_QUANTITY
FROM latest_amendments
WHERE rn = 1 -- 仅保留每个字段的最新记录
GROUP BY CONTRACT;

逻辑说明 #

• CTElatest_amendments中，用ROW_NUMBER()给每个合同每个字段的修订记录按日期倒序编号，rn=1即为该字段的最新修订。
• 外层通过条件聚合，将Price和Quantity的字段值合并到同一行。

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