如何在 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，这意味着该行不是组中的第一行，或者换句话说，它是重复的。

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)
)

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

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

删除重复项 - 试运行

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

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

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

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

 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>

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

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

删除重复项 - 真实运行

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

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       |

结论

我写文章主要是为了帮助自己的未来，或者帮助我在工作中使用的工具的发展。

如果这篇文章对您有帮助，请点赞。

你想让我谈论一个特定的话题吗？

在评论里告诉我吧！

以上是如何在 Postgres SQL 中删除重复项的详细内容。更多信息请关注PHP中文网其他相关文章！