`
骑猪逛街666
  • 浏览: 141800 次
  • 性别: Icon_minigender_2
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

PostgreSQL 数据去重大法

阅读更多
阅读原文请点击: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 数据迁移到达梦数据库操作是指将 PostgreSQL 数据库中的数据迁移到达梦数据库中的过程。该过程需要使用 Navicat for PostgreSql 工具生成 SQL 脚本,然后使用达梦...

    PostgreSQL数据分页技术概述.pptx

    本文总结了 PostgreSQL 数据分页技术的概述,介绍了在大数据量时如何高效地使用模糊查询和数据分页浏览,并根据后台设计降低用户界面的使用复杂程度。 一、数据分页浏览技术 数据分页浏览技术是开发中基本都会用到...

    postgresql数据快速倒入redis

    在IT行业中,数据库管理和数据迁移是一项关键任务,尤其是在大数据处理和高性能应用中。"postgresql数据快速倒入redis"这个主题涉及到两个流行的开源数据库系统——PostgreSQL和Redis,以及它们之间的数据迁移。...

    基于PostgreSQL数据库构建数据中台.pdf

    - **数据去重与验证**:确保数据的一致性和准确性,避免重复或错误信息。 - **ETL过程**:抽取(Extract)、转换(Transform)和加载(Load)数据,适配多种查询引擎,如Hive、Spark和Flink。 - **数据校验**:...

    QL SERVER 2000到POSTGRESQL数据迁移

    【SQL SERVER 2000到POSTGRESQL数据迁移】是一项技术性的工作,涉及两个不同数据库系统的数据转换。在这个过程中,我们需要确保数据的完整性和一致性,同时还要处理两种数据库之间结构差异的问题。以下是对迁移过程...

    postgresql数据类型转换.pdf

    总结来说,PostgreSQL中数据类型的转换是一个功能丰富且灵活的领域,掌握了这些知识能够为数据库应用的开发和维护提供极大的帮助。对于数据库开发者和管理员而言,合理运用这些转换函数和操作符,将能够有效地解决在...

    QGIS+postgresql操作

    PostgreSQL是一个对象关系数据库系统,支持空间数据类型和空间索引,非常适合用来存储和管理大量的空间数据。当两者结合使用时,可以在QGIS中方便地查询PostgreSQL空间数据库中的数据,并进行数据的导入导出操作。 ...

    JDBC 连接到 PostgreSQL 数据库获取数据简单例子

    本示例将详细介绍如何使用JDBC连接到PostgreSQL数据库并执行简单的数据获取操作。PostgreSQL是一种开源的对象关系型数据库管理系统,因其强大的功能和稳定性而广受欢迎。 首先,确保已安装PostgreSQL数据库并在系统...

    PostgresQL8.3中文文档

    1. **并行查询**: PostgreSQL 8.3开始支持并行查询,尤其是对于聚合和扫描操作,可以显著提高大数据集的处理速度。 2. **分区表**: 分区表是将大表逻辑上分成多个部分,每个部分称为一个分区。这使得管理和查询...

    postgresql+postgis安装和空间数据的导入

    PostgreSQL与PostGIS的安装和空间数据导入 PostgreSQL是一个功能强大且开源的关系数据库管理系统,而PostGIS则是一个基于PostgreSQL的空间数据库扩展,提供了对空间数据的支持。在本文中,我们将介绍如何安装...

    cpp-postgresqlunit实现一种支持SIUnits的PostgreSQL数据类型

    "cpp-postgresql-unit"项目正是为了满足这种需求,它提供了一个用于PostgreSQL数据库的自定义数据类型,该类型支持国际单位制(SI Units)。让我们深入探讨这个项目及其背后的实现原理。 首先,`cpp-postgresql-...

    导出数据字典工具 支持mysql,sqlserver,postgresql

    导出数据字典工具 支持mysql,sqlserver,postgresql

    PostgreSQL数据库对象名大小写敏感的解决方法

    除了数据库对象名,PostgreSQL对数据本身也是大小写敏感的。这意味着在查询时,必须确保字符串比较符合预期。例如,如果表`TUser`中有一个字段`Name`,其中包含值“TonyTang”,直接使用`LIKE`操作符进行大小写不...

    MySQL数据迁移到postgresql必备手册.pdf

    - **性能优化** 对于大规模数据迁移,可能需要考虑分批迁移以优化性能,避免一次性迁移导致的系统压力过大。 4. **后续学习资源** 网站美河学习在线(www.eimhe.com)提供了更多的数据库资源,对于进一步了解和...

    使用PostgreSQL数据进行开发.zip

    使用PostgreSQL数据进行开发

    PostgreSQL存储二进制数据.htm

    PostgreSQL存储二进制数据.htm

    利用Python查询postgresql数据库数据

    利用Python查询postgresql数据库数据

    PostgreSQL_8.4安装及空间数据导入_安装操作手册

    PostgreSQL 8.4是...综上所述,本操作手册是PostgreSQL 8.4数据库及其空间数据扩展PostGIS 1.5的安装及数据导入指南,为数据库管理员提供了一个清晰的步骤说明,以便于他们完成数据库的部署和空间数据的集成。

Global site tag (gtag.js) - Google Analytics