如何在 Postgres SQL 中删除重复项

2024-11-20 11:07:40 编辑：抖狐科技来源：摘自互联网

如何在 postgres sql 中删除重复项

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

我们的架构

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_name_2></cte_name></final_query></cte_name></query_2></cte_name_2></query></cte_name>
登录后复制

通过交易和 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;

登录后复制