一意な識別子を持たない重複した行を削除する方法

質問

テーブルに重複した行があり、テーブルが大きいので最も効率的な方法で重複を削除したいと思います。いくつかの研究の後、私はこのクエリを思いついた。

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 句の後？

どのように解決するのですか？

私は @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 しないほうがいいのですが)、そのカラムに COALESCE() をそのカラムの条件に使うことができます。

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