實例講解資料庫的數據去重

来源:https://www.cnblogs.com/huaweiyun/archive/2023/11/07/17814580.html
-Advertisement-
Play Games

本文分享自華為雲社區《GaussDB資料庫SQL系列-數據去重》,作者: Gauss松鼠會小助手2 。 一、前言 數據去重在資料庫中是比較常見的操作。複雜的業務場景、多業務線的數據來源等等,都會帶來重覆數據的存儲。本文以GaussDB資料庫為實驗平臺,將為大家詳細講解如何去重。 二、數據去重應用場景 ...


本文分享自華為雲社區《GaussDB資料庫SQL系列-數據去重》,作者: Gauss松鼠會小助手2 。

一、前言

數據去重在資料庫中是比較常見的操作。複雜的業務場景、多業務線的數據來源等等,都會帶來重覆數據的存儲。本文以GaussDB資料庫為實驗平臺,將為大家詳細講解如何去重。

二、數據去重應用場景

• 資料庫管理(含備份):在資料庫中進行數據去重可以避免數據重覆存儲、備份,提高資料庫的存儲效率、降低備份的存儲成本。

• 數據集成:在數據集成的過程中,需要合併多個數據源的數據,去重可以避免重覆的數據對合併結果的影響。

• 數據分析挖掘):在進行數據分析或數據挖掘時,去重可以避免重覆的數據對分析或挖掘結果的干擾,提高分析的準確性。

• 電商平臺:在電商平臺上進行商品去重可以避免重覆上架相同的商品,提高平臺的用戶體驗。

• 金融風控:在金融風控領域,去重可以避免重覆的數據對風控模型的影響,提高風控的準確性。

三、數據去重案例(GaussDB)

實戰業務場景 + GaussDB資料庫

1、示例場景描述

以保險行業的客戶信息除重為例,為防止坐席重覆聯繫客戶(容易造成客戶投訴),需要將客戶進行唯一身份識別。存在以下兩種情況,需要將其識別成一個人(唯一),這時候就需要進行數據去重的動作。

• 情況一:同一個客戶有不同的來源渠道:客戶即購買了壽險、又購買了產險(兩個不同的來源系統);

• 情況二:同一個客戶多次迴流:客戶在同一個渠道多次購買(續保或者購買同一險種的不同產品)。

2、定義重覆數據

通過“姓名+證件類型+證件號”將其識別為一個人,即只要這三個欄位重覆,就認為這些數據行為重覆數據。 (當然還有更複雜的場景,例如,“姓名+證件類型+證件號+手機號+車牌號”等,本次不做詳細介紹)。

3、制定去重規則

1)多選一

• 隨機:根據去重規則,隨機保留一條數據。

• 優先順序:根據去重規則 + 業務邏輯,保留優先需要的一條數據。例如優先保留“是否有房、是否有車”。

2)多合一

• 將重覆數據合併成一條數據,合併規則根據業務邏輯確定。

4、創建測試數據(GaussDB)

客戶信息欄位主要包含“姓名、性別、出生年月日、證件類型、證件號、來源、是否有車、是否有房、婚姻狀態、手機號、……”等信息。

--創建客戶信息表

CREATE TABLE customer(

name VARCHAR(20)

,sex INT

,birthday VARCHAR(10)

,ID_type INT

,ID_number VARCHAR(20)

,source VARCHAR(10)

,IS_car INT

,IS_house INT

,marital_status INT

,tel_number VARCHAR(15)

);
--插入測試數據

INSERT INTO customer VALUES('張三','1','1988-01-01','1','61010019880101****','壽險','1','1','1','');

INSERT INTO customer VALUES('張三','1','1988-01-01','1','61010019880101****','車險','1','0','1','');

INSERT INTO customer VALUES('張三','1','1988-01-01','1','61010019880101****','','','','','186****0701');

INSERT INTO customer VALUES('李四','1','1989-01-02','1','61010019890102****','壽險','1','1','1','');

INSERT INTO customer VALUES('李四','1','1989-01-02','1','61010019890102****','車險','1','0','1','');

