sql - 如何从 postgresql sql 表中删除重复行

Question

date        | window  | points  |    actual_bool      |         previous_bool          |       creation_time        | source 
------------+---------+---------+---------------------+---------------------------------+----------------------------+--------
 2021-02-11 |     110 |     0.6 |                   0 |                               0 | 2021-02-14 09:20:57.51966  | bldgh
 2021-02-11 |     150 |     0.7 |                   1 |                               0 | 2021-02-14 09:20:57.51966  | fiata
 2021-02-11 |     110 |     0.7 |                   1 |                               0 | 2021-02-14 09:20:57.51966  | nfiws
 2021-02-11 |     150 |     0.7 |                   1 |                               0 | 2021-02-14 09:20:57.51966  | fiata
 2021-02-11 |     110 |     0.6 |                   0 |                               0 | 2021-02-14 09:20:57.51966  | bldgh
 2021-02-11 |     110 |     0.3 |                   0 |                               1 | 2021-02-14 09:22:22.969014 | asdg1
 2021-02-11 |     110 |     0.6 |                   0 |                               0 | 2021-02-14 09:22:22.969014 | j
 2021-02-11 |     110 |     0.3 |                   0 |                               1 | 2021-02-14 09:22:22.969014 | aba
 2021-02-11 |     110 |     0.5 |                   0 |                               1 | 2021-02-14 09:22:22.969014 | fg
 2021-02-11 |     110 |     0.6 |                   1 |                               0 | 2021-02-14 09:22:22.969014 | wdda
 2021-02-11 |     110 |     0.7 |                   1 |                               1 | 2021-02-14 09:23:21.977685 | dda
 2021-02-11 |     110 |     0.5 |                   1 |                               0 | 2021-02-14 09:23:21.977685 | dd
 2021-02-11 |     110 |     0.6 |                   1 |                               1 | 2021-02-14 09:23:21.977685 | so
 2021-02-11 |     110 |     0.5 |                   1 |                               1 | 2021-02-14 09:23:21.977685 | dar
 2021-02-11 |     110 |     0.6 |                   1 |                               1 | 2021-02-14 09:23:21.977685 | firr
 2021-02-11 |     110 |     0.8 |                   1 |                               1 | 2021-02-14 09:24:15.831411 | xim
 2021-02-11 |     110 |     0.8 |                   1 |                               1 | 2021-02-14 09:24:15.831411 | cxyy
 2021-02-11 |     110 |     0.3 |                   0 |                               1 | 2021-02-14 09:24:15.831411 | bisd
 2021-02-11 |     110 |     0.1 |                   0 |                               1 | 2021-02-14 09:24:15.831411 | cope
 2021-02-11 |     110 |     0.2 |                   0 |                               1 | 2021-02-14 09:24:15.831411 | sand
 ...

我在 testdb 中名为 testtable 的 postgresql 表中有以下数据集。

我不小心复制了数据库并复制了行。

如何删除重复项？

第 1 行和第 5 行是该帧中的副本，第 2 行和第 4 行也是副本。

我以前从未使用过 sql 来删除重复项，我不知道从哪里开始。

我试过了

select creation_time, count(creation_time) from classification group by creation_time having count (creation_time)>1 order by source;

但它所做的只是显示我每天有多少重复项，

像这样

       creation_time        | count 
----------------------------+-------
 2021-02-14 09:20:57.51966  |    10
 2021-02-14 09:22:22.969014 |    10
 2021-02-14 09:23:21.977685 |    10
 2021-02-14 09:24:15.831411 |    10
 2021-02-14 09:24:27.733763 |    10
 2021-02-14 09:24:38.41793  |    10
 2021-02-14 09:27:04.432466 |    10
 2021-02-14 09:27:21.62256  |    10
 2021-02-14 09:27:22.677763 |    10
 2021-02-14 09:27:37.996054 |    10
 2021-02-14 09:28:09.275041 |    10
 2021-02-14 09:28:22.649391 |    10
...

每个creation_timestamp中只能有5条唯一记录。

它没有向我显示重复项，即使我这样做了，它也不知道如何删除它们。

Answer 1

需要删除很多行。我建议重新创建表格:

create table new_classification as
    select distinct c.*
    from classification c;

验证数据后，如果您确实需要，可以重新加载它:

truncate table classification;

insert into classification
    select *
    from new_classification;

此过程应该比删除 90% 的行快得多。