PostgreSQL 删除重复数据

本文介绍几种方法删除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