交叉发布在我的博客上您可以在这里阅读
我们的架构
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_likegroup by post_id, user_idhaving count(*) > 2;
| post_id | user_id | count || ------- | ------- | ----- || 3 | 2 | 2 |
此输出告诉我们，用户 2 已多次喜欢帖子 3，特别是 2 次。
删除重复项
现在我们知道存在重复项，我们可以删除它们。
我们将此过程分为两步：
读取重复项删除重复项（试运行）删除重复项（实际运行）
读取重复项
事务回滚
为了在不删除真实数据的情况下测试我们的查询，直到我们确定查询正确为止，我们使用事务回滚功能。
通过这样做，我们的查询将永远不会被提交，类似于
您可以在其他应用程序上找到“试运行”概念（例如
rsync）。
cte
我们使用 cte 因为它提供了良好的 dx。
使用 cte，我们可以运行查询，将结果存储在临时表中，然后使用同一表进行后续查询。
这种心理模型类似于我们通常通过创建临时变量来进行编码。
cte 语法为
with as ( ), as ( -- here we can refernce ) -- here we can refernce and
通过交易和 cte，我们可以执行以下操作：
begin; -- start transactionwithduplicates_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 transactionwithduplicates_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 transactionwithduplicates_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 transactionwithduplicates_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 *+ deletefrom post_likewhere 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 transactionwithduplicates_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)deletefrom post_likewhere 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 transactionbegin; withduplicates_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)deletefrom post_likewhere 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 databasecommit;
结论
我写文章主要是为了帮助自己的未来，或者帮助我在工作中使用的工具的发展。
如果这篇文章对您有帮助，请点赞。
你想让我谈论一个特定的话题吗？
在评论里告诉我吧！
以上就是如何在 Postgres SQL 中删除重复项的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1498679.html
微信扫一扫 
支付宝扫一扫 
