postgresql如何找到表中重復(fù)數(shù)據(jù)的行并刪除

更新時間：2023年05月05日 16:49:18 作者：大妮喲

這篇文章主要介紹了postgresql如何找到表中重復(fù)數(shù)據(jù)的行并刪除問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

postgresql找到表中重復(fù)數(shù)據(jù)的行并刪除

創(chuàng)建測試表并插入數(shù)據(jù)

create table aaa(id bigserial,col1 varchar(255));

insert into aaa values(1,'b'),(2,'a'),(3,'b'),(4,'c');

select * from aaa;

找到重復(fù)行并刪除

方法1：ctid表示數(shù)據(jù)行在它所處的表內(nèi)的物理位置，ctid由兩個數(shù)字組成，第一個數(shù)字表示物理塊號，第二個數(shù)字表示在物理塊中的行號。

select * from aaa where ctid not in(select max(ctid) from aaa group by col1);

刪除重復(fù)行

delete from aaa where ctid not in(select max(ctid) from aaa group by col1);

方法2：利用exists

找到重復(fù)行

select * from aaa t1 where  exists (select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id )----exists后的意思是同一列相等，但是自增id不相等且id小的那一個

刪除重復(fù)行

delete from aaa t1 where  exists (select 1 from aaa t2 where t1.col1=t2.col1 and t1.id<t2.id )

postgresql常用的刪除重復(fù)數(shù)據(jù)方法

最高效方法

測試環(huán)境驗證，6600萬行大表，刪除2200萬重復(fù)數(shù)據(jù)僅需3分鐘

delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));

PG中三種刪除重復(fù)數(shù)據(jù)方法

首先創(chuàng)建一張基礎(chǔ)表，并插入一定量的重復(fù)數(shù)據(jù)。

create table deltest(id int, name varchar(255));
create table deltest_bk (like deltest);
insert into deltest select generate_series(1, 10000), 'ZhangSan';
insert into deltest select generate_series(1, 10000), 'ZhangSan';
insert into deltest_bk select * from deltest;

1. 常規(guī)刪除方法

最容易想到的方法就是判斷數(shù)據(jù)是否重復(fù)，對于重復(fù)的數(shù)據(jù)只保留ctid最?。ɑ蜃畲螅┑臄?shù)據(jù)，刪除其他的。

explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id);
-------------------------------------------------------------------------------------------
? ? Delete on deltest a ?(cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1)
? ? ? ?-> ?Seq Scan on deltest a ?(cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1)
? ? ? ? ?Filter: (ctid <> (SubPlan 1))
? ? ? ? ?Rows Removed by Filter: 10000
? ? ? ? ?SubPlan 1
? ? ? ? ? ?-> ?Aggregate ?(cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000)
? ? ? ? ? ? ? ? ?-> ?Seq Scan on deltest t ?(cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20000)
? ? ? ? ? ? ? ? ? ? ? ?Filter: (a.id = id)
? ? ? ? ? ? ? ? ? ? ? ?Rows Removed by Filter: 19998
Total runtime: 67758.931 ms
select count(*) from deltest;
count
-------
10000

可以看到，id相同的數(shù)據(jù)，保留ctid最小的，其他的刪除。相當(dāng)于把deltest表中的數(shù)據(jù)刪掉一半，耗時達到67s多。相當(dāng)慢。

2. group by刪除方法

group by方法通過分組找到ctid最小的數(shù)據(jù)，然后刪除其他數(shù)據(jù)。

explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id);
-------------------------------------------------------------------------------------------
? ? Delete on deltest a ?(cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1)
? ? ? ?-> ?Seq Scan on deltest a ?(cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1)
? ? ? ? ?Filter: (NOT (SubPlan 1))
? ? ? ? ?Rows Removed by Filter: 10000
? ? ? ? ?SubPlan 1
? ? ? ? ? ?-> ?Materialize ?(cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000)
? ? ? ? ? ? ? ? ?-> ?HashAggregate ?(cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1)
? ? ? ? ? ? ? ? ? ? ? ?-> ?Seq Scan on deltest ?(cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20000 loops=1)
? ? ?Total runtime: 30942.819 ms
select count(*) from deltest;
count
-------
10000

可以看到同樣是刪除一半的數(shù)據(jù)，使用group by的方式，時間節(jié)省了一半。但仍含需要30s，下面試一下第三種刪除操作。

3. 高效刪除方法

explain analyze delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));
-----------------------------------------------------------------------------------------
? ? Delete on deltest a ?(cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1)
? ? InitPlan 1 (returns 0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0)?>SubqueryScanont(cost=204.95..250.73rows=509width=6)(actualtime=29.446..47.867rows=10000loops=1)Filter:(t.rownumber>1)RowsRemovedbyFilter:10000?>WindowAgg(cost=204.95..231.66rows=1526width=10)(actualtime=29.436..44.790rows=20000loops=1)?>Sort(cost=204.95..208.77rows=1526width=10)(actualtime=12.466..13.754rows=20000loops=1)SortKey:deltest.idSortMethod:quicksortMemory:1294kB?>SeqScanondeltest(cost=0.00..124.26rows=1526width=10)(actualtime=0.021..5.110rows=20000loops=1)?>TidScanondeltesta(cost=0.01..20.11rows=10width=6)(actualtime=82.983..88.751rows=10000loops=1)TIDCond:(ctid=ANY(0))
? ? Total runtime: 98.912 ms
select count(*) from deltest;
count
-------
10000

可以看到，居然只要98ms