INSERT INTO customer VALUES('李四','1','1989-01-02','1','61010019890102****','','','','','186****0702');
--查看結果

SELECT * FROM customer;

Tip: 部分為INT類型的欄位值取字典表的值,此處省。

5、編寫去重方法(GaussDB)

以下示例中不包含過多的數據清洗、數據脫敏、業務邏輯等的處理,這些步驟均建議進行“前置”處理。本次示例重點描述去重的過程。

1隨機保留:根據業務邏輯,隨機保留一條記錄。

SELECT *

FROM (SELECT *

,ROW_NUMBER() OVER (PARTITION BY name,id_type,id_number ) as row_num

FROM customer)

WHERE row_num = 1;

說明

• ROW_NUMBER(): 從第一行開始,依次為每一行分配一個唯一且連續的編號。

• PARTITION BY col1[, col2...]: 指定分區的列,例如去重的鍵“姓名、證件類型、證件號碼”。

• WHERE row_num = 1:取ROW_NUMBER()生成的編號1。

2)優先保留:根據業務邏輯,優先保留有手機號的一條記錄,如果有多條記錄含有手機號或有沒有手機號,則在此基礎上隨機保留。

--保留含有手機號的記錄行

SELECT t.*

FROM (SELECT *

,ROW_NUMBER() OVER (PARTITION BY name,id_type,id_number ORDER BY tel_number ASC) as row_num

FROM customer) t

WHERE t.row_num = 1;

說明

• ROW_NUMBER(): 從第一行開始,依次為每一行分配一個唯一且連續的號碼。

• PARTITION BY col1[, col2...]: 指定分區的列,例如去重的鍵“姓名、證件類型、證件號碼”。

• ORDER BY col [asc|desc]: 指定排序的列。升序( ASC )排列指只保留第一行,而降序排列( DESC )則指保留最後一行。

• WHERE row_num = 1:取ROW_NUMBER()生成的編號1。

3)合併保留:根據業務邏輯,合併完整性高、準確性高的欄位信息。例如優先將含有手機號的記錄行進行補齊,需要補齊的欄位有“是否有車、是否有房、婚姻狀況”,其取值是來源為“車險”的對應記錄。

--合併保留

SELECT t1.name

,t1.sex

            ,t1.birthday

            ,t1.id_type

            ,t1.id_number

            ,t1.source

            ,t2.is_car

            ,t2.is_house

            ,t2.marital_status

            ,t1.tel_number

FROM

(SELECT t.*

FROM (SELECT *

,ROW_NUMBER() OVER (PARTITION BY name,id_type,id_number ORDER BY tel_number ASC) as row_num

FROM customer) t

WHERE t.row_num = 1) t1

LEFT JOIN

(SELECT *

FROM customer

WHERE source ='車險' and is_car IS NOT NULL AND is_house IS NOT NULL AND marital_status IS NOT NULL) t2

ON t1.name =t2.name

and t1.id_type=t2.id_type

and t1.id_number=t2.id_number

說明

t1 表是優先保留含有手機的記錄行(去重),並作為主表,t2表是需要補齊的欄位來源表。兩張表通過“姓名+證件類型+證件號碼”進行關聯,然後合併需要的信息。

6、附:全欄位去重

在資料庫應用時,例如,重覆誤操作、數據翻倍等原因造成的全欄位重覆,此時也要進行去重。 那除了前面介紹的3種方式外,大家還可以使用關鍵字DISTINCT、UNION 進行去重,但需要註意其數據量及SQL 性能。 (大家自行測試)

1 DISTINCT (假設全部有如下三個欄位)

2 UNION(假設全部有如下三個欄位)

四、數據去重效率提升建議

最好的去重其實是在數據源頭就進行“攔截”。當然了, 因業務流轉也不可能完全避免,但是我們可以提高去重的效率:

• 選擇合適的去重演算法

根據數據集的特點和規模,選擇適合的去重演算法,可以大大提高去重效率。

• 優化數據存儲結構

採用合適的數據存儲結構,如哈希表、B+樹等,可以加快數據的查找和比較速度,從而提高去重效率。

• 並行化處理

