---

交叉發(fā)布在我的博客上
您可以在這里閱讀

---

我們的架構(gòu)

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

現(xiàn)在我們要確保每個(gè)用戶不能多次喜歡同一個(gè)帖子。
這可以通過以下方式避免：

• 對(duì) post_like 表的一對(duì) post_id user_id 列使用唯一約束。
• 或者刪除post_like表的id列并在post_id user_id上使用復(fù)合主鍵

但是，假設(shè)我們已經(jīng)存在重復(fù)項(xiàng)，我們需要?jiǎng)h除它們。

檢查是否有重復(fù)

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 次。

刪除重復(fù)項(xiàng)

現(xiàn)在我們知道存在重復(fù)項(xiàng)，我們可以刪除它們。

我們將此過程分為兩步：

• 讀取重復(fù)項(xiàng)
• 刪除重復(fù)項(xiàng)（試運(yùn)行）
• 刪除重復(fù)項(xiàng)（實(shí)際運(yùn)行）

讀取重復(fù)項(xiàng)

事務(wù)回滾

為了在不刪除真實(shí)數(shù)據(jù)的情況下測試我們的查詢，直到我們確定查詢正確為止，我們使用事務(wù)回滾功能。

通過這樣做，我們的查詢將永遠(yuǎn)不會(huì)被提交，類似于
您可以在其他應(yīng)用程序上找到“試運(yùn)行”概念（例如
rsync）。

CTE

我們使用 CTE 因?yàn)樗峁┝肆己玫?DX。

使用 CTE，我們可以運(yùn)行查詢，將結(jié)果存儲(chǔ)在臨時(shí)表中，然后使用同一表進(jìn)行后續(xù)查詢。

這種心理模型類似于我們通常在編碼中創(chuàng)建臨時(shí)變量的做法。

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，我們可以執(zhí)行以下操作：

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       |

最新一行結(jié)果，其中g(shù)roup_index為2，表示該行是post_id = 3且user_id = 2的組中的第二行。

這里的語法會(huì)發(fā)生什么？

row_number() over (partition by ...) as group_index 是一個(gè)窗口函數(shù)，它首先按partition by 子句中的列對(duì)行進(jìn)行分組，然后根據(jù)行的索引為每行分配一個(gè)數(shù)字在組里。

partition 與 group by 類似，因?yàn)樗垂擦袑?duì)行進(jìn)行分組，但如果 group by 每組只返回 1 行，partition 讓我們根據(jù)組向源表添加新列。

group_index是列名別名，常規(guī)sql語法。

僅過濾重復(fù)項(xiàng)

現(xiàn)在讓我們只保留 group_index > 的項(xiàng)目1，這意味著該行不是組中的第一行，或者換句話說，它是重復(fù)的。

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 的這一行。

刪除重復(fù)項(xiàng) - 試運(yùn)行

現(xiàn)在重寫最終查詢，以便我們從 post_like 表中讀取，而不是再從 cte煩人的_info 中讀取。
我們?nèi)匀皇褂?cte duplics_info 來獲取重復(fù)項(xiàng)的 id。

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

我們將看到我們想要?jiǎng)h除的記錄。

在我們檢查它們正確后，我們將選擇與刪除交換。

 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>

最后一個(gè)查詢是我們最終想要執(zhí)行的。

但因?yàn)槲覀冞€有回滾語句，所以這些更改是模擬的，并沒有應(yīng)用到數(shù)據(jù)庫。

刪除重復(fù)項(xiàng) - 真實(shí)運(yùn)行

最后我們可以真正刪除重復(fù)項(xiàng)了。
這里我們使用提交而不是回滾，以便將更改應(yīng)用到數(shù)據(jù)庫。

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       |

結(jié)論

我寫文章主要是為了幫助自己的未來，或者幫助我在工作中使用的工具的發(fā)展。

如果這篇文章對(duì)您有幫助，請(qǐng)點(diǎn)贊。

你想讓我談?wù)撘粋€(gè)特定的話題嗎？

在評(píng)論里告訴我吧！

以上是如何在 Postgres SQL 中刪除重復(fù)項(xiàng)的詳細(xì)內(nèi)容。更多信息請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！