Oracle PL/SQL删除表重复数据并保留正确client_id记录

原方案问题梳理 #

原有SQL和流程存在3个核心错误：

• 重复判定分组维度错误：GROUP BY字段包含client_id，会把client_id='-1'的无效记录和正确client_id的记录拆成两个独立分组，无法识别同一业务场景下混有无效client_id的重复数据
• 分组查询语法错误：SELECT列表包含contact_id、start_time等非分组字段，开启ONLY_FULL_GROUP_BYSQL模式时会直接报语法错误；即使绕过语法限制，也会返回同分组下多条有效记录，无法实现每组仅保留1条的要求
• 删除逻辑有漏洞：仅通过client_id关联删除，既无法清除同组下client_id='-1'的无效重复数据，还可能误删其他client_id相同的非重复业务记录

基于测试表中转的正确实现流程 #

1. 初始化测试表 #

先创建和主表字段结构完全一致的空测试表：

CREATE TABLE table_test AS SELECT * FROM `table` WHERE 1=0;

2. 插入每个重复分组的唯一有效记录 #

通过窗口函数给同业务维度的记录排序，仅取每个分组中client_id有效的第一条记录写入测试表，保留并行写入提示提升大表操作效率：

INSERT /*+ append enable_parallel_dml parallel(16)*/
INTO table_test
SELECT 
    contact_id,
    call_siebel,
    start_time,
    operator_text,
    client_text,
    client_id,
    phone_num
FROM (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(
            -- 按业务唯一维度分组，不包含错误字段client_id
            PARTITION BY operator_text, client_text
            -- 排序规则：优先取client_id不为-1的有效记录，同优先级下按开始时间倒序取最新的
            ORDER BY CASE WHEN client_id != '-1' THEN 0 ELSE 1 END, start_time DESC
        ) AS rn
    FROM `table` t
    -- 仅筛选存在重复的业务分组
    WHERE (operator_text, client_text) IN (
        SELECT operator_text, client_text
        FROM `table`
        GROUP BY operator_text, client_text
        HAVING COUNT(*) > 1
    )
) tmp
WHERE rn = 1 AND client_id != '-1';

可根据业务需求调整排序规则，比如需要保留最早创建的记录就把start_time DESC改成start_time ASC，或按contact_id排序。

3. 删除主表所有重复分组的全量数据 #

按业务维度匹配删除，避免误删和漏删：

DELETE FROM `table`
WHERE (operator_text, client_text) IN (
    SELECT operator_text, client_text FROM table_test
);

4. 回插有效记录 #

将测试表中留存的唯一有效记录插回主表即可完成去重：

INSERT INTO `table`
SELECT * FROM table_test;

高效单表去重方案（无需中转测试表） #

如果数据库版本支持窗口函数（MySQL8.0+、Oracle、PostgreSQL等均支持），可以直接单表操作完成去重，执行前建议先备份全表数据：

-- 开启事务，确认结果正确前可回滚
START TRANSACTION;

DELETE FROM `table`
WHERE contact_id IN (
    SELECT contact_id FROM (
        SELECT 
            contact_id,
            ROW_NUMBER() OVER(
                PARTITION BY operator_text, client_text
                ORDER BY CASE WHEN client_id != '-1' THEN 0 ELSE 1 END, start_time DESC
            ) AS rn
        FROM `table`
    ) tmp
    WHERE rn > 1
);

-- 核对删除后的数据符合预期再执行COMMIT，异常则执行ROLLBACK回滚
COMMIT;

该方案直接按业务维度分组，每个分组仅保留1条client_id有效的记录，其余重复记录（包括client_id='-1'的无效数据、同client_id的多份有效重复数据）全部删除，大表场景下执行效率远高于中转测试表的方案。

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