我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Postgresql删除数据库表中重复数据的几种方法详解

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

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就完成了,删除了许多重复数据。

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。如果你想了解更多相关内容请查看下面相关链接

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

Postgresql删除数据库表中重复数据的几种方法详解

下载Word文档到电脑,方便收藏和打印~

下载Word文档

猜你喜欢

postgresql怎么删除表中重复数据的行

在PostgreSQL中,你可以使用以下方法删除表中重复数据的行:找出重复数据的行:SELECT col1, col2, ..., coln, COUNT(*) FROM table_nameGROUP BY col1, col2, .
postgresql怎么删除表中重复数据的行
2024-04-09

MySql中删除数据表的方法详解

目录定义:1 删除一个或多个没有被其他表关联的数据表1.1 新建一张表1.2 执行删除命令1.3 结果检查2 删除被其他表关联的主表2.1 创建两张具有关联关系的表2.2 执行删除DROP TABLE命令2.3 取消外键关系,再删除。定义:
2022-08-09

MySQL中恢复被误删除表数据的方法详解

在MySQL数据库中,有时会发生误删除表数据的情况,这可能是由于操作失误、程序错误或者恶意攻击等原因导致的。当发生这种情况时,及时采取恢复措施是非常重要的,以防止数据的永久丢失。本文将详细介绍在MySQL中恢复被误删除表数据的方法,并提供相
MySQL中恢复被误删除表数据的方法详解
2023-12-23

postgresql如何找到表中重复数据的行并删除

目录PostgreSQL找到表中重复数据的行并删除创建测试表并插入数据找到重复行并删除postgresql常用的删除重复数据方法最高效方法PG中三种删除重复数据方法总结postgresql找到表中重复数据的行并删除创建测试表并插入数据c
2023-05-05

redis删除数据的方法有哪几种

DEL key [key …]: 删除指定的key及其关联的值。FLUSHDB: 删除当前数据库中的所有key。FLUSHALL: 删除所有数据库中的所有key。UNLINK key [key …]: 异步删除指定的key及其关联的值,不阻
redis删除数据的方法有哪几种
2024-04-09

SQL中去除重复数据的几种方法汇总(窗口函数对数据去重)

目录方法1:distinct方法2:group by方法3:窗口函数使用SQL对数据进行提取和分析时,我们经常会遇到数据重复的场景,需要我们对数据进行去重后分析。以某电商公司的销售报表为例,常见的去重方法我们用到distinct 或者gr
2023-05-05

怎么在mysql数据库中删除重复的数据

本篇文章为大家展示了怎么在mysql数据库中删除重复的数据,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。1.问题引入假设一个场景,一张用户表,包含3个字段。id,identity_id,name。现
2023-06-07

编程热搜

目录