重複データを見つけて取り除くSQL

データ分析がはじめての方向けに、SQLで重複データを見つける方法と、安全に取り除く方法を、結果を見ながら解説します。重複は、売上や人数の水増しに直結する、実務でよくある問題です。

1. 重複データとは?

重複には、大きく2種類あります。

• 完全重複:すべての列の値が、同じになっている行

• キー重複:メールアドレスや注文番号など、「本来は1件しかないはずのキー」が同じで、他の列の値が違う行

原因は、システムの二重登録、データの取り込みの2回実行、表記のゆれ、変更履歴の混入など、さまざまです。まず、どちらの重複かを見分けることが、正しい対処の第一歩です。

2. この記事で使うデータ

会員表(members)を使います。email(メールアドレス)が「1人を表すキー」です。

表は全部で6行あり、c@example.com(鈴木)とd@example.com(高橋)は1件ずつです。重複しているのは、次の2つです。

• a@example.com:id 1とid 3で、名前も更新日も同じ(完全重複に近い状態)

• b@example.com:id 2とid 5で、名前と更新日が違う(キー重複)

3. 重複を見つける

方法1:キーごとの件数を数える

SELECT
  email,
  COUNT(*) AS cnt
FROM members
GROUP BY email
HAVING COUNT(*) > 1;

結果は、a@example.comとb@example.comが、それぞれ2件です。

GROUP BYとHAVING COUNT(*) > 1 の組み合わせは、重複チェックの定番です。結果が0件なら、重複はありません。

方法2:重複している行の中身を見る

件数だけでは、「何がどう重複しているか」が分かりません。ウィンドウ関数で、各行に件数を並べると、重複した行を、そのまま表示できます。

WITH counted AS (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY email) AS cnt
  FROM members
)
SELECT *
FROM counted
WHERE cnt > 1
ORDER BY email, id;

結果は、id 1、3、2、5の4行です。この行を並べて読むと、「a@は同じ内容、b@は内容が違う」という違いが、一目で分かります。

方法3:全体の重複の規模を確認する

SELECT
  COUNT(*)              AS total_rows,
  COUNT(DISTINCT email) AS unique_emails
FROM members;

結果は、total_rowsが6、unique_emailsが4です。差の2行が、余分に入っている行(重複している行)です。

4. 取り除く方法1:DISTINCT

DISTINCTは、「全部の列が同じ行」を1つにまとめる指定です。idの列を除いて実行してみましょう。

SELECT DISTINCT
  email,
  name,
  updated_at
FROM members;

結果は、5行です。a@example.comは、名前も更新日も同じため、1行になりました。しかし、b@example.comは、名前と更新日が違うため、2行のまま残ります。DISTINCTで消せるのは、完全に同じ内容の行だけです。キーが同じで中身が違う重複には、次の方法が必要です。

5. 取り除く方法2:ROW_NUMBERで1件だけ残す