如何删除没有唯一标识符的重复行

Question

我的表中有重复的行，我想以最有效的方式删除重复行，因为表很大。经过一番研究，我想出了这个查询：

 WITH TempEmp AS
(
SELECT name, ROW_NUMBER() OVER(PARTITION by name, address, zipcode ORDER BY name) AS duplicateRecCount
FROM mytable
)
-- Now Delete Duplicate Records
DELETE FROM TempEmp
WHERE duplicateRecCount > 1;

但它只适用于 SQL，而不适用于 Netezza。它似乎不喜欢 DELETE WITH ？

原文由 moe 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

我喜欢 @erwin-brandstetter 的解决方案，但想展示一个带有 USING 关键字的解决方案：

 DELETE   FROM table_with_dups T1
  USING       table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- delete the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

If you want to review the records before deleting them, then simply replace DELETE with SELECT * and USING with a comma , , ie

 SELECT * FROM table_with_dups T1
  ,           table_with_dups T2
WHERE  T1.ctid    < T2.ctid       -- select the "older" ones
  AND  T1.name    = T2.name       -- list columns that define duplicates
  AND  T1.address = T2.address
  AND  T1.zipcode = T2.zipcode;

更新：我在这里测试了一些不同的解决方案以提高速度。如果您不希望有很多重复项，那么此解决方案的性能比具有 NOT IN (...) 子句的解决方案要好得多，因为它们会在子查询中生成大量行。

如果您重写查询以使用 IN (...) 那么它的执行与此处提供的解决方案类似，但 SQL 代码变得不那么简洁。

更新 2：如果您在关键列之一中有 NULL 值（您真的不应该在 IMO），那么您可以在该列的条件下使用 COALESCE() ，例如

  AND COALESCE(T1.col_with_nulls, '[NULL]') = COALESCE(T2.col_with_nulls, '[NULL]')

原文由 isapir 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

要从数据如下所示的表“选项卡”中删除重复项（仅保留一个条目）：

| fk_id_1 | fk_id_2 |

| 12 | 32 |

| 12 | 32 |

| 12 | 32 |

| 15 | 37 |

| 15 | 37 |

你可以这样做：

 DELETE FROM tab WHERE ctid IN
  (SELECT ctid FROM
    (SELECT ctid, fk_id_1, fk_id_2, row_number() OVER (PARTITION BY fk_id_1, fk_id_2 ORDER BY fk_id_1) AS rnum FROM tab) t
  WHERE t.rnum > 1);

其中 ctid 是行在其表中的物理位置（因此是行标识符）， row_number 是一个窗口函数，它为结果集中的每一行分配一个顺序整数。

PARTITION 对结果集进行分组，并为每个组重新启动顺序整数。

原文由 Gozar-Manu Ariana 发布，翻译遵循 CC BY-SA 4.0 许可协议