Oracle：如何识别最新日期记录中的新增与更新条目

你的思路方向是对的，但窗口函数的分区逻辑没抓准核心——我们需要的是按每个id_number分区，对比它的最新记录和历史记录的差异，而不是按id_number, category, type分区（这样会把属性相同的记录归为一组，没法看出变化）。

下面给出两种可行的解决方案，先讲清楚逻辑，再上代码：

核心逻辑拆解 #

要区分new和updated，我们需要两个判断条件：

1. 新增条目：该id_number在最新日期之前没有任何历史记录（即总记录数只有1条，就是最新这条）
2. 更新条目：该id_number有历史记录，但最新记录的category或type和之前的记录不一致

方案一：使用CTE+左连接对比历史记录 #

这个方法逻辑直观，适合新手理解：

1. 先筛选出最新日期的所有记录
2. 左连接到该id_number的所有历史记录（日期早于最新日期）
3. 根据连接结果判断是新增还是更新

WITH all_records AS (
    SELECT id_number, category, type, date
    FROM schema.table
),
latest_records AS (
    -- 这里可以用动态获取最新日期，避免写死
    SELECT *
    FROM all_records
    WHERE date = (SELECT MAX(date) FROM all_records)
),
historical_data AS (
    -- 获取每个id_number的最新历史记录（取最近的一条来对比更准确）
    SELECT 
        id_number, category, type,
        ROW_NUMBER() OVER (PARTITION BY id_number ORDER BY date DESC) AS rn
    FROM all_records
    WHERE date < (SELECT MAX(date) FROM all_records)
)
SELECT 
    lr.id_number,
    lr.category,
    lr.type,
    lr.date,
    CASE
        WHEN hd.id_number IS NULL THEN 'new'
        WHEN hd.type != lr.type OR hd.category != lr.category THEN 'updated'
        ELSE NULL
    END AS new_or_updated
FROM latest_records lr
LEFT JOIN historical_data hd 
    ON lr.id_number = hd.id_number AND hd.rn = 1 -- 只关联每个id的最新历史记录
WHERE 
    CASE
        WHEN hd.id_number IS NULL THEN 'new'
        WHEN hd.type != lr.type OR hd.category != lr.category THEN 'updated'
        ELSE NULL
    END IS NOT NULL;

方案二：使用窗口函数一次性完成判断 #

这个方法更简洁，用窗口函数直接在原表上计算需要的判断条件：

1. 给每个id_number的记录按日期倒序排名，rn=1就是最新记录
2. 统计每个id_number的总记录数，判断是否是新增
3. 用LAG()函数获取该id上一条（历史最新）记录的属性，对比是否更新

WITH ranked_records AS (
    SELECT 
        id_number,
        category,
        type,
        date,
        -- 按日期倒序排名，第一条是最新记录
        ROW_NUMBER() OVER (PARTITION BY id_number ORDER BY date DESC) AS rn,
        -- 统计每个id的总记录数
        COUNT(*) OVER (PARTITION BY id_number) AS total_records,
        -- 获取上一条记录的type和category
        LAG(type) OVER (PARTITION BY id_number ORDER BY date DESC) AS prev_type,
        LAG(category) OVER (PARTITION BY id_number ORDER BY date DESC) AS prev_category
    FROM schema.table
)
SELECT 
    id_number,
    category,
    type,
    date,
    CASE
        WHEN total_records = 1 THEN 'new'
        WHEN type != prev_type OR category != prev_category THEN 'updated'
        ELSE NULL
    END AS new_or_updated
FROM ranked_records
WHERE rn = 1 -- 只保留最新日期的记录
AND (
    total_records = 1 
    OR (type != prev_type OR category != prev_category)
);

为什么你之前的方法没生效？ #

你之前用PARTITION BY id_number, category, type，会把同一个id下属性完全相同的记录分到一个分区，这样row_number()只会给这些相同属性的记录排号，根本没法对比历史和最新记录的差异。我们需要的是按id_number单独分区，这样才能看到这个id下所有记录的时间线变化。

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