对SQL Server表进行重复数据消除

SELECT 
   ID, first, last, dob, address, city, state, zip, telephone, 
   ROW_NUMBER() OVER (PARTITION BY first, last, dob, address, city, state, zip, telephone ORDER BY ID) AS RecordInstance
FROM PF_main

将为您提供每个唯一条目的“编号”(按Id排序)

因此,如果您有以下记录:

id,last,first,dob,地址,城市,州,邮编,电话
006,trevelyan,alec,“1954-05-15”,“阿尔伯特堤85号”,“伦敦”, “英国”,“1SE1 7TP”,0064
007,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “英国”,“1SE1 7TP”,0074
008,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “英国”,“SE1 7TP”,0074
009,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “英国”,“SE1 7TP”,0074

您将得到以下结果(请注意最后一列)

006,trevelyan,alec,“1954-05-15”,“阿尔伯特堤85号”,“伦敦”, “英国”,“1SE1 7TP”,0064, 1.
007,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “英国”,“1SE1 7TP”,0074, 1.
008,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “UK”,“SE1 7TP”,0074, 2.
009,邦德,詹姆斯,“1957-02-08”,“阿尔伯特堤85号”,“伦敦”, “UK”,“SE1 7TP”,0074, 3.

因此,您可以使用RecordInstance删除记录>1:

WITH Records AS
(
   SELECT 
      ID, first, last, dob, address, city, state, zip, telephone,
      ROW_NUMBER() OVER (PARTITION BY first, last, dob, address, city, state, zip, telephone ORDER BY ID) AS RecordInstance
   FROM PF_main
)
DELETE FROM Records
WHERE RecordInstance > 1

20亿行表相当大。让我假设 first , last 和 dob 构成“人”。我的建议是在“人物”上建立一个索引,然后 truncate /重新插入方法。

实际上,这看起来像:

create index idx_pf_main_first_last_dob on pf_main(first, last, dob);

select m.*
into temp_pf_main
from pf_main m
where not exists (select 1
                  from pf_main m2
                  where m2.first = m.first and m2.last = m.last and m2.dob = m.dob and
                        m2.id < m.id
                 );

truncate table pf_main;

insert into pf_main
    select *
    from temp_pf_main;

其他答案肯定会给你语法方面的想法。

对于20亿行,您的关注可能涉及语法之外的其他内容,因此我将为您提供适用于许多数据库的通用答案。如果您无法在一个会话中“在线”执行删除或复制,或者空间不足,请考虑以下增量方法。

删除如此大的数据可能需要很长时间才能完成,例如几小时甚至几天,而且在完成之前可能会失败。在少数情况下,令人惊讶的是,效果最好的方法是一个基本的、长时间运行的存储过程,它只需要少量批处理,并每隔几条记录提交一次(这里很少是相对术语)。可能只有100、1000或10000条记录。当然,它看起来并不优雅,但重点是它是“增量”的,而且资源消耗量低。

其思想是标识一个分区键,通过该键可以寻址记录范围(以划分工作集),或者执行初始查询以标识另一个表的重复键。然后一次一小批地遍历这些键,删除,然后提交,然后重复。如果您在没有临时表的情况下执行此操作,请通过添加适当的条件来减少结果集,并保持光标或排序区域的大小较小,从而确保范围较小。

-- Pseudocode
counter = 0;
for each row in dup table       -- and if this takes long, break this into ranges
   delete from primary_tab where id = @id
   if counter++ > 1000 then
      commit;
      counter = 0;
   end if
end loop

这个存储过程可以停止和重新启动,而不必担心会发生巨大的回滚,而且它还可以可靠地运行数小时或数天,不会对数据库可用性产生重大影响。在Oracle中,这可能是撤消段和排序区域大小以及其他事情,但在MSSQL中,我不是专家。最终,它会完成。同时,您不会将目标表与锁或大型事务绑定在一起,因此DML可以在表上继续。需要注意的是,如果DML继续执行,那么您可能需要将快照重复到dup-ids表中,以处理自快照以来出现的任何重复。

注意:这并不像完全构建一个新表那样对空闲的块/行进行碎片整理或合并已删除的空间,但它确实允许在不分配新副本的情况下在线完成。另一方面,如果您可以在网上和/或维护窗口中自由地执行此操作,并且重复行大于数据的15-20%,那么您应该选择“从原始减去重复项中选择*创建表”方法,如Gordon的回答所示,以便将数据段压缩为一个密集使用的连续段,并从长远来看获得更好的缓存/IO性能。然而,很少有重复项超过空间百分比的一小部分。

这样做的原因包括:

1-表太大,无法创建临时重复数据消除拷贝。

2-一旦新表准备好,您就不能或不想删除原始表来进行交换。

3-你不可能有一个维护窗口来完成一个巨大的操作。

否则,请看戈登·林诺夫的回答。

其他人都在技术方面提供了很好的方法,我只想补充一点。

IMHO很难完全自动化消除大量人员中重复项的过程。如果匹配过于宽松。。。则合法记录将被删除。如果匹配太严格。。。则将保留副本。

对于我的客户,我构建了一个类似于上面的查询,该查询返回表示LIKELY重复的行,使用地址和姓氏作为匹配条件。然后,他们查看可能的行列表,并在选择要复制的行上单击“删除”或“合并”。

这可能在您的项目中不起作用(具有数十亿行),但在需要避免重复和丢失数据的环境中,这是有意义的。单个人工操作员可以在几分钟内改善数千行数据的清洁度,而且他们可以在多个会话中一次完成一点。

IMHO没有一种最佳的重复数据消除方法,这就是为什么您会看到这么多不同的解决方案。这取决于你的情况。现在,我有一个大的历史文件,列出了每年数万个贷款账户的每月指标。每个帐户在其保持活动状态的多个月结束时都会显示在文件中,但当其变为非活动状态时,将不会在以后的日期出现。我只想要每个帐户的最后或最新记录。我不在乎20年前开户时的记录,我只在乎5年前账户关闭时的最新记录。对于那些仍处于活动状态的帐户,我需要最近一个日历月的记录。所以我认为“重复”是同一个账户的记录,除了该账户的最后一个月记录之外,其他所有记录都是重复的,我想把它们去掉。

这可能不是你的确切问题,但我提出的解决方案可能会给你带来你想要的解决方案。

(我大部分的sql代码都是用SAS PROC sql编写的,但我想你会明白的。)

我的解决方案使用子查询。。。

 /* dedupe */ 
 proc sql ;   
   create table &delta. as       
     select distinct b.*, sub.mxasof
       from &bravo. b
       join ( select distinct Loan_Number, max(asof) as mxasof format 6.0
                from &bravo.
                group by Loan_Number
            ) sub
       on 1
       and b.Loan_Number = sub.Loan_Number
       and b.asof = sub.mxasof
       where 1
       order by b.Loan_Number, b.asof desc ;