Postgresql删除数据库表中重复数据的几种方法详解
iamitnan · 160浏览 · 发布于2022-10-08
本文详细讲解了Postgresql删除数据库表中重复数据的几种方法,文中通过示例代码介绍的非常详细。对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下
一直使用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就完成了,删除了许多重复数据。
相关推荐
使用SELECT语句检索数据
奔跑的男人 · 807浏览 · 2019-06-03 09:33:43
部署MySQL延迟从库的几个好处
吴振华 · 666浏览 · 2019-05-14 21:57:51
MongoDB凭什么跻身数据库排行前五?
iamitnan · 725浏览 · 2019-06-18 10:04:56
一个快速上手、轻量级 Golang 公共类库 (golang_common)
qq2360248666 · 901浏览 · 2019-05-29 09:48:45
详解基于linux环境MySQL搭建与卸载
追忆似水年华 · 876浏览 · 2019-05-27 09:54:23
Oracle开启和关闭的几种模式
qq2360248666 · 756浏览 · 2019-06-04 10:18:47
分类专栏
最新发布
最热排行
0评论