快捷導(dǎo)航

Postgresql刪除數(shù)據(jù)庫(kù)表中重復(fù)數(shù)據(jù)的幾種方法詳解

更新時(shí)間：2022年10月02日 11:04:42 作者：波多爾斯基

本文詳細(xì)講解了Postgresql刪除數(shù)據(jù)庫(kù)表中重復(fù)數(shù)據(jù)的幾種方法，文中通過(guò)示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下

一直使用Postgresql數(shù)據(jù)庫(kù)，有一張表是這樣的：

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，是通過(guò)程序生成的GUID，隨著數(shù)據(jù)表的越來(lái)越大（70w），即便我建立了索引，查詢(xún)效率依然不樂(lè)觀。

使用GUID作為數(shù)據(jù)庫(kù)的主鍵對(duì)分布式應(yīng)用比較友好，但是不利于數(shù)據(jù)的插入，可以使用類(lèi)似ABP的方法生成連續(xù)的GUID解決這個(gè)問(wèn)題。

為了進(jìn)行優(yōu)化，計(jì)劃使用DeviceId與Timestamp作為主鍵，由于主鍵會(huì)自動(dòng)建立索引，使用這兩個(gè)字段查詢(xún)的時(shí)候，查詢(xún)效率可以有很大的提升。不過(guò)，由于數(shù)據(jù)庫(kù)的插入了很多的重復(fù)數(shù)據(jù)，直接切換主鍵不可行，需要先剔除重復(fù)數(shù)據(jù)。

使用group by

數(shù)據(jù)量小的時(shí)候適用。對(duì)于我這個(gè)70w的數(shù)據(jù)，查詢(xún)運(yùn)行了半個(gè)多小時(shí)也無(wú)法完成。

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

使用DISTINCT

建立一張新表然后插入數(shù)據(jù)，或者使用select into語(yǔ)句。

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

不過(guò)這個(gè)問(wèn)題也非常大，很明顯，未來(lái)的表，是不需要Id列的，但是DataArray也沒(méi)有了，沒(méi)有意義。

如果SELECT DISTINCT "Timestamp", "DeviceId", "DataArray"，那么可能出現(xiàn)"Timestamp", "DeviceId"重復(fù)的現(xiàn)象。

使用ON CONFLICT

如果我們直接建立新表格，設(shè)置好新的主鍵，然后插入數(shù)據(jù)，如果重復(fù)了就跳過(guò)不就行了？但是使用select into是不行了，重復(fù)的數(shù)據(jù)會(huì)導(dǎo)致語(yǔ)句執(zhí)行中斷。需要借助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";

執(zhí)行不到100s就完成了，刪除了許多重復(fù)數(shù)據(jù)。

以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對(duì)腳本之家的支持。如果你想了解更多相關(guān)內(nèi)容請(qǐng)查看下面相關(guān)鏈接

您可能感興趣的文章: