在數(shù)據(jù)庫中做增刪查改時(shí)，難免會(huì)因?yàn)檎`操作導(dǎo)致數(shù)據(jù)庫中存在一些重復(fù)數(shù)據(jù)，那么如何定位這些重復(fù)數(shù)據(jù)并且刪除呢？本文將介紹在Greenplum數(shù)據(jù)庫中如何實(shí)現(xiàn)查詢并刪除重復(fù)數(shù)據(jù)的方法。

PostgreSQL與Greenplum的關(guān)系

眾所周知，Greenplum是通過postgresql的底層實(shí)現(xiàn)的，所以postgresql中90%的語法都可以在greenplum中實(shí)現(xiàn)，但是GP數(shù)據(jù)庫的特點(diǎn)也是其最津津樂道的優(yōu)點(diǎn)是其為分布式并行數(shù)據(jù)庫。

大家應(yīng)該都聽過很多關(guān)于分布式的優(yōu)點(diǎn)、好處等，不過作為初學(xué)者，這個(gè)概念還是過于抽象，乍一聽感覺沒什么，使用起來也只是在建表的時(shí)候更注重distributed by key而已，但實(shí)際上分布式的表結(jié)構(gòu)就注定了GP要實(shí)現(xiàn)某些功能就注定要與Postgre背道而馳，尤其是在表結(jié)構(gòu)本身的問題上，這個(gè)現(xiàn)象會(huì)在下文中得到具體的展示。

GP查詢重復(fù)數(shù)據(jù)

GP查詢重復(fù)數(shù)據(jù)方面和Postgre的底層邏輯是一致的，且有許多種方法，主要思想即為利用每行數(shù)據(jù)的唯一標(biāo)識(shí)（可以是一列也可以是多列）進(jìn)行查詢并計(jì)數(shù)，數(shù)量大于1的數(shù)據(jù)即為重復(fù)數(shù)據(jù)。

具體實(shí)現(xiàn)方法這里僅作簡單地介紹。

1. row_number()函數(shù)

利用row_number() over(partition by col1, col2) as rn語句可以輕松對數(shù)據(jù)進(jìn)行分類聚合后計(jì)數(shù)，再篩選rn > 1的數(shù)據(jù)即為重復(fù)數(shù)據(jù)（關(guān)于此函數(shù)的介紹詳情請看本人PL/pgSQL自學(xué)之路系列文章）。

2. having函數(shù)

此方法有點(diǎn)即為與postgre查詢重復(fù)數(shù)據(jù)方法高度一致，也為后續(xù)刪除重復(fù)數(shù)據(jù)奠定一定基礎(chǔ)，缺點(diǎn)是對于多列作為數(shù)據(jù)唯一標(biāo)識(shí)的情況下語句稍顯復(fù)雜，下面將分別展示單列、多列作為unique id時(shí)，利用having函數(shù)查重的具體語句：

1）單列作為unique id時(shí)

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時(shí)

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刪除重復(fù)數(shù)據(jù)

在介紹GP如何刪除重復(fù)數(shù)據(jù)之前，首先我們來看PostgreSQL作為GP的大哥，是如何實(shí)現(xiàn)這一功能的。

原理：利用ctid區(qū)分重復(fù)數(shù)據(jù)。

ctid是什么？

在展示具體代碼之前，我先簡單介紹下ctid是什么，以便初學(xué)者理解為何可以通過ctid實(shí)現(xiàn)這一功能。

這里引用一下postgresql的ctid中對于ctid的定義：

• ctid表示數(shù)據(jù)行在它所處的表內(nèi)的物理位置，ctid字段的類型是tid。盡管ctid可以快速定位數(shù)據(jù)行，每次vacuum
• full之后，數(shù)據(jù)行在塊內(nèi)的物理位置就會(huì)移動(dòng)，即ctid會(huì)發(fā)生變化，所以ctid不能作為長期的行標(biāo)識(shí)符，應(yīng)該使用主鍵來標(biāo)識(shí)一個(gè)邏輯行。

根據(jù)此定義不難發(fā)現(xiàn)，ctid有能夠起到一定的數(shù)據(jù)標(biāo)識(shí)符的作用，但在某些特定的場景下，它也不是那么可靠，這為后續(xù)GP實(shí)現(xiàn)刪除功能埋下了重要伏筆。

流程

1）查詢要?jiǎng)h除的數(shù)據(jù)——上文已介紹

2）重復(fù)的數(shù)據(jù)保留其中的一行——利用min(ctid)或者max(ctid)

3）刪除其余的數(shù)據(jù)

示例代碼

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

GP刪除重復(fù)數(shù)據(jù)

本文的重頭戲來了，按照慣有思路，我們可以一脈相承postgre的思路和代碼，這里先賣個(gè)關(guān)子，我們不妨試試看如果這么做會(huì)發(fā)生什么。

發(fā)生了報(bào)錯(cuò)：

這條報(bào)錯(cuò)信息里也給了錯(cuò)誤提示和修改建議，大概意思是只用ctid無法得到唯一的數(shù)據(jù)行（實(shí)際上我已經(jīng)加了一些其他的字段以保證是唯一的數(shù)據(jù)行，但gp會(huì)把這個(gè)語句識(shí)別為語法錯(cuò)誤而非邏輯錯(cuò)誤）。

在解決之前，不妨先思考一下為什么會(huì)出現(xiàn)這種情況：因?yàn)镚P是分布式并行數(shù)據(jù)庫！分布式意味著同一張表上的數(shù)據(jù)會(huì)由于你設(shè)置的分布鍵的不同而存儲(chǔ)在不同的segment上，那么根據(jù)ctid的定義很可能某些在不同segment上的數(shù)據(jù)由于在其segment上面的相對位置相同，所以會(huì)擁有相同的ctid，這時(shí)就會(huì)出現(xiàn)報(bào)錯(cuò)中提到的問題——僅用ctid無法確保得到的是unique row。

對此我們可以進(jìn)行驗(yàn)證：

同一個(gè)ctid在一張表里查出了多行完全不同的數(shù)據(jù)，驗(yàn)證了我們之前的猜想。

解決方案：

加入gp_segment_id字段與ctid結(jié)合共同定位數(shù)據(jù)行.

代碼：

可能有更簡潔的寫法，此處僅提供一種可以實(shí)現(xiàn)的代碼供參考。

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, col4
       from phm.phmot_crm_order
       group by col1, col2, col3, col4
       having count (*) > 1)
order by gp_segment_id, ctid
) as df1
where rn > 1
order by gp_segment_id
);

GP判斷重復(fù)數(shù)據(jù)

當(dāng)然解決問題最好從問題的源頭進(jìn)行解決，避免在同一張表中插入重復(fù)數(shù)據(jù)可以減少我們需要?jiǎng)h除重復(fù)數(shù)據(jù)的需求，在gp乃至postgresql中用如下方式可避免重復(fù)插入數(shù)據(jù)：

--先給表創(chuàng)建一個(gè)唯一性約束
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;

總結(jié)

本文介紹了GP數(shù)據(jù)庫實(shí)現(xiàn)查詢和刪除重復(fù)數(shù)據(jù)的幾種方案以及原理，相信讀者們通過此案例可以對分布式數(shù)據(jù)庫以及底層數(shù)據(jù)庫和衍生的數(shù)據(jù)庫的異同點(diǎn)有了初步的感知。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論