时间戳

首页 > 解决方案 > 从非常大的表中删除重复的子集

sql - 从非常大的表中删除重复的子集

问题描述

我正在使用的数据相当复杂，所以我将提供一个更简单的示例，希望可以将其扩展到我正在处理的内容。

注意：我已经找到了一种方法，但它非常慢且不可扩展。它在小型数据集上效果很好，但如果我将它应用到它需要运行的实际表上，那将需要很长时间。

我需要删除表中整个重复的数据子集。删除重复的行很容易，但我一直在寻找一种有效的方法来删除重复的子集。

例子：

GroupID  Subset Value
-------  ----   ----
1        a      1
1        a      2
1        a      3

1        b      1
1        b      3
1        b      5

1        c      1
1        c      3
1        c      5


2        a      1
2        a      2
2        a      3

2        b      4
2        b      5
2        b      6

2        c      1
2        c      3
2        c      6

因此，在此示例中，从 GroupID 1 中，我需要删除子集“b”或子集“c”，这并不重要，因为两者都包含值 1、2、3。对于 GroupID 2，没有一个集合是重复的，所以没有一个被删除。

这是我用来小规模解决这个问题的代码。它工作得很好，但是当应用于 10+ 百万条记录时......你可以想象它会很慢（我后来被告知记录的数量，我得到的样本数据要小得多）......：

DECLARE @values TABLE (GroupID INT NOT NULL, SubSet VARCHAR(1) NOT NULL, [Value] INT NOT NULL)
INSERT INTO @values (GroupID, SubSet, [Value])
VALUES  (1,'a',1),(1,'a',2),(1,'a',3)  ,(1,'b',1),(1,'b',3),(1,'b',5)  ,(1,'c',1),(1,'c',3),(1,'c',5),
        (2,'a',1),(2,'a',2),(2,'a',3)  ,(2,'b',2),(2,'b',4),(2,'b',6)  ,(2,'c',1),(2,'c',3),(2,'c',6)

SELECT *
FROM @values v
ORDER BY v.GroupID, v.SubSet, v.[Value]

SELECT x.GroupID, x.NameValues, MIN(x.SubSet)
FROM (
    SELECT t1.GroupID, t1.SubSet
        , NameValues = (SELECT ',' + CONVERT(VARCHAR(10), t2.[Value]) FROM @values t2 WHERE t1.GroupID = t2.GroupID AND t1.SubSet = t2.SubSet ORDER BY t2.[Value] FOR XML PATH(''))
    FROM @values t1
    GROUP BY t1.GroupID, t1.SubSet
) x
GROUP BY x.GroupID, x.NameValues

我在这里所做的只是按 GroupID 和 Subset 进行分组，并将所有值连接到一个逗号分隔的字符串中……然后将其分组到 GroupID 和 Value 列表中，然后取 MIN 子集。

标签： sqlsql-servertsql

解决方案

我会用这样的东西：

;with cte as
(
    select v.GroupID, v.SubSet, checksum_agg(v.Value) h, avg(v.Value) a
    from @values v
    group by v.GroupID, v.SubSet
)

delete v
from @values v
join
(
    select c1.GroupID, case when c1.SubSet > c2.SubSet then c1.SubSet else c2.SubSet end SubSet
    from cte c1
    join cte c2 on c1.GroupID = c2.GroupID and c1.SubSet <> c2.SubSet and c1.h = c2.h and c1.a = c2.a
)x on v.GroupID = x.GroupID and v.SubSet = x.SubSet

select *
from @values

推荐阅读