以正确的方式在多列的大表中查找重复记录

Question

我现在已经阅读了很多关于这个主题的线程，并尝试了一些事情，但没有像我希望的那样奏效。如果这被认为是重复的线索，我需要澄清并道歉。

我的一个客户端托管了一个Postgres数据库，其中一个表包含1200多万条记录。他们让我找到重复的记录，提取它们以供查看，如果一切正常，则删除重复的记录。

我主要关心的是服务器的性能。对1200万条记录运行DISTINCT查询必须消耗大量资源？

由于我的第一个任务是提取记录以在中查看，比如CSV，而不是简单地删除它们，所以我在PgAdmin中的方法是对文件执行此操作。

SELECT * 
FROM
    my_table
WHERE
my_table_id NOT IN (
                SELECT DISTINCT 
                    ON (
                        num_1,
                        num_2,
                        num_3,
                        num_4,
                        num_5,
                        my_date
                    )
                    my_table_id
                FROM
                    my_table
);

然而，这个查询需要很长时间。执行了20分钟后，我停止了执行。为了使事情更加复杂，由于严格的安全性，我的客户不愿意允许我克隆表的本地副本。他们更喜欢在现场托管环境中完成。

表的定义非常简单。看起来像这个

CREATE TABLE my_table
(
    my_table_id bigserial NOT NULL,
    num_1 bigserial NOT NULL,
    num_2 bigserial NOT NULL,
    num_3 bigserial NOT NULL,
    num_4 numeric,
    num_5 integer,
    my_date date,
    my_text character varying
)

主键"my_table_id"未被泄露，并且始终是唯一的。col"my_text"在查询中并不有趣，因为它对于所有重复项都是空的。只有数字字段和日期需要匹配。所有列（my_table_id和my_text除外）必须在记录之间匹配，才能符合重复项的条件。

解决这个问题的最佳方法是什么？有没有一种服务器友好的方式不会占用主机环境中的所有资源？请帮助我了解最佳方法！

谢谢！

Answer 1

需要使用GROUP BY和HAVING来获得重复记录，而不是DISTINCT

子查询将查找所有重复的记录

SELECT * FROM
my_table mt
JOIN
(
     SELECT
            num_1,
            num_2,
            num_3,
            num_4,
            num_5,
            my_date
     FROM
            my_table
     GROUP BY num_1, num_2, num_3, num_4, num_5, my_date
     HAVING COUNT(*) >1
) T 
ON mt.num_1= T.num_1
and mt.num_2= T.num_2
and mt.num_3= T.num_3
and mt.num_4= T.num_4
and mt.num_5= T.num_5
and mt.my_date= T.my_date

Answer 2

使用分析函数的另一种方法

select * from (
    select * , 
    count(*) over (partition by num1,num2,num3,num4,my_date) cnt
    from mytable
) t1 where cnt > 1

Answer 3

#检查重复记录的简单方法

SELECT COL1,COL2,COL3,COL4, COUNT(*) as DUP_CNT
FROM TABLE_NAME
GROUP BY COL1,COL2,COL3,COL4
HAVING COUNT(*) > 1;

如果它返回数据，则您有重复的记录，否则没有

#跌落限制

alter table TABLE_NAME drop constraint PK_CONSTRAINTS_NAME;

#添加限制

alter table TABLE_NAME add constraint PK_CONSTRAINTS_NAME primary key(COL1,COL2,COL3,COL4);