Oracle SQL实现历史表按val_id保留最新2条记录的删插操作

核心思路 #

先将符合条件的记录导入tbl_b，再通过窗口函数筛选出每个val_id需要保留的最新2条记录，删除其余冗余数据。这种方式能确保导入后整体数据符合要求，避免分步操作的逻辑漏洞。

具体操作步骤 #

1. 导入tbl_a中flag=1的记录 #

先把tbl_a里符合条件的记录插入到tbl_b，利用tbl_b的按月分区特性指定分区，能大幅提升插入效率：

INSERT INTO tbl_b (val_id, reg_date)
SELECT val_id, reg_date
FROM tbl_a
WHERE flag = 1;

2. 清理tbl_b中每个val_id的冗余记录 #

使用窗口函数ROW_NUMBER()按val_id分组、reg_date倒序排序，标记出每个组内排名超过2的记录并删除。为了适配1.5亿级的大数据量，建议加上分区过滤（比如只处理近几个月的分区），避免全表扫描：

DELETE FROM tbl_b
WHERE (val_id, reg_date) IN (
    SELECT val_id, reg_date
    FROM (
        SELECT 
            val_id, 
            reg_date,
            ROW_NUMBER() OVER (PARTITION BY val_id ORDER BY reg_date DESC) AS rn
        FROM tbl_b
        -- 可选：限定分区范围，比如只处理最近3个月的分区，降低查询压力
        WHERE reg_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)
    ) t
    WHERE rn > 2
);

大数据量场景优化建议 #

• 分区裁剪优先：每次清理仅针对有新数据导入的分区（比如当月、上月），无需扫描全表1.5亿数据。
• 临时表中转：若tbl_a待导入数据量较大，可先将flag=1的记录插入临时表，再从临时表导入tbl_b，减少原表锁的影响。
• 分批删除：如果单次删除数据量过大导致锁表，可按val_id范围拆分任务，分批执行删除操作。

替代方案：先清旧冗余再导入新数据 #

如果担心导入后删除影响性能，也可以分三步操作，聚焦待更新的val_id范围：

-- 第一步：清理tbl_b中与待导入val_id重复的冗余记录（确保每个val_id最多2条）
DELETE FROM tbl_b
WHERE (val_id, reg_date) IN (
    SELECT val_id, reg_date
    FROM (
        SELECT 
            val_id, 
            reg_date,
            ROW_NUMBER() OVER (PARTITION BY val_id ORDER BY reg_date DESC) AS rn
        FROM tbl_b
        WHERE val_id IN (SELECT DISTINCT val_id FROM tbl_a WHERE flag = 1)
    ) t
    WHERE rn > 2
);

-- 第二步：导入新记录
INSERT INTO tbl_b (val_id, reg_date)
SELECT val_id, reg_date
FROM tbl_a
WHERE flag = 1;

-- 第三步：清理导入后超过2条的记录
DELETE FROM tbl_b
WHERE (val_id, reg_date) IN (
    SELECT val_id, reg_date
    FROM (
        SELECT 
            val_id, 
            reg_date,
            ROW_NUMBER() OVER (PARTITION BY val_id ORDER BY reg_date DESC) AS rn
        FROM tbl_b
        WHERE val_id IN (SELECT DISTINCT val_id FROM tbl_a WHERE flag = 1)
    ) t
    WHERE rn > 2
);

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