刪除資料庫表中重覆數據的方法

来源:https://www.cnblogs.com/podolski/archive/2022/09/30/16745080.html
-Advertisement-
Play Games

一直使用Postgresql資料庫,有一張表是這樣的: DROP TABLE IF EXISTS "public"."devicedata"; CREATE TABLE "public"."devicedata" ( "Id" varchar(200) COLLATE "pg_catalog"."d ...


一直使用Postgresql資料庫,有一張表是這樣的:

DROP TABLE IF EXISTS "public"."devicedata";
CREATE TABLE "public"."devicedata" (
  "Id" varchar(200) COLLATE "pg_catalog"."default" NOT NULL,
  "DeviceId" varchar(200) COLLATE "pg_catalog"."default",
  "Timestamp" int8,
  "DataArray" float4[]
)

CREATE INDEX "timeIndex" ON "public"."devicedata" USING btree (
  "Timestamp" "pg_catalog"."int8_ops" DESC NULLS LAST,
  "DeviceId" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

ALTER TABLE "public"."devicedata" ADD CONSTRAINT "devicedata_pkey" PRIMARY KEY ("Id");

主鍵為Id,是通過程式生成的GUID,隨著數據表的越來越大(70w),即便我建立了索引,查詢效率依然不樂觀。

使用GUID作為資料庫的主鍵對分散式應用比較友好,但是不利於數據的插入,可以使用類似ABP的方法生成連續的GUID解決這個問題。

為了進行優化,計劃使用DeviceId與Timestamp作為主鍵,由於主鍵會自動建立索引,使用這兩個欄位查詢的時候,查詢效率可以有很大的提升。不過,由於資料庫的插入了很多的重覆數據,直接切換主鍵不可行,需要先剔除重覆數據。

使用group by

數據量小的時候適用。對於我這個70w的數據,查詢運行了半個多小時也無法完成。

DELETE FROM "DeviceData"
WHERE "Id"
NOT IN (
SELECT max("Id")
FROM "DeviceData_temp"
GROUP BY "DeviceId", "Timestamp"
);

使用DISTINCT

建立一張新表然後插入數據,或者使用select into語句。

SELECT DISTINCT "Timestamp", "DeviceId"
INTO "DeviceData_temp"
FROM "DeviceData";
-- 刪除原表
DROP TABLE "DeviceData";
-- 將新表重命名
ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";

不過這個問題也非常大,很明顯,未來的表,是不需要Id列的,但是DataArray也沒有了,沒有意義。

如果SELECT DISTINCT "Timestamp", "DeviceId", "DataArray",那麼可能出現"Timestamp", "DeviceId"重覆的現象。

使用ON CONFLICT

如果我們直接建立新表格,設置好新的主鍵,然後插入數據,如果重覆了就跳過不就行了?但是使用select into是不行了,重覆的數據會導致語句執行中斷。需要藉助upsert(on conflict)方法。

INSERT INTO "DeviceData_temp"
SELECT * FROM "DeviceData"
on conflict("DeviceId", "Timestamp") DO NOTHING;
-- 刪除原表
DROP TABLE "DeviceData";
-- 將新表重命名
ALTER TABLE "DeviceData_temp" RENAME TO "DeviceData";

執行不到100s就完成了,刪除了許多重覆數據。

參考文獻

https://blog.csdn.net/wendred/article/details/84704042

除非特殊說明,本作品由podolski創作,採用知識共用署名 4.0 國際許可協議進行許可。歡迎轉載,轉載請保留原文鏈接~喜歡的觀眾老爺們可以點下關註或者推薦~
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ALglib 是一個跨平臺的數值分析和數據處理庫。它支持多種編程語言(C + + 、 C # 、 Delphi)和多種操作系統(Windows 和 POSIX,包括 Linux)。 ALglib 功能包括: 數據分析(分類/回歸,統計學) 優化和非線性解法 插值和線性/非線性最小二乘擬合 線性代數( ...
  • [演算法2-數組與字元串的查找與匹配] (.NET源碼學習) 關鍵詞:1. 數組查找(演算法) 2. 字元串查找(演算法) 3. C#中的String(源碼) 4. 特性Attribute 與內在屬性(源碼) 5. 字元串的比較(底層原理) 6. C#中的StringComparsion(源碼) 7. 字 ...
  • 大家好,我是痞子衡,是正經搞技術的痞子。今天痞子衡給大家講的是一個關於Segger J-Flash在Micron Flash固定區域下載校驗失敗的故事。 痞子衡最近在支持一個 i.MXRT1170 歐美客戶,客戶項目里選用了來自 Micron 的四線 NOR Flash - MT25QL256ABA ...
  • 《上古卷軸5:天際》是Bethesda五年磨一劍的《上古卷軸》系列作品,Bethesda將提供充滿無限期待與幻想的諾德大陸。在這廣闊的地圖之上,Bethesda工作室給玩家提供了超過120個不重覆的地下迷宮,以及5個規模巨集大的城市供玩家探索,而玩家將在這個奇異自由的世界踏上史詩性的徵程,使用自己心儀 ...
  • @(文章目錄) 前言 今天和大家一起來看一下在LabVIEW中如何使用OpenCV DNN模塊實現手寫數字識別 一、OpenCV DNN模塊 1.OpenCV DNN簡介 OpenCV中的DNN(Deep Neural Network module)模塊是專門用來實現深度神經網路相關功能的模塊。Op ...
  • 一、原理總結 利用兩個寄存器R4和R5來存儲兩個數位管的顯示效果,R4是前一個數位管顯示所需,而R5是後一個數位管顯示所需,利用左移操作RLC來使之每一位被依次輸入到C中,然後將C輸入到LED中(當LED每位都有數據時,數位管才會顯示),利用停頓函數使數位管上數字停留一段時間。 二、程式分析 以下為 ...
  • Java資料庫的安裝和使用 1.資料庫的作用 一個問題:淘寶網、京東、微信抖音,都有各自的功能,那麼我們退出系統的時候,為什麼信息還在? 解決之道-文件,資料庫 為瞭解決上訴問題,使用更加利於管理數據東西-資料庫,他能更加有效地管理數據。 舉一個生活化的案例說明:如果說圖書館是保存書籍的,那麼資料庫 ...
  • LIKE操作符:通配符搜索只能用於文本欄位(字元串) 1、%通配符 1 select 2 col_name 3 from 4 table_name 5 where 6 col_name 7 like "%str" 2、_通配符 1 select 2 col_name 3 from 4 table_n ...
一周排行
    -Advertisement-
    Play Games
  • public static void GetRegistData() { string name = "huishuangzhu"; //搜索到註冊表根目錄 RegistryKey hkml = Registry.ClassesRoot; //搜索到註冊表根目錄下的XXX文件夾。 RegistryK ...
  • 用acme.sh自動部署功能變數名稱證書 安裝ACME 目前使用量最大的免費SSL證書就是Let’s Encrypt,自2018-03開始,Let’s Encrypt官方發佈上線了免費的SSL泛功能變數名稱證書,目前通過DNS方式獲取比較快,國內可以通過鵝雲的DNSPod功能變數名稱API或者貓雲功能變數名稱API自動簽發Let’ ...
  • 經常看到有群友調侃“為什麼搞Java的總在學習JVM調優?那是因為Java爛!我們.NET就不需要搞這些!”真的是這樣嗎?今天我就用一個案例來分析一下。 昨天,一位學生問了我一個問題:他建了一個預設的ASP.NET Core Web API的項目,也就是那個WeatherForecast的預設項目模 ...
  • 1、環境搭建 1.1 依賴 <!-- nacos註冊中心 註解 @EnableDiscoveryClient --> <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba- ...
  • ULID:Universally Unique Lexicographically Sortable Identifier(通用唯一詞典分類標識符) UUID:Universally Unique Identifier(通用唯一標識符) 為什麼不選擇UUID UUID 目前有 5 個版本: 版本1: ...
  • 虛基類/抽象類 抽象類:有純虛函數的類 虛繼承 通過修飾繼承方式, 如代碼2是虛繼承,被虛繼承的類稱為虛基類 虛繼承派生類的記憶體佈局方式 先是vbptr => 派生類的數據 =>基類的數據 , 對比代碼1和代碼2,發現原本基類數據在前面,派生類數據在後面,但是在虛繼承的時候 基類數據方式放到了後面, ...
  • 下麵給出 Kafka 一些重要概念,讓大家對 Kafka 有個整體的認識和感知,後面還會詳細的解析每一個概念的作用以及更深入的原理 • Producer:消息生產者,向 Kafka Broker 發消息的客戶端。 • Consumer:消息消費者,從 Kafka Broker 取消息的客戶端。 • ...
  • 前面介紹了對稱加密演算法,本文將介紹密碼學中另一類重要應用:消息摘要(Digest),什麼是消息摘要?簡單的定義是:對一份數據,進行一個單向的Hash函數,生成一個固定長度的Hash值,這個值就是這份數據的摘要,也稱為指紋。 ...
  • 弟弟最近要考試,臨時抱佛腳在網上找了一堆學習資料複習,這不剛就來找我了,說PDF上有水印,影響閱讀效果,到時候考不好就怪資料不行,氣的我差點當場想把他揍一頓! 算了,弟弟長大了,看在打不過他的份上,就不打他了~ 稍加思索,我想起了Python不是可以去水印?說搞就搞! 去除水印原理 去除方法: 用 ...
  • 作者:陳昌浩 1 導讀 if…else…在代碼中經常使用,聽說可以通過Java 8的Function介面來消滅if…else…!Function介面是什麼?如果通過Function介面介面消滅if…else…呢?讓我們一起來探索一下吧。 2 Function介面 Function介面就是一個有且僅有 ...