Oracle表按id_1、id_2组合去重保留最新update_date的实现方法

问题说明 #

现有Oracle表CUSTOMER_TEST，业务要求id_1与id_2的组合唯一，但现有数据存在同一组合多条记录的情况，需要每个组合仅保留update_date最新的一条数据。
你最初编写的GROUP BY查询存在语法错误：score字段既未加入GROUP BY子句，也未被聚合函数包裹，Oracle执行时会直接报错，无法正确获取每个组合最新日期对应的score值。

实现方案 #

方案1：直接删除冗余数据（适合数据量较小的场景） #

使用窗口函数标记每个id_1+id_2组合下的行序号，update_date最新的行序号为1，删除序号大于1的冗余行即可：

DELETE FROM CUSTOMER_TEST
WHERE ROWID IN (
    SELECT rid
    FROM (
        SELECT 
            ROWID rid,
            ROW_NUMBER() OVER(PARTITION BY id_1, id_2 ORDER BY update_date DESC) rn
        FROM CUSTOMER_TEST
    ) t
    WHERE t.rn > 1
);
COMMIT;

也可以用KEEP聚合函数简化写法：

DELETE FROM CUSTOMER_TEST
WHERE ROWID NOT IN (
    SELECT MAX(ROWID) KEEP(DENSE_RANK FIRST ORDER BY update_date DESC)
    FROM CUSTOMER_TEST
    GROUP BY id_1, id_2
);
COMMIT;

注意：如果存在同一个id_1+id_2组合有两条及以上记录的update_date完全相同的情况，上述语句会随机保留其中一条，你可以在窗口函数的ORDER BY子句后追加其他排序字段（比如score DESC）来明确保留规则。

方案2：CTAS重建表（适合数据量较大的场景，性能更高） #

如果表数据量很大，DELETE操作性能较差，可通过临时表中转的方式重建表，操作前需暂停对该表的业务写入，避免数据丢失：

-- 1. 创建临时表存储去重后的有效数据
CREATE TABLE CUSTOMER_TEST_TMP AS
SELECT update_date, id_1, id_2, score
FROM (
    SELECT 
        update_date, id_1, id_2, score,
        ROW_NUMBER() OVER(PARTITION BY id_1, id_2 ORDER BY update_date DESC) rn
    FROM CUSTOMER_TEST
) t
WHERE t.rn = 1;

-- 2. 清空原表
TRUNCATE TABLE CUSTOMER_TEST;

-- 3. 将去重后的数据插回原表
INSERT /*+ APPEND */ INTO CUSTOMER_TEST
SELECT * FROM CUSTOMER_TEST_TMP;
COMMIT;

-- 4. 删除临时表（可选）
DROP TABLE CUSTOMER_TEST_TMP;

后续优化建议 #

处理完历史重复数据后，建议增加唯一约束避免后续再产生重复数据：

ALTER TABLE CUSTOMER_TEST 
ADD CONSTRAINT CUSTOMER_TEST_UK UNIQUE (id_1, id_2);

后续新增/更新数据可以用MERGE语句，避免插入重复组合：

MERGE INTO CUSTOMER_TEST t
USING dual ON (t.id_1 = :new_id1 AND t.id_2 = :new_id2)
WHEN MATCHED THEN UPDATE SET t.score = :new_score, t.update_date = :new_date
WHEN NOT MATCHED THEN INSERT (update_date, id_1, id_2, score) VALUES (:new_date, :new_id1, :new_id2, :new_score);

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