从小表中删除重复行

Question

我有一个存储在PostgreSQL 8.3.8数据库中的表格，该表格没有任何键或约束，并且有多行具有完全相同的值。

我希望删除所有重复项并仅保留每行的一份副本。

特别地，有一个列（名为“key”）可用于识别重复项，即每个不同的“key”只应存在一个条目。

我该如何做？ （最好使用单个SQL命令。）
在此情况下速度不是问题（只有几行数据）。

- André Morujão

Answer 1

一个更快的解决方案是：

• 找到第一个重复出现的位置，
• 然后删除除第一个重复位置外的所有行。

具体如下：

DELETE FROM dups a USING (
    SELECT MIN(ctid) as ctid, key
    FROM dups 
    GROUP BY key HAVING COUNT(*) > 1
) b
WHERE a.key = b.key 
AND a.ctid <> b.ctid

请注意，使用此解决方案时，您无法控制保留哪一行。

玩具示例

CREATE TABLE people (
    name    varchar(50) NOT NULL,
    surname varchar(50) NOT NULL,
    age     integer NOT NULL
);

INSERT INTO people (name, surname, age) VALUES 
    ('A.', 'Tom', 30),
    ('A.', 'Tom', 10),
    ('B.', 'Tom', 20),
    ('B', 'Chris', 20);

-- The inner command to find duplicates first occurences:
SELECT MIN(ctid) as ctid, name, surname
FROM people 
GROUP BY (name, surname) HAVING COUNT(*) > 1;


DELETE FROM people a USING (
    SELECT MIN(ctid) as ctid, name, surname
    FROM people 
    GROUP BY (name, surname) HAVING COUNT(*) > 1
) b
WHERE a.name = b.name
AND a.surname = b.surname
AND a.ctid <> b.ctid;

SELECT * FROM people;

内部请求输出：

ctid	name	surname
(0,1)	A.	Tom

最终请求（删除后）的输出为：

name	surname	age
A.	Tom	30
B.	Tom	20
B	Chris	20

在DB Fiddle上查看玩具示例

- rapimo

---

为什么它比一个名叫“a_horse_with_no_name”的方案更快？ - Roberto

这个更快，因为它只运行了2个查询。第一个是选择所有重复项，第二个是从表中删除所有项目。@a_horse_with_no_name的查询为每个表中的项目都执行一次查询，以查看它是否与其他任何项目匹配。 - Aeolun

"ctid"是什么？ - techkuz

来自文档：ctid。指的是行版本在表中的物理位置。请注意，尽管可以使用ctid非常快速地定位行版本，但每次通过VACUUM FULL更新或移动行时，该行的ctid都会发生更改。因此，ctid无法作为长期的行标识符。 - Saim

@Daria，你错了。这个查询语句会删除所有重复记录，其中ctid不是每个键的最小citid。一个简单的测试可以证明这一点。创建表t_location (country text,city text); 插入t_location值 ('Country', 'City1'), ('Country','City2'),('Country','City3'); --重复多次 从t_location a using ( 选择min(ctid) as ctid, city 从t_location 分组城市拥有计数(*) > 1 ) b 其中a.city = b.city 和a.ctid <> b.ctid; 选择*从t_location order by city; --仅有3条记录 - EAmez

Answer 2

DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
                 FROM   dupes b
                 WHERE  a.key = b.key);

- user330315

---

别用它，它太慢了！ - Paweł Malisak

虽然这个解决方案肯定有效，但是@rapimo在下面的解决方案中的执行速度更快。我相信这与此处的内部Select语句被执行N次有关（对于dupes表中的所有N行），而不是在其他解决方案中正在进行的分组有关。 - David

对于巨大的表格（数百万条记录），这个解决方案实际上适合内存，不像@rapimo的解决方案。因此，在这些情况下，这是更快的解决方案（无需交换）。 - Giel

添加解释：它的可行性是由于ctid是一个特殊的PostgreSQL列，表示行的物理位置。即使您的表没有唯一ID，您也可以使用它作为唯一标识符。https://www.postgresql.org/docs/8.2/ddl-system-columns.html - Eric Burel

@PawełMalisak 我在运行后才阅读了它。 - Sachin Verma

Answer 3

这是快速而简洁的：

DELETE FROM dupes T1
    USING   dupes T2
WHERE   T1.ctid < T2.ctid  -- delete the older versions
    AND T1.key  = T2.key;  -- add more columns if needed

请参阅我在如何在没有唯一标识符的情况下删除重复行中的答案，其中包括更多信息。

- isapir

---

"ct" 代表什么？计数（count）吗？ - techkuz

@trthhrtz中提到ctid指向表中记录的物理位置。与我在评论中写的相反，使用小于号操作符不一定指向较早版本，因为ct可以环绕并且具有较低ctid值的值实际上可能是更新的版本。 - isapir

只是提供信息，我尝试了这个解决方案，等待了15分钟后放弃了。尝试了rapimo的解决方案，大约10秒钟就完成了（删除了约700,000行）。 - phemmer

@isapir 我只是好奇，上面的答案，他们选择 min(ctid) 保留旧记录，而你的则保留新记录？谢谢！ - stucash

ctid指向表中记录的物理位置，而不是旧版本！ - Емилиян Йорданов

Answer 4

EXISTS 很简单，并且对于大多数数据分布来说是最快的：

