Skip to main content
 首页 » 数据库

PostgreSQL 删除重复数据

2022年07月19日182yyy_WW

本文介绍几种方法删除PostgreSQL表中的重复数据记录。

准备数据

创建 basket 表:

CREATE TABLE basket( 
    id SERIAL PRIMARY KEY, 
    fruit VARCHAR(50) NOT NULL 
); 

插入示例数据:

INSERT INTO basket(fruit) values('apple'); 
INSERT INTO basket(fruit) values('apple'); 
 
INSERT INTO basket(fruit) values('orange'); 
INSERT INTO basket(fruit) values('orange'); 
INSERT INTO basket(fruit) values('orange'); 
 
INSERT INTO basket(fruit) values('banana'); 
 
select * from basket; -- 查询示例数据 

返回结果:

id fruit
1 apple
2 apple
3 orange
4 orange
5 orange
6 banana

我们看到有两条 apple 和 三条 orange 记录,下面我们的目标是删除重复数据。

查询重复数据

SELECT   fruit,  COUNT(fruit) 
FROM basket 
GROUP BY fruit 
HAVING COUNT(fruit) > 1 
ORDER BY fruit; 

返回结果:

fruit count
apple 2
orange 3

删除重复数据

使用DELETE USING 语句

DELETE FROM basket a 
       USING basket b 
WHERE a.id < b.id 
  AND a.fruit = b.fruit; 

我们连接 basket 表 和 它自身,然后检查不同行满足条件为 (a.id < b.id) 和 fruit 相同(a.fruit = b.fruit); 结果符合我们预期,重复数据被删除。

我们发现最小ID 值对应记录被删除而保留了ID值最大的记录。如果我们选哟保留最小的记录:

DELETE FROM basket a 
       USING basket b 
WHERE a.id > b.id 
  AND a.fruit = b.fruit; 

结果是最小ID值记录保留。

使用子查询删除重复记录

DELETE FROM basket 
WHERE id IN 
   (SELECT id 
    FROM ( 
        SELECT id, ROW_NUMBER() OVER( PARTITION BY fruit ORDER BY id ) AS row_num 
        FROM basket  
    ) t 
    WHERE t.row_num > 1 ); 

子查询返回重复行,除了重复记录的第一行;然后外部DELETE 根据子查询删除对应记录。

如果你想保留最高ID记录,需要修改排序条件:

DELETE FROM basket 
WHERE id IN 
   (SELECT id 
    FROM ( 
        SELECT id, ROW_NUMBER() OVER( PARTITION BY fruit ORDER BY  id DESC ) AS row_num 
        FROM basket  
    ) t 
    WHERE t.row_num > 1 ); 

如果基于多个列确定重复记录,则查询模板为:

DELETE FROM table_name 
WHERE id IN 
    (SELECT id 
    FROM ( 
        SELECT id, 
         ROW_NUMBER() OVER( PARTITION BY column_1, column_2 ORDER BY  id ) AS row_num 
        FROM table_name  
    ) t 
    WHERE t.row_num > 1 ); 

如果数据量较大情况下,利用窗口函数的子查询方式效率应该会更优。因为不是每条记录和全部进行比较,而是在窗口内比较。

使用中间表删除数据

利用中间表需要下面几个步骤:

  1. 创建相同表结构的新表,用于存储无重复记录
  2. 插入无重复记录至新表
  3. 删除原表
  4. 重命名新表

实现如下:

-- step 1 
CREATE TABLE basket_temp (LIKE basket); 
 
-- step 2 
INSERT INTO basket_temp(fruit, id) 
SELECT  
    DISTINCT ON (fruit) fruit, 
    id 
FROM basket;  
 
-- step 3 
DROP TABLE basket; 
 
-- step 4 
ALTER TABLE basket_temp  
RENAME TO basket;             

总结

本文介绍不同方式删除重复记录,使用 delete using 、利用窗口函数的子查询方式,最后是利用中间表方式。


本文参考链接:https://blog.csdn.net/neweastsun/article/details/119566977
阅读延展