如何在 Postgres SQL 中刪除重複項-js教程-PHP中文網

How to remove duplicates in 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，我們可以執行以下操作：

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>

登入後複製

最後一個查詢是我們最終想要執行的。

但因為我們還有回滾語句，所以這些變更是模擬的，並沒有應用到資料庫。

刪除重複項 - 真實運行

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

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       |

登入後複製