Oracle SQL：如何按月份标记姓名+商品类型组合的首次出现

需求说明 #

• 有一张每日加载文件数据的Oracle表，包含采购姓名（NAME）、商品类型（ITEM）、月份字段（原列名为DATE，格式如012024）
• 需要更新表中已有的FIRST列：每个NAME+ITEM+MONTH组合的首次出现记录标记为Y，其余标记为N

当前表数据示例 #

期望更新后的数据示例 #

用户尝试的SQL #

SELECT ROWNUM, NAME||ITEM||MONTH AS "RequestID"
FROM alphabeta_table
ORDER BY NAME||ITEM||MONTH

解决方案 #

方法1：使用MERGE语句（推荐，性能更优） #

MERGE INTO alphabeta_table t
USING (
    SELECT 
        FILE, NAME, ITEM, MONTH,
        -- 按姓名、商品、月份分组，组内按文件名排序（假设文件名顺序对应加载顺序）
        ROW_NUMBER() OVER (PARTITION BY NAME, ITEM, MONTH ORDER BY FILE) AS rn
    FROM alphabeta_table
) s
ON (t.FILE = s.FILE AND t.NAME = s.NAME AND t.ITEM = s.ITEM AND t.MONTH = s.MONTH)
WHEN MATCHED THEN
    UPDATE SET t.FIRST = CASE WHEN s.rn = 1 THEN 'Y' ELSE 'N' END;

方法2：关联UPDATE语句 #

UPDATE alphabeta_table t
SET FIRST = (
    SELECT CASE WHEN rn = 1 THEN 'Y' ELSE 'N' END
    FROM (
        SELECT 
            FILE, NAME, ITEM, MONTH,
            ROW_NUMBER() OVER (PARTITION BY NAME, ITEM, MONTH ORDER BY FILE) AS rn
        FROM alphabeta_table
    ) s
    WHERE t.FILE = s.FILE AND t.NAME = s.NAME AND t.ITEM = s.ITEM AND t.MONTH = s.MONTH
);

关键说明 #

• PARTITION BY NAME, ITEM, MONTH：确保我们只在相同姓名、相同商品、相同月份的范围内判断首次出现
• ORDER BY FILE：这里假设文件名的顺序就是数据加载的先后顺序（比如Alpha1早于Beta2），如果表中有专门的加载时间戳字段，将FILE替换为该时间字段排序会更准确

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