logo标签列表

如何删除重复条目？

sqlpostgresqlduplicatesunique-constraintsql-delete
93

我需要在现有的表中添加唯一约束。但这个表已经有数百万行数据，并且其中许多行违反了我需要添加的唯一约束。

如何最快速地删除这些问题行？我有一个SQL语句可以查找并删除重复行，但运行时间非常长。是否有其他解决方法？例如在添加约束后备份表，然后恢复数据？

16个回答
3

如果您只有一个或少量重复的条目，并且它们确实是重复的（即它们出现两次），则可以使用上面提出的“隐藏”ctid列，再加上LIMIT：

DELETE FROM mytable WHERE ctid=(SELECT ctid FROM mytable WHERE […] LIMIT 1);

这将仅删除所选行中的第一行。

我知道这并没有解决 OP 的问题，他有数百万行的重复数据，但这可能仍然有所帮助。 - Skippy le Grand Gourou
这个操作需要针对每一行副本运行一次。shekwi的答案只需要运行一次。 - bradw2k
3

首先，您需要决定保留哪个“重复项”。如果所有列都相等，那么可以删除任何一个...但也许您只想保留最新的或其他标准？

最快的方法取决于您对上述问题的回答，还取决于表中重复项的百分比。如果删除了50％的行，则最好使用CREATE TABLE ... AS SELECT DISTINCT ... FROM ... ;，如果删除了1％的行，则使用DELETE更好。

此外，对于像这样的维护操作，通常最好将work_mem设置为您RAM的一大部分：运行EXPLAIN，检查排序/哈希的数量N，并将work_mem设置为RAM / 2 / N。使用大量RAM；速度更快。只要您只有一个并发连接...

1

我正在使用PostgreSQL 8.4。当我运行建议的代码时，我发现它实际上没有删除重复项。在运行一些测试后，我发现添加“DISTINCT ON（duplicate_column_name）”和“ORDER BY duplicate_column_name”就可以解决问题了。虽然我不是SQL大师，但我在PostgreSQL 8.4 SELECT...DISTINCT文档中找到了这个方法。

CREATE OR REPLACE FUNCTION remove_duplicates(text, text) RETURNS void AS $$
DECLARE
  tablename ALIAS FOR $1;
  duplicate_column ALIAS FOR $2;
BEGIN
  EXECUTE 'CREATE TEMPORARY TABLE _DISTINCT_' || tablename || ' AS (SELECT DISTINCT ON (' || duplicate_column || ') * FROM ' || tablename || ' ORDER BY ' || duplicate_column || ' ASC);';
  EXECUTE 'DELETE FROM ' || tablename || ';';
  EXECUTE 'INSERT INTO ' || tablename || ' (SELECT * FROM _DISTINCT_' || tablename || ');';
  EXECUTE 'DROP TABLE _DISTINCT_' || tablename || ';';
  RETURN;
END;
$$ LANGUAGE plpgsql;
1
CREATE TABLE test (col text);
INSERT INTO test VALUES
 ('1'),
 ('2'), ('2'),
 ('3'),
 ('4'), ('4'),
 ('5'),
 ('6'), ('6');
DELETE FROM test
 WHERE ctid in (
   SELECT t.ctid FROM (
     SELECT row_number() over (
               partition BY col
               ORDER BY col
               ) AS rnum,
            ctid FROM test
       ORDER BY col
     ) t
    WHERE t.rnum >1);
我测试过了，它可以正常工作；我对其进行了格式化以提高可读性。它看起来相当复杂，但可能需要一些解释。如果要将此示例更改为自己的用例，应该如何操作？ - Tobias
1
这很好地工作，并且非常快：

CREATE INDEX otherTable_idx ON otherTable( colName );
CREATE TABLE newTable AS select DISTINCT ON (colName) col1,colName,col2 FROM otherTable;
1
DELETE FROM tablename
WHERE id IN (SELECT id
    FROM (SELECT id,ROW_NUMBER() OVER (partition BY column1, column2, column3 ORDER BY id) AS rnum
                 FROM tablename) t
          WHERE t.rnum > 1);

通过列删除重复项并保留最低id的行。该模式取自postgres wiki。
使用CTE，您可以通过以下方式实现以上更易读的版本。
WITH duplicate_ids as (
    SELECT id, rnum 
    FROM num_of_rows
    WHERE rnum > 1
),
num_of_rows as (
    SELECT id, 
        ROW_NUMBER() over (partition BY column1, 
                                        column2, 
                                        column3 ORDER BY id) AS rnum
        FROM tablename
)
DELETE FROM tablename 
WHERE id IN (SELECT id from duplicate_ids)
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接