交叉发布在我的博客上 您可以在这里阅读
我们的架构
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 语法为
withas ( ), as ( -- here we can refernce ) -- here we can refernce and
通过交易和 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;
结论
我写文章主要是为了帮助自己的未来，或者帮助我在工作中使用的工具的发展。
如果这篇文章对您有帮助，请点赞。
你想让我谈论一个特定的话题吗？
在评论里告诉我吧！
