在SQL Server 2016中删除部分重复行

Question

我有一个数据集，其中一些列的值是相同的，但是其他列的值不同。我需要删除重复项，其中较低级别（Level2、Level3和Level4）的SubCategory“IS NOT NULL”，但其对应的“重复伙伴”（按[SubCategory Level 1 ID]、[Product Category]和[Product Name]分组）具有相同的较低级别SubCategory -“ IS NULL”。根据下表，我需要删除ID为2、4、6和9的行（请参见以红色字体突出显示的部分）。

我尝试使用Dense_Rank、Rank和Row_Number函数与Partition By，但这并没有给我所需的输出。也许我需要使用它们的组合...

例如：RowID 1和2按[Product Category]、[Product Name]、[Category Level 1]进行了重复。 “Category Level 1”只是“Product Category”的ID。当RowID 2有时，我需要删除RowID 2，因为它对应的重复伙伴RowID 1没有分配“Category Level 3”。相同的逻辑适用于RowID 9和10，但此时RowID 9具有“Category Level 2”，而Row 10则没有。如果两个重复项（RowID 1和2）都分配了“Category Level 3”，我们就不需要删除任何一个重复项。

图片链接：

IF OBJECT_ID('tempdb..#Category', 'U') IS NOT NULL
  DROP TABLE #Category;
GO

CREATE TABLE #Category 
(
    RowID INT NOT NULL,
    CategoryID INT NOT NULL,
    ProductCategory VARCHAR(100) NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    [SubCategory Level 1 ID] INT NOT NULL,
    [SubCategory Level 2 ID] INT NULL,
    [SubCategory Level 3 ID] INT NULL,  
    [SubCategory Level 4 ID] INT NULL
);

INSERT INTO #Category (RowID, CategoryID, ProductCategory, ProductName, [SubCategory Level 1 ID], [SubCategory Level 2 ID], [SubCategory Level 3 ID], [SubCategory Level 4 ID])
VALUES 
(1, 111, 'Furniture', 'Table', 200, 111, NULL, NULL),
(2, 234, 'Furniture', 'Table', 200, 234, 123, NULL),
(3, 122, 'Furniture', 'Chair', 200, 122, NULL, NULL),
(4, 122, 'Furniture', 'Chair', 200, 122, 32, NULL),
(5, 12, 'Auto', 'Trucks', 300, 766, 12, NULL),
(6, 3434, 'Auto', 'Trucks', 300, 322, 3434, 333),
(7, 332, 'Auto', 'Sport Vehicles', 300, 332, NULL, NULL),
(8, 332, 'Auto', 'Sport Vehicles', 300, 332, NULL, NULL),
(9, 300, 'Auto', 'Sedans', 300, 231, NULL, NULL),
(10, 300, 'Auto', 'Sedans', 300, NULL, NULL, NULL),
(11, 300, 'Auto', 'Cabriolet', 300, 456, 688, NULL),
(12, 300, 'Auto', 'Cabriolet', 300, 456, 976, NULL),
(13, 300, 'Auto', 'Motorcycles', 300, 456, 235, 334),
(14, 300, 'Auto', 'Motorcycles', 300, 456, 235, 334);


SELECT * FROM #Category; 
-- ADD YOU CODE HERE TO RETURN the following RowIDs: 2, 4, 6, 9

- Data Engineer

---

请提供您的现有查询 - Squirrel

你的需求很难理解。你能解释一下为什么要删除2、4、6和9吗？ - forpas

嗨Forpas，他们应该因为完全相同的原因被删除。例如：行ID 1和2是由[产品类别]，[产品名称]，[类别级别1]重复的。 "类别级别1"只是"产品类别"的ID。需要删除RowID 2，因为它对应的伙伴RowID 1没有分配"类别级别3"，而RowID 2有。RowID 9和10也是同样的逻辑，但此时RowID 9具有"类别级别2"，而Row 10没有。如果两个重复项（RowID 1和2）都分配了"类别级别3"，我们就不需要删除它们中的任何一个。 - Data Engineer

