PostgreSQL查找并刪除重復(fù)數(shù)據(jù)的方法總結(jié)
創(chuàng)建示例表
我們首先創(chuàng)建一個(gè)示例表 people 并生成一些數(shù)據(jù)：
-- DROP TABLE IF EXISTS people;
CREATE TABLE people (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name varchar(50) NOT NULL,
email varchar(100) NOT NULL
);
INSERT INTO people(name, email)
VALUES ('張三', 'zhangsan@test.com'),
('李四', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('李斯', 'lisi@test.com'),
('王五', 'wangwu@test.com'),
('王五', 'wangwu@test.com');
SELECT * FROM people;
id|name|email |
--|-----|-----------------|
1|張三 |zhangsan@test.com|
2|李四 |lisi@test.com |
3|王五 |wangwu@test.com |
4|李斯 |lisi@test.com |
5|王五 |wangwu@test.com |
6|王五 |wangwu@test.com |
其中，2 和 4 的 email 字段存在重復(fù)數(shù)據(jù)；3、5 和 6 的 name 和 email 字段存在重復(fù)數(shù)據(jù)。
此時(shí)，如果我們想要為 email 字段創(chuàng)建一個(gè)唯一約束，將會(huì)返回錯(cuò)誤：
ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email); SQL 錯(cuò)誤 [23505]: ERROR: could not create unique index "uk_people_email" 詳細(xì)：Key (email)=(lisi@test.com) is duplicated.
我們必須找出并刪除 email 字段中的重復(fù)記錄才能創(chuàng)建唯一約束。
使用聚合函數(shù)查找重復(fù)記錄
基于單個(gè)字段的重復(fù)記錄
如果想要找出 email 重復(fù)的數(shù)據(jù)，可以基于該字段進(jìn)行分組統(tǒng)計(jì)，并且返回行數(shù)大于 1 的分組：
SELECT email, count(email) FROM people GROUP BY email HAVING count(email) > 1; email |count| ---------------+-----+ wangwu@test.com| 3| lisi@test.com | 2|
查詢結(jié)果顯示有兩個(gè)郵箱地址存在重復(fù)情況。如果想要查看完整的重復(fù)數(shù)據(jù)，可以使用子查詢或者連接查詢：
SELECT *
FROM people
WHERE email IN (
SELECT email
FROM people
GROUP BY email
HAVING count(email) > 1)
ORDER BY email;
id|name|email |
--+----+----------------+
2|李四 |lisi@test.com |
4|李斯 |lisi@test.com |
3|王五 |wangwu@test.com|
5|王五 |wangwu@test.com|
6|王五 |wangwu@test.com|
WITH d AS (
SELECT email
FROM people
GROUP BY email
HAVING count(email) > 1)
SELECT p.*
FROM people p
JOIN d ON (d.email = p.email)
ORDER BY p.email;
id|name|email |
--+----+----------------+
2|李四 |lisi@test.com |
4|李斯 |lisi@test.com |
3|王五 |wangwu@test.com|
5|王五 |wangwu@test.com|
6|王五 |wangwu@test.com|
另一種查找重復(fù)記錄的方法就是直接使用自連接查詢和 distinct 操作符，例如：
SELECT DISTINCT p.* FROM people p JOIN people d ON p.email = d.email WHERE p.id <> d.id ORDER BY p.email; id|name|email | --+----+----------------+ 2|李四 |lisi@test.com | 4|李斯 |lisi@test.com | 3|王五 |wangwu@test.com| 5|王五 |wangwu@test.com| 6|王五 |wangwu@test.com|
注意不能省略 distinct，否則會(huì)某些數(shù)據(jù)（3、5、6）會(huì)返回多次。
基于多個(gè)字段的重復(fù)記錄
如果我們想要找出 name 和 email 兩個(gè)字段都重復(fù)的數(shù)據(jù)，可以基于這兩個(gè)字段進(jìn)行分組統(tǒng)計(jì)：
SELECT *
FROM people
WHERE (name, email) IN (
SELECT name, email
FROM people
GROUP BY name, email
HAVING count(1) > 1)
ORDER BY email;
id|name|email |
--+----+----------------+
3|王五 |wangwu@test.com|
5|王五 |wangwu@test.com|
6|王五 |wangwu@test.com|
WITH d AS (
SELECT name, email
FROM people
GROUP BY name, email
HAVING count(*) > 1)
SELECT p.*
FROM people p
JOIN d ON (d.name = p.name AND d.email = p.email)
ORDER BY p.email;
id|name|email |
--+----+---------------+
3|王五 |wangwu@test.com|
5|王五 |wangwu@test.com|
6|王五 |wangwu@test.com|
只有當(dāng) name 和 email 都相同時(shí)才是重復(fù)數(shù)據(jù)，所以 2 和 4 不是重復(fù)記錄。
使用窗口函數(shù)查找重復(fù)記錄
使用聚合函數(shù)查找重復(fù)記錄需要掃描同一個(gè)表兩次，如果表中的數(shù)據(jù)量很大時(shí)，可能存在性能問題。為此，我們可以采用另一種方法：窗口函數(shù)。
基于單個(gè)字段的重復(fù)記錄
首先，我們通過 count() 窗口函數(shù)找出每個(gè) email 出現(xiàn)的次數(shù)：
SELECT id, name, email, count(*) over (partition by email) cnt FROM people; id|name|email |cnt| --+----+------------------+---+ 2|李四 |lisi@test.com | 2| 4|李斯 |lisi@test.com | 2| 3|王五 |wangwu@test.com | 3| 5|王五 |wangwu@test.com | 3| 6|王五 |wangwu@test.com | 3| 1|張三 |zhangsan@test.com| 1|
窗口函數(shù)不僅可以找出字段的重復(fù)次數(shù)，同時(shí)還可以保留原表中的數(shù)據(jù)，避免了二次掃描的操作。接下來我們只需要返回次數(shù)大于 1 的記錄即可：
WITH d AS (
SELECT id, name, email,
count(*) over (partition by email) cnt
FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER BY id;
id|name|email |cnt|
--+----+----------------+---+
2|李四 |lisi@test.com | 2|
3|王五 |wangwu@test.com| 3|
4|李斯 |lisi@test.com | 2|
5|王五 |wangwu@test.com| 3|
6|王五 |wangwu@test.com| 3|
基于多個(gè)字段的重復(fù)記錄
窗口函數(shù)同樣支持基于多個(gè)字段的分區(qū)操作，以下語(yǔ)句可以用于找出 name 和 email 兩個(gè)字段都重復(fù)的數(shù)據(jù)：
WITH d AS (
SELECT id, name, email,
count(*) over (partition by name, email) cnt
FROM people)
SELECT *
FROM d
WHERE cnt > 1
ORDER BY id;
id|name|email |cnt|
--+----+----------------+---+
3|王五 |wangwu@test.com| 3|
5|王五 |wangwu@test.com| 3|
6|王五 |wangwu@test.com| 3|
顯然，窗口函數(shù)不但擁有更好的性能，也具有更好的可讀性。
刪除重復(fù)記錄
記下來需要解決的問題就是如何刪除這些重復(fù)記錄，通常我們需要保留其中的一條記錄。
使用子查詢刪除重復(fù)記錄
假如我們想要?jiǎng)h除 email 字段重復(fù)的記錄，只保留其中 id 最小的一條；可以使用子查詢找出需要保留的數(shù)據(jù)，然后刪除其他的數(shù)據(jù)：
DELETE
FROM people
WHERE id NOT IN (
SELECT min(id)
FROM people
GROUP BY email
);
刪除之后再次查詢 people 表：
SELECT * FROM people; id|name|email | --+----+------------------+ 1|張三 |zhangsan@test.com| 2|李四 |lisi@test.com | 3|王五 |wangwu@test.com |
也可以使用跨表刪除或者關(guān)聯(lián)子查詢刪除重復(fù)的數(shù)據(jù)：
DELETE
FROM people p
USING people d
WHERE p.email = d.email AND p.id < d.id;
DELETE
FROM people p
WHERE p.id NOT IN (
SELECT min(id)
FROM people
WHERE email = p.email
);
在執(zhí)行上面的語(yǔ)句之前，記得重新創(chuàng)建 people 表并生成測(cè)試數(shù)據(jù)。
想一想，如果想要保留重復(fù)數(shù)據(jù)中 id 最大的數(shù)據(jù)應(yīng)該怎么實(shí)現(xiàn)呢？
使用窗口函數(shù)刪除重復(fù)記錄
ROW_NUMBER() 窗口函數(shù)可以用于將數(shù)據(jù)進(jìn)行分組，然后為每一條數(shù)據(jù)分配一個(gè)唯一的數(shù)字編號(hào)。例如：
SELECT id, name, email,
row_number() over (PARTITION BY email ORDER BY id) AS row_num
FROM people;
ID|NAME|EMAIL |ROW_NUM|
--|----|------------------|-------|
2|李四 |lisi@test.com | 1|
4|李斯 |lisi@test.com | 2|
3|王五 |wangwu@test.com | 1|
5|王五 |wangwu@test.com | 2|
6|王五 |wangwu@test.com | 3|
1|張三 |zhangsan@test.com| 1|
以上語(yǔ)句基于 email 分組（PARTITION BY email），同時(shí)按照 id 進(jìn)行排序（ORDER BY id），然后為每個(gè)組內(nèi)的數(shù)據(jù)分配一個(gè)編號(hào)；如果編號(hào)大于 1 就意味著存在重復(fù)的數(shù)據(jù)。
除了 ROW_NUMBER() 之外，RANK() 或者 DENSE_RANK() 函數(shù)也可以實(shí)現(xiàn)以上功能。關(guān)于窗口函數(shù)的介紹和使用案例，可以參考這篇文章。
我們可以基于該查詢結(jié)果刪除重復(fù)的記錄：
DELETE
FROM people
WHERE id IN (
SELECT id
FROM (
SELECT id, name, email,
row_number() over (PARTITION BY email ORDER BY id DESC) AS row_num
FROM people) d
WHERE row_num > 1);
在執(zhí)行上面的語(yǔ)句之前，記得重新創(chuàng)建 people 表并生成測(cè)試數(shù)據(jù)。
基于多個(gè)字段的重復(fù)數(shù)據(jù)刪除方法和單個(gè)字段類似，大家可以自行嘗試。最后，為了防止再次產(chǎn)生重復(fù)數(shù)據(jù)，可以增加一個(gè)唯一約束。例如：
ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);
總結(jié)
本文介紹了如何在 PostgreSQL 中查找并刪除重復(fù)記錄，包括使用聚合函數(shù)加上子查詢或者連接查詢、使用窗口函數(shù)等方法查找單個(gè)字段或者多個(gè)字段中的重復(fù)數(shù)據(jù)，以及使用子查詢和窗口函數(shù)等方法實(shí)現(xiàn)重復(fù)數(shù)據(jù)的刪除。
以上就是PostgreSQL查找并刪除重復(fù)數(shù)據(jù)的方法總結(jié)的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL查找并刪除重復(fù)數(shù)據(jù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
這篇文章主要介紹了基于PostgreSQL 權(quán)限解讀，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql的now()與Oracle的sysdate區(qū)別說明
這篇文章主要介紹了postgresql的now()與Oracle的sysdate區(qū)別說明，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL死鎖了怎么辦？本文給大家講解了死鎖原因及解決方法，介紹l避免死鎖的實(shí)戰(zhàn)建議,本文給大家介紹的非常詳細(xì)，需要的朋友可以參考下2023-01-01
這篇文章主要介紹了PostgreSQL COALESCE使用方法代碼解析,文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-12-12
PostgreSQL的日期時(shí)間差DATEDIFF實(shí)例詳解
PostgreSQL是一款簡(jiǎn)介而又性能強(qiáng)大的數(shù)據(jù)庫(kù)應(yīng)用程序,其在日期時(shí)間數(shù)據(jù)方面所支持的功能也都非常給力,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL的日期時(shí)間差DATEDIFF的相關(guān)資料,需要的朋友可以參考下2023-04-04
Postgresql常用函數(shù)及使用方法大全(看一篇就夠了)
使用函數(shù)可以極大的提高用戶對(duì)數(shù)據(jù)庫(kù)的管理效率,函數(shù)表示輸入?yún)?shù)表示一個(gè)具有特定關(guān)系的值,下面這篇文章主要給大家介紹了關(guān)于Postgresql常用函數(shù)及使用方法的相關(guān)資料,需要的朋友可以參考下2022-11-11
Postgresql之時(shí)間戳long,TimeStamp,Date,String互轉(zhuǎn)方式
這篇文章主要介紹了Postgresql中的時(shí)間戳long,TimeStamp,Date,String互轉(zhuǎn)方式，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-03-03
PostgreSQL upsert(插入更新)數(shù)據(jù)的操作詳解
這篇文章主要介紹了PostgreSQL upsert(插入更新)教程詳解,本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2021-01-01
這篇文章主要介紹了PostgreSQL 慢查詢SQL跟蹤操作，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql刪除數(shù)據(jù)庫(kù)表中重復(fù)數(shù)據(jù)的幾種方法詳解
本文詳細(xì)講解了Postgresql刪除數(shù)據(jù)庫(kù)表中重復(fù)數(shù)據(jù)的幾種方法，文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2022-10-10