PostgreSQLで重複行を見つけて削除する方法：3つのアプローチと比較

PostgreSQLで重複行を見つけて削除する

DISTINCT句を使用する

DELETE FROM table_name
WHERE rownum IN (
  SELECT ROW_NUMBER() OVER (ORDER BY col1, col2, ...)
  FROM table_name
  GROUP BY col1, col2, ...
  HAVING COUNT(*) > 1
);

この方法はシンプルですが、すべての列を指定する必要があるという制限があります。また、パフォーマンスが遅い場合もあります。

CTEを使用する

より高速な方法として、WITH 句と共通表式 (CTE) を使用して重複行を削除できます。

WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY col1, col2, ...) AS rn
  FROM table_name
)
DELETE FROM cte
WHERE rn > 1;

この方法は、DISTINCT 句よりも高速ですが、より複雑です。

サブクエリを使用する

最も柔軟な方法は、サブクエリを使用して重複行を削除することです。

DELETE FROM table_name t1
WHERE EXISTS (
  SELECT 1
  FROM table_name t2
  WHERE t2.col1 = t1.col1
    AND t2.col2 = t1.col2
    AND t2.id < t1.id
);

この方法は、複雑な条件で重複行を削除する場合に役立ちます。

Ruby on Rails 3 での使用

上記のいずれかの方法を、Ruby on Rails 3 アプリケーション内で ActiveRecordを使用して実装できます。

# DISTINCT 句を使用する
class User < ActiveRecord::Base
  def self.remove_duplicates
    connection.execute <<-SQL
      DELETE FROM users
      WHERE rownum IN (
        SELECT ROW_NUMBER() OVER (ORDER BY name, email)
        FROM users
        GROUP BY name, email
        HAVING COUNT(*) > 1
      );
    SQL
  end
end

# CTE を使用する
class User < ActiveRecord::Base
  def self.remove_duplicates
    connection.execute <<-SQL
      WITH cte AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY name, email) AS rn
        FROM users
      )
      DELETE FROM cte
      WHERE rn > 1;
    SQL
  end
end

# サブクエリを使用する
class User < ActiveRecord::Base
  def self.remove_duplicates
    connection.execute <<-SQL
      DELETE FROM users t1
      WHERE EXISTS (
        SELECT 1
        FROM users t2
        WHERE t2.name = t1.name
          AND t2.email = t1.email
          AND t2.id < t1.id
      );
    SQL
  end
end

これらの例はほんの一例であり、ニーズに合わせて調整する必要があります。

注意事項

• 重複行を削除する前に、必ずデータのバックアップを取ってください。
• 重複行を削除すると、データが失われる可能性があります。
• 重複行を削除する前に、影響を受ける可能性のあるすべてのアプリケーションを考慮してください。

-- 重複行を削除し、すべての列を保持する
DELETE FROM customers
WHERE rownum IN (
  SELECT ROW_NUMBER() OVER (ORDER BY first_name, last_name, email)
  FROM customers
  GROUP BY first_name, last_name, email
  HAVING COUNT(*) > 1
);

-- 特定の列のみを保持する
DELETE FROM customers
WHERE rownum IN (
  SELECT ROW_NUMBER() OVER (ORDER BY first_name, last_name)
  FROM customers
  GROUP BY first_name, last_name
  HAVING COUNT(*) > 1
)
SELECT first_name, last_name, email
FROM customers;

-- 重複行を削除し、すべての列を保持する
WITH cte AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY first_name, last_name, email) AS rn
  FROM customers
)
DELETE FROM cte
WHERE rn > 1;

-- 特定の列のみを保持する
WITH cte AS (
  SELECT first_name, last_name, email, ROW_NUMBER() OVER (ORDER BY first_name, last_name) AS rn
  FROM customers
)
SELECT first_name, last_name, email
FROM cte
WHERE rn = 1;

-- 重複行を削除し、すべての列を保持する
DELETE FROM customers t1
WHERE EXISTS (
  SELECT 1
  FROM customers t2
  WHERE t2.first_name = t1.first_name
    AND t2.last_name = t1.last_name
    AND t2.email = t1.email
    AND t2.id < t1.id
);

-- 特定の列のみを保持する
SELECT first_name, last_name, email
FROM customers t1
WHERE NOT EXISTS (
  SELECT 1
  FROM customers t2
  WHERE t2.first_name = t1.first_name
    AND t2.last_name = t1.last_name
    AND t2.email = t1.email
    AND t2.id < t1.id
);

以下のコードは、ActiveRecordを使用して上記のいずれかの方法を Rails アプリケーションに実装する方法を示しています。

class User < ActiveRecord::Base
  # DISTINCT 句を使用する
  def self.remove_duplicates_with_distinct
    connection.execute <<-SQL
      DELETE FROM users
      WHERE rownum IN (
        SELECT ROW_NUMBER() OVER (ORDER BY name, email)
        FROM users
        GROUP BY name, email
        HAVING COUNT(*) > 1
      );
    SQL
  end

  # CTE を使用する
  def self.remove_duplicates_with_cte
    connection.execute <<-SQL
      WITH cte AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY name, email) AS rn
        FROM users
      )
      DELETE FROM cte
      WHERE rn > 1;
    SQL
  end

  # サブクエリを使用する
  def self.remove_duplicates_with_subquery
    connection.execute <<-SQL
      DELETE FROM users t1
      WHERE EXISTS (
        SELECT 1
        FROM users t2
        WHERE t2.name = t1.name
          AND t2.email = t1.email
          AND t2.id < t1.id
      );
    SQL
  end
end

この方法は、DISTINCT 句と似ていますが、より柔軟です。特定の列の値に基づいて重複行を削除できます。

DELETE FROM customers
WHERE rownum = 1
PARTITION BY first_name, last_name
ORDER BY first_name, last_name, id;

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

PostgreSQL 9.5 以降では、ウィンドウ関数を使用して重複行を削除できます。

DELETE FROM customers
WHERE dense_rank() OVER (ORDER BY first_name, last_name, email) > 1;

トリガーを使用する

トリガーを使用して、新しい行が挿入されるたびに重複行を自動的に削除できます。

CREATE TRIGGER delete_duplicate_customers
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1
    FROM customers t2
    WHERE t2.first_name = NEW.first_name
      AND t2.last_name = NEW.last_name
      AND t2.email = NEW.email
      AND t2.id < NEW.id
  ) THEN
    RETURN NULL;
  END IF;
END;

結合を使用する

結合を使用して、重複行を識別できます。

DELETE FROM customers t1
USING (
  SELECT first_name, last_name, email, MIN(id) AS min_id
  FROM customers
  GROUP BY first_name, last_name, email
) t2
ON t1.first_name = t2.first_name
AND t1.last_name = t2.last_name
AND t1.email = t2.email
AND t1.id > t2.min_id;