パフォーマンス爆上げ！ PostgreSQLで重複データを削除してスピーディーなデータベースを実現

PostgreSQLで重複エントリを削除する方法

DISTINCT句を使用する

最も簡単な方法は、DISTINCT 句を使用して、重複のない行を取得することです。 ただし、この方法は、列の組み合わせに基づいて重複を削除する場合にのみ有効です。すべての列で一致する行だけが削除されます。

SELECT DISTINCT column1, column2, ... FROM table_name;

GROUP BY 句を使用して、各グループの最初の行のみを選択することもできます。 これにより、各グループ内のすべての重複が削除されます。

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

ウィンドウ関数を使用する

PostgreSQL 9.1以降では、ウィンドウ関数を使用して、より柔軟な方法で重複を削除できます。 例えば、ROW_NUMBER() 関数を使用して、各行に番号を付け、その番号に基づいて重複を削除できます。

DELETE FROM table_name
WHERE row_number() OVER (PARTITION BY column1, column2, ... ORDER BY column3, column4, ...) > 1;

CTEを使用する

Common Table Expressions (CTE)を使用して、より複雑な重複削除ロジックを実装することもできます。

例： 特定の条件に基づいて重複を削除する場合

WITH cte_duplicates AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column3, column4, ...) AS row_num
  FROM table_name
)
DELETE FROM table_name
WHERE id NOT IN (
  SELECT id FROM cte_duplicates
  WHERE row_num = 1
);

使用する方法は、データの特性と削除したい重複の種類によって異なります。

• シンプルなケース： DISTINCT 句または GROUP BY 句を使用します。
• より複雑なケース： ウィンドウ関数または CTE を使用します。

重要事項

• 重複を削除する前に、必ずデータのバックアップを取ってください。
• どの方法を使用する場合も、削除するデータが確実に不要であることを確認してください。
• 複雑なクエリを使用する場合は、パフォーマンスへの影響を考慮する必要があります。

PostgreSQLで重複エントリを削除するサンプルコード

-- 重複レコードを特定するCTEを作成
WITH cte_duplicates AS (
  SELECT customer_id, name, email, ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY customer_id) AS row_num
  FROM customers
)

-- 最初の行のみを残し、重複レコードを削除
DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM cte_duplicates
  WHERE row_num = 1
);

このコードの説明:

1. WITH cte_duplicates AS ( ... )：CTE (Common Table Expression) を定義します。この CTE は、customers テーブル内の各名前とメールアドレスの組み合わせに対して、customer_id と row_num (行番号) を含む行を返します。 ROW_NUMBER() 関数は、各パーティション内の行に番号を付けます。
2. DELETE FROM customers ...：customers テーブルからレコードを削除します。
3. WHERE customer_id NOT IN ( ... )：cte_duplicates CTE で row_num = 1 の行に関連付けられている customer_id を持つレコードのみを削除します。つまり、各名前とメールアドレスの組み合わせの最初の行のみを残します。

このコードをどのように変更して、ニーズに合わせることができますか？

• 削除する列を変更するには、ORDER BY 句を変更します。 例えば、customer_id で昇順に並べ替えるには、ORDER BY customer_id を使用します。
• 特定の条件に基づいて重複を削除するには、WHERE 句を追加します。 例えば、active 列が true のレコードのみを残すには、WHERE active = true を追加します。
• より複雑なロジックを実装するには、CTE を使用します。

サブクエリを使用する

DELETE ステートメント内でサブクエリを使用して、削除するレコードを特定する方法があります。

DELETE FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM customers AS c1
  WHERE EXISTS (
    SELECT 1
    FROM customers AS c2
    WHERE c2.customer_id != c1.customer_id
      AND c2.name = c1.name
      AND c2.email = c1.email
  )
);

ROW_NUMBER() 関数と CTE を組み合わせて、より複雑な重複削除ロジックを実装することもできます。

WITH cte_duplicates AS (
  SELECT customer_id, name, email, ROW_NUMBER() OVER (PARTITION BY name, email ORDER BY customer_id) AS row_num
  FROM customers
)
DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM cte_duplicates
  WHERE row_num = 1
);

PL/pgSQL 関数を使用して、より柔軟な方法で重複を削除することもできます。

CREATE OR REPLACE FUNCTION delete_duplicate_customers()
RETURNS void AS $$
BEGIN
  DELETE FROM customers c1
  USING (
    SELECT c2.customer_id
    FROM customers c2
    WHERE c2.customer_id < c1.customer_id
      AND c2.name = c1.name
      AND c2.email = c1.email
  ) AS dupes
  WHERE dupes.customer_id IS NOT NULL;
END; $$ LANGUAGE plpgsql;

CALL delete_duplicate_customers();

• シンプルなケース： サブクエリを使用する方法が最も簡単です。
• より複雑なケース： ROW_NUMBER() 関数と CTE または PL/pgSQL を使用する方が適しています。
• PL/pgSQL に慣れている場合: PL/pgSQL を使用すると、より柔軟なロジックを実装できます。