---

交叉發(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è)貼文。
這可以透過以下方式避免：

• 對 post_like 表的一對 post_id user_id 欄位使用唯一約束。
• 或刪除post_like表的id列並在post_id user_id上使用複合主鍵

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

檢查是否有重複

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

刪除重複項(xiàng)

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

我們將此過程分為兩步驟：

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

讀取重複項(xiàng)

事務(wù)回滾

為了在不刪除真實(shí)資料的情況下測試我們的查詢，直到我們確定查詢正確為止，我們使用交易回滾功能。

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

CTE

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

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

這種心理模型類似於我們通常在編碼中創(chuàng)建臨時(shí)變數(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的群組中的第二行。

這裡的文法會發(fā)生什麼事？

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

partition 與 group by 類似，因?yàn)樗鼤垂擦袑π羞M(jìn)行分組，但如果 group by 每組只傳回 1 行，partition 讓我們根據(jù)群組向來源表新增列。

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

僅過濾重複項(xiàng)

現(xiàn)在讓我們只保留 group_index > 的項(xiàng)目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 的這一行。

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

現(xiàn)在重寫最終查詢，以便我們從 post_like 表中讀取，而不是再從 cte煩人的_info 中讀取。
我們?nèi)匀皇褂?cte duplics_info 來取得重複項(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)用到資料庫。

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

最後我們可以真正刪除重複了。
這裡我們使用提交而不是回滾，以便將變更應(yīng)用到資料庫。

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ā)展。

如果這篇文章對您有幫助，請按讚。

你想讓我談一個(gè)特定的話題嗎？

在評論裡告訴我吧！

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