从大型数据集（>100Mio行）中删除重复项

Question

我知道这个话题在这里之前多次出现过，但是对于我的数据集，所提出的解决方案都没有起作用，因为我的笔记本由于内存问题或存储空间已满而停止计算。

我的表格如下所示，有108万行:

Col1       |Col2   |  Col3           |Col4   |SICComb |  NameComb 

Case New   |3523   |  Alexander      |6799   |67993523| AlexanderCase New 
Case New   |3523   |  Undisclosed    |6799   |67993523| Case NewUndisclosed 
Undisclosed|6799   |  Case New       |3523   |67993523| Case NewUndisclosed 
Case New   |3523   |  Undisclosed    |6799   |67993523| Case NewUndisclosed 
SmartCard  |3674   |  NEC            |7373   |73733674| NECSmartCard 
SmartCard  |3674   |  Virtual NetComm|7373   |73733674| SmartCardVirtual NetComm 
SmartCard  |3674   |  NEC            |7373   |73733674| NECSmartCard

唯一的列是 SICComb 和 NameComb。我试着加入一个主键:

ALTER TABLE dbo.test ADD ID INT IDENTITY(1,1)

但是整数类型的数据在短短几分钟内就占用了超过30GB的存储空间。

从表格中删除重复项的最快且最有效的方法是什么？

- user2713440

---

@user2713440 你对“重复”的定义是什么？当所有列都相同时？还是当SICComb和NameComb相同时？ - Luis LL

当SICComb和NameComb完全相同时。 - user2713440

那么你如何决定保留哪一个？ - paparazzo

Answer 1

如果您正在使用SQL Server，可以使用通用表达式删除：

with cte as (
    select row_number() over(partition by SICComb, NameComb order by Col1) as row_num
    from Table1
)
delete
from cte
where row_num > 1

这里所有的行都将被编号，每个唯一组合的 SICComb + NameComb 都会有自己的序列。您可以通过在 over 子句中选择 order by 来选择要删除的行。

- Roman Pekar

---

@ShahgholiArdalan 不要碰我的代码。我认为在SQL中使用大写字母的传统应该消失，我总是按照可读性格式化我的代码，不要让我的回答变得更糟糕！ - Roman Pekar

这种方法似乎比接受的答案更有效，可以从具有大量列和行以及包含唯一标识符的列的表中删除重复项。 - Malcolm

并非所有的查询都是受益的，有些查询可能会出现严重的倒退。这取决于您的索引是否对齐。一般来说，对齐的索引访问速度稍慢。有时需要访问所有分区索引B树才能找到键。当搜索关键字未作为前导段分区时，排序/ TOP查询可能会变得非常缓慢。 - Sam

Answer 2

一般来说，从表格中删除重复项的最快方法是将没有重复项的记录插入到一个临时表中，然后清空原表并将这些记录重新插入。

以下是使用SQL Server语法的思路：

select distinct t.*
into #temptable
from t;

truncate table t;

insert into t
    select tt.*
    from #temptable;

当然，这在很大程度上取决于第一步的速度。而且，你需要有足够的空间来存储相同表的两个副本。

请注意，在不同的数据库中创建临时表的语法是不同的。有些使用create table as的语法，而不是select into的语法。

编辑：

您的身份插入错误很麻烦。我认为你需要从不同的列中删除身份。或者做如下操作：

select min(<identity col>), <all other columns>
from t
group by <all other columns>

如果您有一个标识列，那么就不会有重复值（根据定义）。最终，您需要决定哪些id适用于这些行。如果您可以为这些行生成新的id，则只需在插入的列列表中省略标识列即可。

insert into t(<all other columns>)
    select <all other columns>;

如果您需要旧的身份值（最小值即可），请关闭身份插入并执行以下操作：

insert into t(<all columns including identity>)
    select <all columns including identity>;

- Gordon Linoff

---

+1，我喜欢这个解决方案，因为它干净而且符合最佳实践。但我也会在执行此过程之前添加注释，以收集所有信息/统计数据，可能重复项的数量很少，直接删除它们比插入数百万行并来回操作更快/更好... - MrSimpleMind

@MrSimpleMind . . . 您是正确的。如果表中有数百万行数据，只有少量重复项（比如几千个），那么直接使用 delete 更好。 - Gordon Linoff

@Gordon Linoof：感谢您的回答。不幸的是，它显示我的Identify_INSERT没有打开。运行代码的结果是一个空表。 - user2713440

@Gordon Linoof：你的编辑有所帮助。但现在出现了错误消息“提供的值的列名或数量与表定义不匹配”。 - user2713440

@user2713440...你需要确保insert的列列表和select后面的列表具有相同的列并且顺序相同。 - Gordon Linoff