当前位置：首页 > 文章列表 > 文章 > 前端 > 如何在 Postgres SQL 中删除重复项

如何在 Postgres SQL 中删除重复项

来源：dev.to 2024-12-07 18:01:06 0浏览收藏

从现在开始，努力学习吧！本文《如何在 Postgres SQL 中删除重复项》主要讲解了等等相关知识点，我会在golang学习网中持续更新相关的系列文章，欢迎大家关注并积极留言建议。下面就先一起来看一下本篇正文内容吧，希望能帮到你！

交叉发布在我的博客上
您可以在这里阅读

我们的架构

create table "post" (
  id serial primary key,
  title varchar(255) not null,
  content text not null
);

create table "user" (
  id serial primary key,
  name varchar(255) not null
)

create table "post_like" (
  id serial primary key,
  post_id integer not null references post(id),
  user_id integer not null references user(id)
)

现在我们要确保每个用户不能多次喜欢同一个帖子。
这可以通过以下方式避免：

对 post_like 表的 post_id + user_id 列对使用唯一约束。
或者删除post_like表的id列并在post_id + user_id上使用复合主键

但是，假设我们已经存在重复项，我们需要删除它们。

检查是否有重复

select 
  post_id, 
  user_id,
  count(*)
from post_like
group by post_id, user_id
having count(*) > 2
;

| post_id | user_id | count |
| ------- | ------- | ----- |
| 3       | 2       | 2     |

此输出告诉我们，用户 2 已多次喜欢帖子 3，特别是 2 次。

删除重复项

现在我们知道存在重复项，我们可以删除它们。

我们将此过程分为两步：

读取重复项
删除重复项（试运行）
删除重复项（实际运行）

读取重复项

事务回滚

为了在不删除真实数据的情况下测试我们的查询，直到我们确定查询正确为止，我们使用事务回滚功能。

通过这样做，我们的查询将永远不会被提交，类似于
您可以在其他应用程序上找到“试运行”概念（例如
rsync）。

cte

我们使用 cte 因为它提供了良好的 dx。

使用 cte，我们可以运行查询，将结果存储在临时表中，然后使用同一表进行后续查询。

这种心理模型类似于我们通常通过创建临时变量来进行编码。

cte 语法为
 with 
 <cte_name> as (
   <query>
 ),
 <cte_name_2> as (
   <query_2> -- here we can refernce <cte_name>
 )
 <final_query> -- here we can refernce <cte_name> and <cte_name_2>

通过交易和 cte，我们可以执行以下操作：

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
;

rollback; -- ends transaction discarding every changes to the database

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 1           | 1  | 1       | 1       |
| 1           | 2  | 2       | 2       |
| 1           | 3  | 3       | 2       |
| 2           | 4  | 3       | 2       |

最新一行结果，其中group_index为2，表示该行是post_id = 3且user_id = 2的组中的第二行。

这里的语法会发生什么？

row_number() over (partition by ...) as group_index 是一个窗口函数，它首先按partition by 子句中的列对行进行分组，然后根据行的索引为每行分配一个数字在组中。

partition 与 group by 类似，因为它按公共列对行进行分组，但如果 group by 为每个组仅返回 1 行，partition 让我们根据组向源表添加新列。

group_index是列名别名，常规sql语法。

仅过滤重复项

现在我们只保留 group_index > 1 的项目，这意味着该行不是组中的第一行，或者换句话说，它是重复的。

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
select *
from duplicates_info
+ where group_index > 1
;

rollback; -- ends transaction discarding every changes to the database

| group_index | id | post_id | user_id |
| ----------- | -- | ------- | ------- |
| 2           | 4  | 3       | 2       |

我们只需删除 id 为 4 的这一行。

删除重复项 - 试运行

现在重写最终查询，以便我们从 post_like 表中读取，而不是再从 cte烦人的_info 中读取。
我们仍然使用 cte duplics_info 来获取重复项的 id。

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
- select *
- from duplicates_info
- where group_index > 1
+ select *
+ from post_like
+ where id in (
+  select id from duplicates_info
+  where group_index > 1
+ )
;

rollback; -- ends transaction discarding every changes to the database

我们将看到我们想要删除的记录。

在检查它们正确后，我们将选择与删除交换。

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
- select *
+ delete
from post_like
where id in (
 select id from duplicates_info
 where group_index > 1
)
+ returning * -- will output deleted rows
;

rollback; -- ends transaction discarding every changes to the database

最后一个查询是我们最终想要执行的。

但因为我们还有回滚语句，所以这些更改是模拟的，并没有应用到数据库。

删除重复项 - 真实运行

最后我们可以真正删除重复项了。
这里我们使用提交而不是回滚，以便将更改应用到数据库。

begin; -- start transaction

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
delete
from post_like
where id in (
 select id from duplicates_info
 where group_index > 1
)
returning * -- output deleted rows
;

- -- ends transaction discarding every changes to the database 
- rollback; 

+ -- ends transaction applying changes to the database
+ commit;

最终代码

-- start transaction
begin; 

with
duplicates_info as (
  select
    row_number() over (
      partition by post_id, user_id order by user_id
    ) as group_index,
    id,
    post_id,
    user_id
  from post_like
)
delete
from post_like
where id in (
 select id from duplicates_info
 where group_index > 1
)
returning * -- output deleted rows
;

-- ends transaction discarding every changes to the database 
-- rollback; 

-- ends transaction applying changes to the database
commit;