PostgreSQL查找并删除重复数据的方法总结

2024-03-01 0 623
目录
  • 创建示例表
  • 使用聚合函数查找重复记录
    • 基于单个字段的重复记录
    • 基于多个字段的重复记录
  • 使用窗口函数查找重复记录
    • 基于单个字段的重复记录
    • 基于多个字段的重复记录
  • 删除重复记录
    • 使用子查询删除重复记录
    • 使用窗口函数删除重复记录
  • 总结

    创建示例表

    我们首先创建一个示例表 people 并生成一些数据:

    — 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 字段存在重复数据;3、5 和 6 的 name 和 email 字段存在重复数据。

    此时,如果我们想要为 email 字段创建一个唯一约束,将会返回错误:

    ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);
    SQL 错误 [23505]: ERROR: could not create unique index \”uk_people_email\”
    详细:Key (email)=(lisi@test.com) is duplicated.

    我们必须找出并删除 email 字段中的重复记录才能创建唯一约束。

    使用聚合函数查找重复记录

    基于单个字段的重复记录

    如果想要找出 email 重复的数据,可以基于该字段进行分组统计,并且返回行数大于 1 的分组:

    SELECT email, count(email)
    FROM people
    GROUP BY email
    HAVING count(email) > 1;
    email |count|
    —————+—–+
    wangwu@test.com| 3|
    lisi@test.com | 2|

    查询结果显示有两个邮箱地址存在重复情况。如果想要查看完整的重复数据,可以使用子查询或者连接查询:

    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|

    另一种查找重复记录的方法就是直接使用自连接查询和 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,否则会某些数据(3、5、6)会返回多次。

    基于多个字段的重复记录

    如果我们想要找出 name 和 email 两个字段都重复的数据,可以基于这两个字段进行分组统计:

    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|

    只有当 name 和 email 都相同时才是重复数据,所以 2 和 4 不是重复记录。

    使用窗口函数查找重复记录

    使用聚合函数查找重复记录需要扫描同一个表两次,如果表中的数据量很大时,可能存在性能问题。为此,我们可以采用另一种方法:窗口函数。

    基于单个字段的重复记录

    首先,我们通过 count() 窗口函数找出每个 email 出现的次数:

    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|

    窗口函数不仅可以找出字段的重复次数,同时还可以保留原表中的数据,避免了二次扫描的操作。接下来我们只需要返回次数大于 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|

    基于多个字段的重复记录

    窗口函数同样支持基于多个字段的分区操作,以下语句可以用于找出 name 和 email 两个字段都重复的数据:

    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|

    显然,窗口函数不但拥有更好的性能,也具有更好的可读性。

    删除重复记录

    记下来需要解决的问题就是如何删除这些重复记录,通常我们需要保留其中的一条记录。

    使用子查询删除重复记录

    假如我们想要删除 email 字段重复的记录,只保留其中 id 最小的一条;可以使用子查询找出需要保留的数据,然后删除其他的数据:

    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 |

    也可以使用跨表删除或者关联子查询删除重复的数据:

    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
    );

    在执行上面的语句之前,记得重新创建 people 表并生成测试数据。

    想一想,如果想要保留重复数据中 id 最大的数据应该怎么实现呢?

    使用窗口函数删除重复记录

    ROW_NUMBER() 窗口函数可以用于将数据进行分组,然后为每一条数据分配一个唯一的数字编号。例如:

    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|

    以上语句基于 email 分组(PARTITION BY email),同时按照 id 进行排序(ORDER BY id),然后为每个组内的数据分配一个编号;如果编号大于 1 就意味着存在重复的数据。

    除了 ROW_NUMBER() 之外,RANK() 或者 DENSE_RANK() 函数也可以实现以上功能。关于窗口函数的介绍和使用案例,可以参考这篇文章。

    我们可以基于该查询结果删除重复的记录:

    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);

    在执行上面的语句之前,记得重新创建 people 表并生成测试数据。

    基于多个字段的重复数据删除方法和单个字段类似,大家可以自行尝试。最后,为了防止再次产生重复数据,可以增加一个唯一约束。例如:

    ALTER TABLE people ADD CONSTRAINT uk_people_email UNIQUE (email);

    总结

    本文介绍了如何在 PostgreSQL 中查找并删除重复记录,包括使用聚合函数加上子查询或者连接查询、使用窗口函数等方法查找单个字段或者多个字段中的重复数据,以及使用子查询和窗口函数等方法实现重复数据的删除。

    以上就是PostgreSQL查找并删除重复数据的方法总结的详细内容,更多关于PostgreSQL查找并删除重复数据的资料请关注悠久资源其它相关文章!

    收藏 (0) 打赏

    感谢您的支持,我会继续努力的!

    打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
    点赞 (0)

    悠久资源 PostgreSQL PostgreSQL查找并删除重复数据的方法总结 https://www.u-9.cn/database/postgresql/182733.html

    常见问题

    相关文章

    发表评论
    暂无评论
    官方客服团队

    为您解决烦忧 - 24小时在线 专业服务