DELETE FROM dupes d
WHERE  EXISTS (
   SELECT FROM dupes
   WHERE  key = d.key
   AND    ctid < d.ctid
   );

针对每组重复行（由相同的key定义），该操作将只保留具有最小ctid的一行。

结果与a_horse目前接受的答案相同。只是更快，因为EXISTS可以在找到第一行违规记录后立即停止评估，而使用min()的替代方法必须考虑每个组的所有行以计算最小值。速度对于这个问题来说不是问题，但为什么不抓住机会呢？

在清理后，您可能需要添加UNIQUE约束条件，以防止重复数据再次出现：

ALTER TABLE dupes ADD CONSTRAINT constraint_name_here UNIQUE (key);

关于系统列ctid:

• 系统列“ctid”用于标识要删除的行是否合法？

如果表中有任何其他定义为UNIQUE NOT NULL（例如PRIMARY KEY）的列，则请使用它而不是ctid。

如果key可以为NULL且您只需要其中一个，请改用IS NOT DISTINCT FROM而不是=。参见:

• 如何在多个列上进行DISTINCT选择？

因为这种方法较慢，所以您可以像原来一样运行上述查询，并额外添加以下内容：

DELETE FROM dupes d
WHERE  key IS NULL
AND    EXISTS (
   SELECT FROM dupes
   WHERE  key IS NULL
   AND    ctid < d.ctid
   );

考虑以下内容：

• 如何在含有NULL列的情况下创建唯一约束

对于小表格，索引通常不会提高性能。因此我们不需要继续查找。

对于大表格和少量重复记录，已经存在的(key)索引可以帮助提升性能（非常明显）。

对于大量重复记录，索引可能增加的成本比收益更多，因为它必须同时保持最新状态。无索引查找重复项变得更快，因为这么多重复项，而且EXISTS只需要找到一个。但是如果您能够负担得起（即允许并发访问），请考虑完全不同的方法：将存活的少数行写入新表中。这也会在过程中删除表格（和索引）膨胀。请参阅：

• 如何删除重复的条目？

- Erwin Brandstetter

Answer 5

我尝试了这个：

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);

由Postgres维基提供：

https://wiki.postgresql.org/wiki/Deleting_duplicates

- Radu Gabriel

---

如果像问题所述，_所有_列都相同，包括id，那么这个不起作用。 - ibizaman

@pyBomb 错了，它会保留 column1...3 都重复的第一个 id。 - Jeff

截至postgresql 12，这绝对是迄今为止最快的解决方案（针对3亿行）。我测试了这个问题中提出的所有方法，包括被接受的答案，而这个“官方”解决方案实际上是最快的，并且满足OP（和我的）所有要求。 - Jeff

这个工作像我想的那样，并且是当前答案中最快的。 - Jazz

这是我最常用的“partition by”示例，每隔几周我都会回到这里。 :D - K. Anye

Answer 6

我会使用临时表：

create table tab_temp as
select distinct f1, f2, f3, fn
  from tab;

然后，删除tab并将tab_temp更名为tab。

- Pablo Santa Cruz

---

这种方法没有考虑触发器、索引和统计信息。当然，你可以添加它们，但这也会增加很多工作量。 - Jordan

并不是每个人都需要这样做。这种方法非常快，而且在没有索引的情况下，在200,000封电子邮件（varchar 250）中的表现要比其他方法好得多。 - Sergey Telshevsky

完整代码：DROP TABLE IF EXISTS tmp; CREATE TABLE tmp as ( SELECT * from (SELECT DISTINCT * FROM your_table) as t ); DELETE from your_table; INSERT INTO your_table SELECT * from tmp; DROP TABLE tmp; - Eric Burel

Answer 7

我不得不创建自己的版本。@a_horse_with_no_name编写的版本在我的表格（21M行）上太慢了。而@rapimo根本就没有删除重复项。

以下是我在PostgreSQL 9.5上使用的代码：

DELETE FROM your_table
WHERE ctid IN (
  SELECT unnest(array_remove(all_ctids, actid))
  FROM (
         SELECT
           min(b.ctid)     AS actid,
           array_agg(ctid) AS all_ctids
         FROM your_table b
         GROUP BY key1, key2, key3, key4
         HAVING count(*) > 1) c);

- expert

Answer 8

另一种方法（仅适用于表中存在任何唯一字段，如id）可以通过列查找所有唯一的id，并删除不在唯一列表中的其他id。

DELETE
FROM users
WHERE users.id NOT IN (SELECT DISTINCT ON (username, email) id FROM users);

- Logovskii Dmitrii

---

问题在于，我的问题中的表没有唯一的ID；“重复项”是多行，在所有列上具有完全相同值的行。 - André Morujão

好的，我添加了一些注释。 - Logovskii Dmitrii

Answer 9

PostgreSQL有窗口函数，您可以使用rank()来实现您的目标，示例：

WITH ranked as (
    SELECT
        id, column1,
        "rank" () OVER (
            PARTITION BY column1
            order by column1 asc
        ) AS r
    FROM
        table1
) 
delete from table1 t1
using ranked
where t1.id = ranked.id and ranked.r > 1

- NewBee

Answer 10

这里是另一种解决方案，对我很有效。

delete from table_name a using table_name b
where a.id < b.id
  and a.column1 = b.column1;

- Simi Lika