阅读原文请点击:
http://click.aliyun.com/m/22549/
摘要: 标签 PostgreSQL , 去重 , 单列去重 , 多列去重 , 行去重 , 多列混合去重 , varidict 参数 , 数组排序 , 数组元素重排 背景 去重的需求比较常见,去重也可以衍生出很多变种。
标签
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;
《PostgreSQL 10.0 preview 优化器改进 - 不完整索引支持复合排序》
多列去重
测试数据
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.c2
Group Key: test1_1.c1, test1_1.c2
Buffers: shared hit=1004800
-> Index Only Scan using idx_test1 on public.test1 test1_1 (cost=0.42..32070.36 rows=1000000 width=12) (actual time=0.027..587.506 rows=1000000 loops=1)
Output: test1_1.c1, test1_1.c2, test1_1.id
Heap Fetches: 1000000
Buffers: shared hit=1004800
Planning time: 0.211 ms
Execution time: 1641.679 ms
(18 rows)
方法2,
阅读原文请点击:
http://click.aliyun.com/m/22549/
分享到:
相关推荐
PostgreSQL 数据迁移到达梦数据库操作 PostgreSQL 数据迁移到达梦数据库操作是指将 PostgreSQL 数据库中的数据迁移到达梦数据库中的过程。该过程需要使用 Navicat for PostgreSql 工具生成 SQL 脚本,然后使用达梦...
本文总结了 PostgreSQL 数据分页技术的概述,介绍了在大数据量时如何高效地使用模糊查询和数据分页浏览,并根据后台设计降低用户界面的使用复杂程度。 一、数据分页浏览技术 数据分页浏览技术是开发中基本都会用到...
在IT行业中,数据库管理和数据迁移是一项关键任务,尤其是在大数据处理和高性能应用中。"postgresql数据快速倒入redis"这个主题涉及到两个流行的开源数据库系统——PostgreSQL和Redis,以及它们之间的数据迁移。...
- **数据去重与验证**:确保数据的一致性和准确性,避免重复或错误信息。 - **ETL过程**:抽取(Extract)、转换(Transform)和加载(Load)数据,适配多种查询引擎,如Hive、Spark和Flink。 - **数据校验**:...
【SQL SERVER 2000到POSTGRESQL数据迁移】是一项技术性的工作,涉及两个不同数据库系统的数据转换。在这个过程中,我们需要确保数据的完整性和一致性,同时还要处理两种数据库之间结构差异的问题。以下是对迁移过程...
总结来说,PostgreSQL中数据类型的转换是一个功能丰富且灵活的领域,掌握了这些知识能够为数据库应用的开发和维护提供极大的帮助。对于数据库开发者和管理员而言,合理运用这些转换函数和操作符,将能够有效地解决在...
PostgreSQL是一个对象关系数据库系统,支持空间数据类型和空间索引,非常适合用来存储和管理大量的空间数据。当两者结合使用时,可以在QGIS中方便地查询PostgreSQL空间数据库中的数据,并进行数据的导入导出操作。 ...
本示例将详细介绍如何使用JDBC连接到PostgreSQL数据库并执行简单的数据获取操作。PostgreSQL是一种开源的对象关系型数据库管理系统,因其强大的功能和稳定性而广受欢迎。 首先,确保已安装PostgreSQL数据库并在系统...
1. **并行查询**: PostgreSQL 8.3开始支持并行查询,尤其是对于聚合和扫描操作,可以显著提高大数据集的处理速度。 2. **分区表**: 分区表是将大表逻辑上分成多个部分,每个部分称为一个分区。这使得管理和查询...
PostgreSQL与PostGIS的安装和空间数据导入 PostgreSQL是一个功能强大且开源的关系数据库管理系统,而PostGIS则是一个基于PostgreSQL的空间数据库扩展,提供了对空间数据的支持。在本文中,我们将介绍如何安装...
"cpp-postgresql-unit"项目正是为了满足这种需求,它提供了一个用于PostgreSQL数据库的自定义数据类型,该类型支持国际单位制(SI Units)。让我们深入探讨这个项目及其背后的实现原理。 首先,`cpp-postgresql-...
导出数据字典工具 支持mysql,sqlserver,postgresql
除了数据库对象名,PostgreSQL对数据本身也是大小写敏感的。这意味着在查询时,必须确保字符串比较符合预期。例如,如果表`TUser`中有一个字段`Name`,其中包含值“TonyTang”,直接使用`LIKE`操作符进行大小写不...
- **性能优化** 对于大规模数据迁移,可能需要考虑分批迁移以优化性能,避免一次性迁移导致的系统压力过大。 4. **后续学习资源** 网站美河学习在线(www.eimhe.com)提供了更多的数据库资源,对于进一步了解和...
使用PostgreSQL数据进行开发
PostgreSQL存储二进制数据.htm
利用Python查询postgresql数据库数据
PostgreSQL 8.4是...综上所述,本操作手册是PostgreSQL 8.4数据库及其空间数据扩展PostGIS 1.5的安装及数据导入指南,为数据库管理员提供了一个清晰的步骤说明,以便于他们完成数据库的部署和空间数据的集成。