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

PostgreSQL與Greenplum的關(guān)系

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

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

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

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

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

1. row_number()函數(shù)

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

2. having函數(shù)

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

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

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

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

ctid是什么？

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

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

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

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

流程

1）查詢要刪除的數(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的思路和代碼，這里先賣個關(guān)子，我們不妨試試看如果這么做會發(fā)生什么。

發(fā)生了報錯：

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

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

對此我們可以進行驗證：

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

解決方案：

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

代碼：

可能有更簡潔的寫法，此處僅提供一種可以實現(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)然解決問題最好從問題的源頭進行解決，避免在同一張表中插入重復(fù)數(shù)據(jù)可以減少我們需要刪除重復(fù)數(shù)據(jù)的需求，在gp乃至postgresql中用如下方式可避免重復(fù)插入數(shù)據(jù)：

--先給表創(chuàng)建一個唯一性約束
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ù)庫實現(xiàn)查詢和刪除重復(fù)數(shù)據(jù)的幾種方案以及原理，相信讀者們通過此案例可以對分布式數(shù)據(jù)庫以及底層數(shù)據(jù)庫和衍生的數(shù)據(jù)庫的異同點有了初步的感知。

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

最新評論