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

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。它似乎不喜欢 WITH 子句之后的 DELETE ？

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;

如果您想在删除记录之前查看记录，只需将 DELETE 替换为 SELECT * 并将 USING 替换为逗号 ,, 即

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 中您真的不应该这样做)，那么您可以在该列的条件，例如

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