採用並行化處理的方式,將數據集分成多個子集,分別進行去重處理,最後合併結果,可以大大加快去重速度。

• 使用索引加速查找

對數據集中的關鍵欄位建立索引,可以加速查找和比較速度,從而提高去重效率。

• 前置過濾

採用前置過濾的方式,先對數據集進行一些簡單的篩選和處理,如去除空值、去除無效字元等,可以減少比較次數,從而提高去重效率。

• 去重結果緩存(臨時表)

對去重結果進行緩存,可以避免重覆計算,從而提高去重效率。

• 不建議重寫(備份)

涉及一些分區表,等不建議直接將去重後的結果集重寫到生產表,創建臨時換成,或進行備份後操作。

五、總結

數據去重涉及到的面非常廣,包括重覆數據的發現、去重規則的定義、去重的方法與效率、去重的困難與挑戰等等。但是,去重原則只有一個,那就是以業務為導向。根據業務需求去定義重覆數據、制定去重規則和方案。在GaussDB資料庫的使用過程,我們同樣會遇到去重的場景。本文從應用背景、案例、去重方案等方面給大家做了介紹,歡迎測試、交流。

點擊關註,第一時間瞭解華為雲新鮮技術~

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 對於手工計算來說,積分計算是非常困難的,對於一些簡單的函數,我們可以直接通過已知的積分公式來求解,但在更多的情況下,原函數並沒有簡單的表達式,因此確定積分的反函數變得非常困難。 另外,相對於微分運算來說,積分運算則具有更多的多樣性,包括不同的積分方法(如換元積分法、分部積分法等)和積分技巧,需要根據 ...
  • 一、概念 AOP面向切麵編程,一種編程範式 二、作用 在不改動原始設計(原代碼不改動)的基礎上為方法進行功能增強(即增加功能) 三、核心概念 1、代理(Proxy):SpringAOP的核心本質是採用代理模式實現的 2、連接點(JoinPoint):在SpringAOP中,理解為任意方法的執行 3、 ...
  • Go 方法集合與選擇receiver類型 目錄Go 方法集合與選擇receiver類型一、receiver 參數類型對 Go 方法的影響二、選擇 receiver 參數類型原則2.1 選擇 receiver 參數類型的第一個原則2.2 選擇 receiver 參數類型的第二個原則三、方法集合(Met ...
  • 線程池(重點) 線程池:三大方法、七大參數、四種拒絕策略 池化技術 程式的運行,本質:占用系統的資源!優化資源的使用!-> 池化技術(線程池、連接池、對象池......);創建和銷毀十分消耗資源 池化技術:事先準備好一些資源,有人要用就拿,拿完用完還給我。 線程池的好處: 1、降低資源消耗 2、提高 ...
  • 我們在類中通常會有一個屬性為 IsDel來表示軟刪除或也稱邏輯刪除,這個屬性會導致我們在進行查詢操作時,每一次都要 .where(s=>s.IsDel==false) 非常的麻煩。在使用efCore時可以通過配置查詢篩選器來很好的解決這個問題。 public class SysUser { publ ...
  • 搜索查找指令 find 指令 find指令將從指定目錄向下遞歸的遍歷其各個子目錄,將滿足條件的文件或者目錄顯示在終端。 基本語法 find [搜索範圍(指定目錄)] [選項] 選項說明 選項 功能 -name<查詢方式> 按照指定的文件名查找模式查找文件 -user<用戶名> 查找屬於指定用戶名所有 ...
  • rsyslog 介紹 日誌(Log)是記錄和存儲電腦、軟體、應用或其他系統的操作和事件的文件或數據流。它們可以為系統管理員、開發人員和最終用戶提供詳細的背景信息,以幫助他們瞭解和診斷系統的行為。 rsyslog 是一個開源的日誌處理工具,一般用在類Unix系統上,是syslogd 的擴展。它提供了 ...
  • 包括線程概念簡介;線程創建函數pthread_create以及退出、回收等;線程同步互斥鎖pthread_mutex_t,讀寫鎖pthread_rwlock_t,條件變數pthread_cond_t以及信號量semaphore ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...