Greenplum/PostgreSQL中数据表数据去重的几种方法

GP主要用于数据仓库领域,在GP数据库中,如果由于ETL重复跑数导致数据重复的话,就需要进行去重复操作。

  • 一种方法是把某一时间段的数据全部删掉,然后重新跑数据,这样其实工作量也比较大,需要补数据,重跑ETL。

  • 另一种方法就是把重复的数据删掉就行,本文就是针对Greenplum分布式数据库如何进行去重复删除操作。

对于在Postgresql中,唯一确定一行的位置的是用ctid,可以用这个ctid作为一行的唯一标识;在Oracle中,数据表中的一行的唯一标识可以使用ROWID进行标识,这作为这一行的物理地址信息。而在GP中,要唯一的标识出一行表数据,需要使用gp_segment_id加上ctid进行标识。 gp_segment_id代表的是GP的segment的节点标识,每个子库的标识是唯一的。

因此删除重复数据的语句可以这么写:

delete from public.ods_m_automonitor_monitor_hour where gp_segment_id::varchar(100)||ctid::varchar(100) in
(select t.ctid from
(select gp_segment_id::varchar(100)||ctid::varchar(100) as ctid,mn_code,pollute_code,monitor_time,
row_number() over (partition by mn_code,monitor_time) rows_num
from public.ods_m_automonitor_monitor_hour ) t
where t.rows_num >=2);

这种语句适合所有的GP表,特别对那种没有唯一主键的数据仓库的表进行去重很有用。

先分析下,第一步:

select gp_segment_id::varchar(100)||ctid::varchar(100) as ctid,monitor_time) rows_num
from public.ods_m_automonitor_monitor_hour

查出来public.ods_m_automonitor_monitor_hour表中字段

gp_segment_id::varchar(100)||ctid::varchar(100) as ctid,monitor_time

其中mn_code,monitor_time这几个字段是这个表中能够保证唯一性的字段,也就是这几个字段的值的任一某个组合在这张表只能出现一次,只要出现2次以上,就说明数据重复了。

然后通过row_number() over (partition by mn_code,monitor_time) rows_num对这几个字段进行分组排序

通过在外层对这个排序字段rows_num进行条件判断 :where t.rows_num >=2,就能过滤出重复的表数据

然后再通过外部条件进行筛选,获取出这部分重复数据的行数据唯一标识:

delete from public.ods_m_automonitor_monitor_hour where gp_segment_id::varchar(100)||ctid::varchar(100) in 
(select t.ctid from ……)

就可以去重重复。

另外,如果表中有唯一标识行的pkid,也就是说有主键ID的话,那直接用PKID作为去重字段:

delete from public.ods_m_automonitor_monitor_hour where pkid in
(select pkid from
(select pkid,monitor_time) rows_num
from public.ods_m_automonitor_monitor_hour ) t
where t.rows_num >=2);

postgresql中去重:

delete from public.ods_m_automonitor_monitor_hour where ctid in
(select ctid from
(select ctid,monitor_time) rows_num
from public.ods_m_automonitor_monitor_hour ) t
where t.rows_num >=2);

另外:oracle中去重:

delete from public.ods_m_automonitor_monitor_hour where ROWID in (select ROWID from (select ROWID,row_number() over (partition by mn_code,monitor_time) rows_numfrom public.ods_m_automonitor_monitor_hour ) twhere t.rows_num >=2);

以上是Greenplum/PostgreSQL中数据表数据去重的几种方法全部内容。


Greenplum/PostgreSQL中数据表数据去重的几种方法
https://www.gmtgo.com/31905.html
作者
大帅
发布于
2022年12月6日
许可协议