您的位置：首页 > 数据库 > PostgreSQL  >  Postgresql删除数据库表中重复数据的几种方法详解

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

 更新时间：2022年10月3日 15:27  点击：1854 作者：波多尔斯基

一直使用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://www.cnblogs.com/podolski/p/16745080.html

[!--infotagslink--]

相关文章

  • php读取zip文件(删除文件,提取文件,增加文件)实例

    下面小编来给大家演示几个php操作zip文件的实例，我们可以读取zip包中指定文件与删除zip包中指定文件，下面来给大这介绍一下。 从zip压缩文件中提取文件 代...2016-11-25

  • PHP 数据库缓存Memcache操作类

    操作类就是把一些常用的一系列的数据库或相关操作写在一个类中，这样调用时我们只要调用类文件，如果要执行相关操作就直接调用类文件中的方法函数就可以实现了，下面整理了...2016-11-25

  • C#连接SQL数据库和查询数据功能的操作技巧

    本文给大家分享C#连接SQL数据库和查询数据功能的操作技巧，本文通过图文并茂的形式给大家介绍的非常详细，需要的朋友参考下吧...2021-05-17

  • C#从数据库读取图片并保存的两种方法

    这篇文章主要介绍了C#从数据库读取图片并保存的方法，帮助大家更好的理解和使用c#，感兴趣的朋友可以了解下...2021-01-16

  • PostgreSQL判断字符串是否包含目标字符串的多种方法

    这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细，对大家的学习或工作具有一定的参考借鉴价值，需要的朋友可以参考下...2021-02-23

  • Intellij IDEA连接Navicat数据库的方法

    这篇文章主要介绍了Intellij IDEA连接Navicat数据库的方法,本文通过图文并茂的形式给大家介绍的非常详细，对大家的学习或工作具有一定的参考借价值，需要的朋友可以参考下...2021-03-25

  • 在数据库里将毫秒转换成date格式的方法

    在开发过程中，我们经常会将日期时间的毫秒数存放到数据库，但是它对应的时间看起来就十分不方便，我们可以使用一些函数将毫秒转换成date格式。 一、 在MySQL中，有内置的函数from_unixtime()来做相应的转换，使用如下： 复制...2014-05-31

  • PostgreSQL TIMESTAMP类型 时间戳操作

    这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26

  • C#操作本地文件及保存文件到数据库的基本方法总结

    C#使用System.IO中的文件操作方法在Windows系统中处理本地文件相当顺手,这里我们还总结了在Oracle中保存文件的方法,嗯,接下来就来看看整理的C#操作本地文件及保存文件到数据库的基本方法总结...2020-06-25

  • 删除条目时弹出的确认对话框

    复制代码 代码如下: <td> <a href="/member/life/edit_ppt/<?php echo $v->id;?>" class="btn">编辑</a> <a href="javascript:;" onclick="if(confirm('您确定删除这条记录？')){location.href='/member/life/d...2014-06-07

  • postgresql 实现多表关联删除

    这篇文章主要介绍了postgresql 实现多表关联删除操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02

  • 如何解决局域网内mysql数据库连接慢

    通过内网连另外一台机器的mysql服务， 确发现速度N慢！ 等了大约几十秒才等到提示输入密码。 但是ping mysql所在服务器却很快！ 想到很久之前有过类似的经验， telnet等一些服务在连接请求的时候，会做一些反向域名解析（如果...2015-10-21

  • Postgresql 如何选择正确的关闭模式

    这篇文章主要介绍了Postgresl 如何选择正确的关闭模式，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18

  • MySQL快速复制数据库数据表的方法

    某些时候，例如为了搭建一个测试环境，或者克隆一个网站，需要复制一个已存在的mysql数据库。使用以下方法，可以非常简单地实现。假设已经存在的数据库名字叫db1，想要复制一份，命名为newdb。步骤如下：1. 首先创建新的数据库newd...2015-10-21

  • mysqldump命令导入导出数据库方法与实例汇总

    mysqldump命令的用法1、导出所有库系统命令行mysqldump -uusername -ppassword --all-databases > all.sql 2、导入所有库mysql命令行mysql>source all.sql; 3、导出某些库系统命令行mysqldump -uusername -ppassword...2015-10-21

  • Mysql数据库错误代码中文详细说明

    1005：创建表失败1006：创建数据库失败1007：数据库已存在，创建数据库失败1008：数据库不存在，删除数据库失败1009：不能删除数据库文件导致删除数据库失败1010：不能删除数据目录导致删除数据库失败1011：删除数据库...2013-09-23

  • postgresql数据添加两个字段联合唯一的操作

    这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04

  • php跨网站请求伪造与防止伪造方法

    伪造跨站请求介绍伪造跨站请求比较难以防范，而且危害巨大，攻击者可以通过这种方式恶作剧，发spam信息，删除数据等等。...2013-10-01

  • c#异步读取数据库与异步更新ui的代码实现

    这篇文章主要介绍了c#从数据库里取得数据并异步更新ui的方法，大家参考使用吧...2020-06-25

  • Yii2.0高级框架数据库增删改查的一些操作

    yii2.0框架是PHP开发的一个比较高效率的框架，集合了作者的大量心血，下面通过用户为例给大家详解yii2使用中的一些基本的增删改查操作。 User::find()->all(); //返回所有用户数据； User::findOne($id); //返回 主键...2015-11-24