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

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

赞 (0)

相关推荐

  • postgresql 删除重复数据案例详解

    1.建表 /* Navicat Premium Data Transfer Source Server : localhost Source Server Type : PostgreSQL Source Server Version : 110012 Source Host : localhost:5432 Source Catalog : postgres Source Schema : public Target Server Type : PostgreSQL Target Server

  • postgresql 删除重复数据的几种方法小结

    在使用PG数据库的这段时间,总结了三种删除重复数据的方法,其中最容易想到的就是最常规的删除方法,但此方法性能较差,删数据耗时较久,虽容易实现,但性能太差,影响写数据的速率. 另外就是被使用的group by删除方法,效率较高. 还有一种是刚发现的,还没有验证,现在就总结下这三种删除方法,并验证各自的执行效率. 首先创建一张基础表,并插入一定量的重复数据. test=# create table deltest(id int, name varchar(255)); CREATE TABLE te

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

    一直使用Postgresql数据库,有一张表是这样的: DROP TABLE IF EXISTS "public"."devicedata"; CREATE TABLE "public"."devicedata" ( "Id" varchar(200) COLLATE "pg_catalog"."default" NOT NULL, "DeviceId&qu

  • Python实现删除排序数组中重复项的两种方法示例

    本文实例讲述了Python实现删除排序数组中重复项的两种方法.分享给大家供大家参考,具体如下: 对于给定的有序数组nums,移除数组中存在的重复数字,确保每个数字只出现一次并返回新数组的长度 注意:不能为新数组申请额外的空间,只允许申请O(1)的额外空间修改输入数组 Example 1: Given nums = [1,1,2], Your function should return length = 2, with the first two elements of nums being 1

  • 使用Java构造和解析Json数据的两种方法(详解二)

    JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,采用完全独立于语言的文本格式,是理想的数据交换格式.同时,JSON是 JavaScript 原生格式,这意味着在 JavaScript 中处理 JSON数据不须要任何特殊的 API 或工具包. 在www.json.org上公布了很多JAVA下的json构造和解析工具,其中org.json和json-lib比较简单,两者使用上差不多但还是有些区别.下面接着介绍用org.json构造和解析Json数据的方法

  • 使用Java构造和解析Json数据的两种方法(详解一)

    JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式,采用完全独立于语言的文本格式,是理想的数据交换格式.同时,JSON是 JavaScript 原生格式,这意味着在 JavaScript 中处理 JSON数据不须要任何特殊的 API 或工具包. 在www.json.org上公布了很多JAVA下的json构造和解析工具,其中org.json和json-lib比较简单,两者使用上差不多但还是有些区别.下面首先介绍用json-lib构造和解析Json数据的方法

  • jQuery使用JSONP实现跨域获取数据的三种方法详解

    本文实例讲述了jQuery使用JSONP实现跨域获取数据的三种方法.分享给大家供大家参考,具体如下: 第一种方法是在ajax函数中设置dataType为'jsonp' $.ajax({ dataType: 'jsonp', url: 'http://www.a.com/user?id=123', success: function(data){ //处理data数据 } }); 第二种方法是利用getJSON来实现,只要在地址中加上callback=?参数即可 $.getJSON('http:/

  • Vue项目中打包优化的四种方法详解

    目录 前言 打包优化的目的: 性能优化的主要方向: 1.异步组件配置(路由懒加载) 2.去掉打包后的 console 3.使用CDN 4.yarn build生成dist目录 总结 前言 默认情况下,通过import语法导入的第三方依赖包,最终会全部打包到一个js文件中,会导致单文件体积过大大,在网速底下时会阻塞网页加载,影响用户体验. 打包优化的目的: 1.项目启动速度,和性能 2.必要的清理数据 3.减少打包后的体积 第一点是核心,第二点呢其实主要是清理console 性能优化的主要方向:

  • Java中ArrayList初始化的四种方法详解

    1 起因 在实际业务开发中, 我们经常会遇到需要临时创建一个数组的情况, 今天我们就来讲一下Java中ArrayList初始化的方法 2 解决方案 直接上结论, 总共有四种初始化方法: 双括号法 Arrays.asList stream Lists 2.1 双括号法 List<Integer> test = new ArrayList<Integer>(){{ add(1); add(2); }}; 2.2 Arrays.asList List<Integer> tes

  • PostgreSQL向数据库表中添加行数据的操作

    PostgreSQL向数据库表中添加行数据的操作

    实例: 例如数据库中有一个2013Rainfall数据表 想在下面接着插入一行数据,在pgAmin III 界面中,点击SQL,并其界面下输入, 如下图所示 并运行,同时刷新编辑数据界面,可看到数据插入成功, 如下图所示 补充:postgresql向表中插入大量数据 不使用存储过程: insert into schema.table select generate_series(1,10000),'a'; 以上表中有一列int类型列和一列char型列,generate_series(1,1000

  • php获取数组中重复数据的两种方法

    (1)利用php提供的函数,array_unique和array_diff_assoc来实现 复制代码 代码如下: <?php function FetchRepeatMemberInArray($array) {     // 获取去掉重复数据的数组     $unique_arr = array_unique ( $array );     // 获取重复数据的数组     $repeat_arr = array_diff_assoc ( $array, $unique_arr );    

  • OpenResty中正则模式匹配的2种方法详解

    前言 本文介绍 OpenResty 的两种正则模式匹配. 首先需要说明的是,OpenResty 套件中包含了两种语法:一种是主要基于 FFI API 实现的 OpenResty 语法,一种是类原生 Lua 脚本语言的语法. 在本文所介绍的内容中,对应以上两种语法的正则模式匹配分别是 ngx.re.find 和 string.find . 这两种规则起到完全相同的作用:在 subject string 中搜索指定的模式的串,若找到匹配值就返回它的开始位置和结束位置的位数,否则返回两个 nil 空值

随机推荐