从小表中删除重复的行

我在 PostgreSQL 8.3.8数据库中有一个表，它上面没有键/约束，并且有多行具有完全相同的值。

我想删除所有的副本，并保持每行只有1个副本。

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

我如何做到这一点? (理想情况下，使用一个 SQL 命令。)
在这种情况下，速度不是问题(只有几行)。

156313 次浏览
小开

我会使用一个临时表:

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

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

小开
最佳答案 
DELETE FROM dupes a
WHERE a.ctid <> (SELECT min(b.ctid)
FROM   dupes b
WHERE  a.key = b.key);
小开

更快的解决办法是

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
小开

我不得不创造我自己的版本。由@a _ horse _ with _ no _ name 编写的版本在我的表(21M 行)上太慢了。而@rapimo 根本不删除 Dup。

下面是我在 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);
小开

我试过了:

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 wiki 提供:

Https://wiki.postgresql.org/wiki/deleting_duplicates

小开

这招对我很管用。我有一个包含重复值的表，术语。运行一个查询，用所有重复的行填充临时表。然后我用临时表中的 id 运行了一个 delete 语句。Value 是包含重复项的列。

        CREATE TEMP TABLE dupids AS
select id from (
select value, id, row_number()
over (partition by value order by value)
as rownum from terms
) tmp
where rownum >= 2;


delete from [table] where id in (select id from dupids)
小开

这种说法既快速又简洁:

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

也可以在 如何删除没有唯一标识符的重复行上看到我的答案，其中包含了更多的信息。

小开

另一种方法(只有在表中有任何唯一字段(如 id)时才有效)按列查找所有唯一 id，并删除不在唯一列表中的其他 id

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

这个怎么样:

WITH
u AS (SELECT DISTINCT * FROM your_table),
x AS (DELETE FROM your_table)
INSERT INTO your_table SELECT * FROM u;

我曾经担心过执行顺序，DELETE 是否会发生在 SELECT DISTINCT 之前，但它对我来说工作得很好。 而且不需要任何关于表结构的知识。

小开

下面是一个解决方案 使用 PARTITION BY和 虚拟 ctid列，它们的工作原理类似于一个主键，至少在一个会话中是这样的:

DELETE FROM dups
USING (
SELECT
ctid,
(
ctid != min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])
) AS is_duplicate
FROM dups
) dups_find_duplicates
WHERE dups.ctid == dups_find_duplicates.ctid
AND dups_find_duplicates.is_duplicate

子查询用于将所有行标记为重复的或不重复的，基于它们是否共享相同的“键列”，但不是相同的 ctid，作为在共享相同键的行的“分区”中找到的“第一个”。

换句话说，“第一”的定义是:

  • min(ctid) OVER (PARTITION BY key_column1, key_column2 [...])

然后，所有 is_duplicate为 true 的行将被它们的 ctid删除。

根据文档，ctid代表(强调矿井) :

行版本在其表中的物理位置。请注意，虽然 ctid 可以用于 快速定位行版本，但是如果通过 VACUUM FULL 更新或移动行的 ctid，它将发生变化。因此，ctid 作为长期行标识符是无用的。应该使用主键来标识逻辑行。

小开

EXISTS 很简单，是大多数数据分发中最快的:

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

从每组重复的行(由相同的 key定义)中，保留一行最小的 ctid。

结果与 目前被一匹马接受的答案相同。只有 更快更强，因为一旦找到第一个违规行，EXISTS就可以停止计算，而使用 min()的替代方案必须考虑每组 所有行才能计算出最小值。速度与 这个问题无关，但为什么不拿走它呢？

你可能需要在清理后添加一个 UNIQUE约束 ，以防止重复内容爬回:

ALTER TABLE dupes ADD CONSTRAINT constraint_name_here UNIQUE (key);

关于系统栏 ctid:

如果表中有任何其他定义为 UNIQUE NOT NULL的列(如 PRIMARY KEY) ，那么一定要使用它而不是 ctid。

如果 key可以是 NULL，而你也只想要其中的一个，那么就用 IS NOT DISTINCT FROM代替 =。参见:

由于速度较慢，您可以按原样运行上面的查询，而这个 此外:

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

再想想:

对于较小的表，索引通常不能提高性能，我们不需要进一步研究。

对于 大桌子和 很少的重复，(key)上的现有索引可以提供很多帮助。

对于 大部分是复制品，一个索引可能会增加更多的成本而不是收益，因为它必须同时保持最新。无论如何，在没有索引的情况下查找重复项会变得更快，因为重复项太多了，而 EXISTS只需要查找一个。但是考虑一个 完全不同的方法，如果你能负担得起的话(即并发访问允许它) : 将仅存的几行写到一个新表中。这也将消除进程中的表(和索引)膨胀。参见:

小开

Postgreql 有 windows 函数，你可以使用 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
小开

这是另一个解决方案，对我有效。

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

如果 id 是重复的，这些解决方案都不起作用，这就是我的用例，那么解决方案很简单:

myTable:
id  name
0   value
0   value
0   value
1   value1
1   value1


create dedupMyTable as select distinct * from myTable;
delete from myTable;
insert into myTable select * from dedupMyTable;


select * from myTable;
id  name
0   value
1   value1

除非没有 PK 约束或者不支持 Hive/data lake 表，否则不应该在表中重复 id

最好在加载数据时注意，以避免在 ID 上出错

小开 
DELETE FROM tracking_order
WHERE
mvd_id IN (---column you need to remove duplicate
SELECT
mvd_id
FROM (
SELECT
mvd_id,thoi_gian_gui,
ROW_NUMBER() OVER (
PARTITION BY mvd_id
ORDER BY thoi_gian_gui desc) AS row_num
FROM
tracking_order
) s_alias
WHERE row_num > 1)
AND thoi_gian_gui in ( --column you used to compare to delete duplicates, eg last update time
SELECT
thoi_gian_gui
FROM (
SELECT
thoi_gian_gui,
ROW_NUMBER() OVER (
PARTITION BY mvd_id
ORDER BY thoi_gian_gui desc) AS row_num
FROM
tracking_order
) s_alias
WHERE row_num > 1)

我的代码，我删除所有重复的7800445行，并保持每行只有1个副本7分28秒。 在此输入图像描述


提交答案