Answer 1

这篇文章帮助我理解了一种与删除重复日期有关的不同方法。我要感谢原始贡献者。然而，我注意到最终解决方案不完整。原始发布者想要返回RowId 2、4、6、9的结果，但是ToInclude != 1过滤器无法实现。我正在添加代码来完成查询，通过添加where > 1过滤器来产生预期的结果。请参见下面的代码：

;with DataToSelect
as
(
    SELECT *,
           DENSE_RANK() OVER(PARTITION BY [ProductCategory], [ProductName], [SubCategory Level 1 ID] 
                    ORDER BY
                        CASE 
                            WHEN [SubCategory Level 4 ID] IS NOT NULL THEN 3
                            WHEN [SubCategory Level 3 ID] IS NOT NULL THEN 2
                            WHEN [SubCategory Level 2 ID] IS NOT NULL THEN 1
                            END) as [ToInclude]
    FROM #Category
)
SELECT *
FROM 
    DataToSelect
WHERE 
    ToInclude > 1
ORDER BY 
    RowID

这将返回：

代码结果表格

- Chadwick

Answer 2

如果我理解正确，您的逻辑如下：

对于每个唯一的“SubCategory Level 1”，“Product Category”和“Product Name”组合，您希望返回填充的SubCategory级别数据最少的行。

使用相关字段上的快速“dense_rank”和“partitions”，您可以将具有较少Sub Category级别的行设置为“1”。现在应该只返回第2、4、6和9行。

;with DataToSelect
as
(
    SELECT *,
           DENSE_RANK() OVER(PARTITION BY [ProductCategory], [ProductName], [SubCategory Level 1 ID] 
                    ORDER BY
                        CASE 
                            WHEN [SubCategory Level 4 ID] IS NOT NULL THEN 3
                            WHEN [SubCategory Level 3 ID] IS NOT NULL THEN 2
                            WHEN [SubCategory Level 2 ID] IS NOT NULL THEN 1
                            END) as [ToInclude]
    FROM #Category
)
SELECT *
FROM 
    DataToSelect
WHERE 
    ToInclude != 1
ORDER BY 
    RowID

请记住，如果您在每个SubCategory Level 1、Product Category和Product Name组合中有两行具有相同的子类别级别，它们都将被包括在内。如果您不希望这样，请将dense_rank替换为row_number，并添加一些替代标准以确定应首选哪个。

- Matt

---

嗨，马特，我已经尝试过这种方法了，这不是我想要实现的。正如我所说，我只需要第2、4、6和9行。逻辑是删除所有重复的记录（按类别1、类别名称和产品名称），其中一个或多个重复记录中的较低级别类别不为空，但其中一个重复行未分配相同的较低级别类别（为NULL）。因此，我将仅保留具有更高级别类别且没有较低级别的行（请参见上面的屏幕截图，它具有完全相同的模式）。 - Data Engineer

您的查询返回RowIDs - 1、3、6、7、8和10。 - Data Engineer

好的，你的问题不太清楚。如果你只想要第2,4,6,9行，那么我刚刚已经将where子句交换为!=。为什么你想要接收重复的行而不是非重复的行呢？ - Matt

Matt，我已经更新了我的代码，并提供了一个回答你问题的示例。当切换到“ ToInclude！= 1”时，它会返回RowID 2、4、5和9。我们不应该删除RowID 5。但是需要删除重复的Row_ID 6，因为它有“分配类别级别4”（333）。因此，你可以看到它仅对类别级别2和“级别3”有效，而不适用于“类别级别4”。 - Data Engineer

Matt，你很接近了。我们还需要将CASE语句调整如下： 当[SubCategory Level 4 ID]不为空时，THEN为3 当[SubCategory Level 3 ID]不为空时，THEN为2 当[SubCategory Level 2 ID]不为空时，THEN为1 我已经更新了你的代码。非常感谢！ - Data Engineer