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 回「いいね！」したことを示しています。

重複を削除する

重複があることがわかったので、それらを削除できます。

このプロセスを 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 のグループ内の 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 のこの行のみを削除する必要があります。

重複の削除 - 予行演習

ここで、cte Duplicates_info からではなく、post_like テーブルから読み取るように、最後のクエリを書き直します。
重複の ID を取得するには、引き続き cte Duplicates_info を使用します。

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

結論

私は主に将来の自分を助けるため、または仕事で使用するツールの成長を助けるために記事を書きます。

この記事が役に立った場合は、「いいね！」を残してください。

特定のトピックについて話したいですか?

コメント欄で教えてください!

以上がPostgres SQL で重複を削除する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。