GP查询并删除重复数据问题

在数据库中做增删查改时，难免会因为误操作导致数据库中存在一些重复数据，那么如何定位这些重复数据并且删除呢？本文将介绍在Greenplum数据库中如何实现查询并删除重复数据的方法。

- PostgreSQL与Greenplum的关系
- GP查询重复数据
- - 1. row_number()函数
  - 2. having函数
- PostgreSQL删除重复数据
- - - ctid是什么？
    - 流程
    - 示例代码
- GP删除重复数据
- - - 发生了报错:
    - 解决方案：
    - 代码:
- GP判断重复数据
- 总结

PostgreSQL与Greenplum的关系

众所周知，Greenplum是通过postgresql的底层实现的，所以postgresql中90%的语法都可以在greenplum中实现，但是GP数据库的特点也是其最津津乐道的优点是其为分布式并行数据库。大家应该都听过很多关于分布式的优点、好处等，不过作为初学者，这个概念还是过于抽象，乍一听感觉没什么，使用起来也只是在建表的时候更注重distributed by key而已，但实际上分布式的表结构就注定了GP要实现某些功能就注定要与Postgre背道而驰，尤其是在表结构本身的问题上，这个现象会在下文中得到具体的展示。

GP查询重复数据

GP查询重复数据方面和Postgre的底层逻辑是一致的，且有许多种方法，主要思想即为利用每行数据的唯一标识（可以是一列也可以是多列）进行查询并计数，数量大于1的数据即为重复数据。具体实现方法这里仅作简单地介绍。

1. row_number()函数

利用row_number() over(partition by col1, col2) as rn语句可以轻松对数据进行分类聚合后计数，再筛选rn > 1的数据即为重复数据（关于此函数的介绍详情请看本人PL/pgSQL自学之路系列文章）。

2. having函数

此方法有点即为与postgre查询重复数据方法高度一致，也为后续删除重复数据奠定一定基础，缺点是对于多列作为数据唯一标识的情况下语句稍显复杂，下面将分别展示单列、多列作为unique id时，利用having函数查重的具体语句：
1）单列作为unique id时

select "POSITION_NAME","CMEMO","SUPERINTENDENT_MAN_NAME","SUPERINTENDENT_MAN_NAME" 
from  "DCS_RISK"
where "ID" in (select  "ID" from   "DCS_RISK"  group by "ID"    having count ("ID") > 1)

2）多列作为unique id时

select "POSITION_NAME","CMEMO","SUPERINTENDENT_MAN_NAME","SUPERINTENDENT_MAN_NAME" 
from  "DCS_RISK"
where "ID" in (select  "ID" from   "DCS_RISK"  group by "ID"    having count ("ID") > 1)

PostgreSQL删除重复数据

在介绍GP如何删除重复数据之前，首先我们来看PostgreSQL作为GP的大哥，是如何实现这一功能的。
原理：利用ctid区分重复数据。

ctid是什么？

在展示具体代码之前，我先简单介绍下ctid是什么，以便初学者理解为何可以通过ctid实现这一功能。
这里引用一下postgresql的ctid中对于ctid的定义：

ctid表示数据行在它所处的表内的物理位置，ctid字段的类型是tid。尽管ctid可以快速定位数据行，每次vacuum
full之后，数据行在块内的物理位置就会移动，即ctid会发生变化，所以ctid不能作为长期的行标识符，应该使用主键来标识一个逻辑行。

根据此定义不难发现，ctid有能够起到一定的数据标识符的作用，但在某些特定的场景下，它也不是那么可靠，这为后续GP实现删除功能埋下了重要伏笔。

流程

1）查询要删除的数据——上文已介绍
2）重复的数据保留其中的一行——利用min(ctid)或者max(ctid)
3）删除其余的数据

示例代码

delete from emp where ctid not in (select min(ctid) from emp group by id);

GP删除重复数据

本文的重头戏来了，按照惯有思路，我们可以一脉相承postgre的思路和代码，这里先卖个关子，我们不妨试试看如果这么做会发生什么。

发生了报错:

在这里插入图片描述
这条报错信息里也给了错误提示和修改建议，大概意思是只用ctid无法得到唯一的数据行（实际上我已经加了一些其他的字段以保证是唯一的数据行，但gp会把这个语句识别为语法错误而非逻辑错误）。
在解决之前，不妨先思考一下为什么会出现这种情况：因为GP是分布式并行数据库！分布式意味着同一张表上的数据会由于你设置的分布键的不同而存储在不同的segment上，那么根据ctid的定义很可能某些在不同segment上的数据由于在其segment上面的相对位置相同，所以会拥有相同的ctid，这时就会出现报错中提到的问题——仅用ctid无法确保得到的是unique row。
对此我们可以进行验证：
在这里插入图片描述
同一个ctid在一张表里查出了多行完全不同的数据，验证了我们之前的猜想。

解决方案：

加入gp_segment_id字段与ctid结合共同定位数据行.

代码:

可能有更简洁的写法，此处仅提供一种可以实现的代码供参考。

delete from table_name
where (gp_segment_id, ctid)in(
select gp_segment_id, ctid from(
select gp_segment_id,ctid,*,row_number() over (partition by col1, col2, col3, col4) as rn
from  table_name
where (col1, col2, col3, col4) in(select  col1, col2, col3, col4from phm.phmot_crm_ordergroup by col1, col2, col3, col4having count (*) > 1)
order by gp_segment_id, ctid
) as df1
where rn > 1
order by gp_segment_id
);

GP判断重复数据

当然解决问题最好从问题的源头进行解决，避免在同一张表中插入重复数据可以减少我们需要删除重复数据的需求，在gp乃至postgresql中用如下方式可避免重复插入数据：

--先给表创建一个唯一性约束
alter table 表名 add constraint 约束名 unique(goods_id, user_id, enterprise_id);INSERT INTO 表名 ( sku, goods_id, user_id, enterprise_id, create_date, create_user_id )
VALUES( ‘222’, 14851, 1154, 1263,‘2020-04-16 20:26:32’, 1153 )
ON CONFLICT ON CONSTRAINT 约束名 DO NOTHING;