SQL Interview Prep · レッスン

安全に行の重複を除去する

Q: 「安全に行の重複を除去する」で何を学びますか？

完全一致および類似した重複行を削除し、代表レコードを1件残します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

完全一致および類似した重複行を削除し、代表レコードを1件残します。

「安全に行の重複を除去する」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

重複排除の問題

「このテーブルには重複行があります。1件ずつ残して削除してください。」この種の問題は、ほとんどすべてのデータエンジニアリング面接で何らかの形で出題されます。課題は安全に実行することです。つまり、正規の行をちょうど1件残し、似て見えるだけの異なるレコードを誤って削除しないようにします。

重複の検出、残すコピーの選択、SELECTでの重複排除、そしてテーブルから重複を物理的に削除する方法を扱います。

まず重複を定義する

面接官に最初に尋ねる質問は、「どのような場合に2行を重複とみなしますか？」です。選択肢には次のようなものがあります。

完全一致の重複：すべての列が同一です。
キーによる重複：同じビジネスキー（例：同じemail）を持ちますが、その他の列は異なる場合があります。

手法はそれぞれ異なります。決めつけずに、重複の定義を確認してください。これは最も重要なステップであり、面接官も質問することを期待しています。

重複を検出する

重複キーを見つけるには、重複を定義する列でグループ化し、件数が1を超えるグループだけを残します。これにより、変更を加える前に、影響を受けるキーと存在するコピーの数を把握できます。

最初に検出用クエリを実行することは、ぜひ伝えておきたいベストプラクティスです。削除する前に問題の規模を確認できます。

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

完全一致の重複：DISTINCT

すべての列が完全に同一の重複であれば、読み取り専用の重複排除済みビューはSELECT DISTINCT *だけで作成できます。UNION（ALLなし）も重複行を削除します。

ただし、DISTINCTが役立つのは、行全体の重複を排除したい場合で、どのコピーを残すか選ぶ必要がない場合に限られます。列が異なるキーによる重複には、ランキングが必要です。

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

キーが重複する場合：ROW_NUMBER

行が同じキーを持ちながら他の列が異なる場合は、キーでパーティション分割し、各コピーに番号を付けます。rn = 1が残す行を示し、rn > 1が破棄する余分な行を示します。

ウィンドウ内のORDER BYによって、どのコピーを正規の行にするかが決まります。たとえば、最終更新日時が最も新しい行を残すなど、意図を持って選択してください。

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

正規のコピーを選択する

番号付けをCTEで包み、rn = 1だけを残します。これにより、キーごとに1行、具体的にはORDER BYで最初に順位付けされた行が返されます。

このSELECT形式は非破壊的です。クリーンなビューの作成や、ソースに触れずに重複排除済みのターゲットテーブルへINSERT ... SELECTする場合に最適です。

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

並び順の選択が重要

パーティション内のORDER BYは形式的なものではなく、ビジネス上の判断です。

ORDER BY updated_at DESCでは、最も新しいレコードを残します。
ORDER BY created_at ASCでは、最初に作成されたレコードを残します。
ORDER BY id ASCでは、最小のサロゲートキーを残します。安定した任意の選択が必要な場合に便利です。

主な並び替え列も同値になる場合に選択結果が決定的になるよう、一意なタイブレーカーを追加してください。

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

重複行を物理的に削除する

テーブルから実際に重複を削除するには、余分な行（rn > 1）を特定して削除します。PostgresとSQL ServerではCTEを使って削除できます。MySQLでは自己結合またはサブクエリが一般的です。

必ず先に対応するSELECTを実行し、具体的にどの行が消えるのかを確認してください。確認せずに削除することが、この質問で不合格になる原因です。

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

自己結合による削除パターン

典型的な移植性の高い方法では、重複キーごとに最小のidを持つ行を残し、自己結合を使って残りを削除します。ウィンドウ関数を必要としないため、古いデータベースエンジンでは特に有用です。

結合条件では、各行を、同じキーを持ちidがより小さい別の行と対応付けます。そのような小さいidの行が存在する行は、削除対象の重複です。

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

安全確認リスト

削除する前に、次の対策を取ってください。

削除をトランザクションで囲み、件数が想定と異なる場合にROLLBACKできるようにします。
まず削除対象の行に対してSELECT COUNT(*)を実行し、件数が妥当か確認します。
バックアップテーブルを作成することを検討します。CREATE TABLE users_bak AS SELECT * FROM users
PARTITION BYの列が本当に重複を定義していることを確認してください。そうしないと、異なるレコードを削除するおそれがあります。

BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

準重複と正規化

行が完全に等しくなくても、論理的には同じ場合があります。たとえば、'Ann@X.com'と'ann@x.com'、あるいは末尾の空白などです。元の列ではなく、正規化された式でパーティション分割してください。

正規化に言及すると、実務を理解していることを示せます。実際の重複は、大文字と小文字、空白、書式の違いに隠れていることが多く、単純なキー比較では見落とされます。

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

クイックチェック

安全な重複排除の方法を選択してください。

まとめ：安全な重複排除

重複排除は手順を守って行ってください。

まず重複の定義を決め、GROUP BY / HAVING COUNT(*) > 1で検出します。
完全一致の重複→DISTINCT。キーによる重複→キーでパーティション分割したROW_NUMBERを使い、rn = 1を残します。
ウィンドウのORDER BYで正規のコピーを選び、一意なタイブレーカーを追加します。
件数を確認した後、トランザクション内でrn > 1の行を削除します。
キーを正規化して準重複を検出します。

よくある質問

「安全に行の重複を除去する」レッスンは無料ですか？

はい。「安全に行の重複を除去する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「安全に行の重複を除去する」で何を学びますか？

完全一致および類似した重複行を削除し、代表レコードを1件残します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「安全に行の重複を除去する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る