postgresal去重_PostgreSQL 数据去重大法-阿里云开发者社区

标签

PostgreSQL , 去重 , 单列去重 , 多列去重 , 行去重 , 多列混合去重 , varidict 参数 , 数组排序 , 数组元素重排

背景

去重的需求比较常见，去重也可以衍生出很多变种。例如

1. 单列去重，很好理解，就是按某列去除重复记录。保留规则(例如保留最新的，保留最旧的，或者保留某个其他字段最大的)。

2. 多列去重，按多列，去除重复记录。保留规则(例如保留最新的，保留最旧的，或者保留某个其他字段最大的)。

3. 行去重，按行，去除重复记录。保留规则(例如保留最新的，保留最旧的，或者保留某个其他字段最大的)。

4. 多列混合去重，按多列混合模式(ROW1: col1=1 , col2=2；ROW2: col1=2, col2=1；这种混合重复的去重)，去除重复记录。保留规则(例如保留最新的，保留最旧的，或者保留某个其他字段最大的)。

下面依次举例，使用不同的方法去重，用户可以自由选择效率最佳的。

单列去重

测试数据

create table test1(id int primary key, c1 int, c2 timestamp);

insert into test1 select generate_series(1,1000000), random()*1000, clock_timestamp();

create index idx_test1 on test1(c1,id);

-- 这个索引可以起到加速效果。如果没有这个索引，以下三种方法，第二种效率最高，其次是第三种。

需求：去除c1重复的行，保留id最大的。

方法1，使用聚合，not in

postgres=# explain delete from test1 where id not in (select max(id) from test1 group by c1);

QUERY PLAN

------------------------------------------------------------------------------------------------------------------

Delete on test1 (cost=35115.63..53023.01 rows=500055 width=6)

-> Seq Scan on test1 (cost=35115.63..53023.01 rows=500055 width=6)

Filter: (NOT (hashed SubPlan 1))

SubPlan 1

-> GroupAggregate (cost=0.42..35113.13 rows=1001 width=8)

Group Key: test1_1.c1

-> Index Only Scan using idx_test1 on test1 test1_1 (cost=0.42..30102.57 rows=1000110 width=8)

(7 rows)

Time: 0.564 ms

postgres=# delete from test1 where id not in (select max(id) from test1 group by c1);

DELETE 998999

Time: 1126.504 ms (00:01.127)

方法2，使用窗口查询，IN

postgres=# explain select id from (select row_number() over(partition by c1 order by id) as rn, id from test1) t where t.rn<>1;

QUERY PLAN

--------------------------------------------------------------------------------------------------

Subquery Scan on t (cost=0.42..60075.54 rows=995109 width=4)

Filter: (t.rn <> 1)

-> WindowAgg (cost=0.42..47574.17 rows=1000110 width=16)

-> Index Only Scan using idx_test1 on test1 (cost=0.42..30072.24 rows=1000110 width=8)

(4 rows)

Time: 0.512 ms

postgres=# delete from test1 where id in (select id from (select row_number() over(partition by c1 order by id) as rn, id from test1) t where t.rn<>1);

DELETE 998999

Time: 2430.276 ms (00:02.430)

方法3，使用PLPGSQL，内部使用排序+游标。

每条记录判断一次的方法去重，只有一次排序+每条记录比对的开销。

do language plpgsql $$

declare

v_rec record;

v_c1 int;

cur1 cursor for select c1,id from test1 order by c1,id for update;

begin

for v_rec in cur1 loop

if v_rec.c1 = v_c1 then

delete from test1 where current of cur1;

end if;

v_c1 := v_rec.c1;

end loop;

end;

$$;

DO

Time: 7345.773 ms (00:07.346)

postgres=# select count(*) from test1;

count

-------

1001

(1 row)

Time: 61.672 ms

postgres=# select * from test1 limit 10;

id | c1 | c2

----+-----+----------------------------

1 | 582 | 2017-06-02 10:21:10.60918

2 | 278 | 2017-06-02 10:21:10.609331

3 | 659 | 2017-06-02 10:21:10.609338

4 | 372 | 2017-06-02 10:21:10.609341

5 | 184 | 2017-06-02 10:21:10.609343

6 | 121 | 2017-06-02 10:21:10.609345

7 | 132 | 2017-06-02 10:21:10.609347

8 | 290 | 2017-06-02 10:21:10.609348

9 | 980 | 2017-06-02 10:21:10.60935

10 | 305 | 2017-06-02 10:21:10.609352

(10 rows)

PostgreSQL 10黑科技

即使只有部分驱动列，也能使用索引排序。

例如index(c1)，可以用于order by c1,id;

多列去重

测试数据

create table test1(id int primary key, c1 int, c2 int, c3 timestamp);

insert into test1 select generate_series(1,1000000), random()*1000, random()*1000, clock_timestamp();

create index idx_test1 on test1(c1,c2,id);

-- 这个索引可以起到加速效果。

需求：去除c1,c2重复的行，保留id最大的。

方法1，

postgres=# explain (analyze,verbose,timing,costs,buffers) delete from test1 where id not in (select max(id) from test1 group by c1,c2);

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Delete on public.test1 (cost=40820.36..59690.36 rows=500000 width=6) (actual time=1634.960..1634.960 rows=0 loops=1)

Buffers: shared hit=1378788

-> Seq Scan on public.test1 (cost=40820.36..59690.36 rows=500000 width=6) (actual time=1090.956..1446.374 rows=367618 loops=1)

Output: test1.ctid

Filter: (NOT (hashed SubPlan 1))

Rows Removed by Filter: 632382

Buffers: shared hit=1011170

SubPlan 1

-> GroupAggregate (cost=0.42..40570.36 rows=100000 width=12) (actual time=0.035..842.497 rows=632382 loops=1)

Output: max(test1_1.id), test1_1.c1, test1_1