【ITニュース解説】How to Find Duplicate Rows in SQL (and Decide What Counts as One)

SQLでデータベース内の重複行を見つけ出し、適切に処理することは、データの品質と信頼性を確保するためにシステムエンジニアが学ぶべき重要なスキルだ。このプロセスは、単に同じ値を持つ行を探すだけでなく、「重複」という言葉が何を意味するのかを明確に定義するところから始まる。

「重複」の定義には、主に三つの考え方がある。一つ目は「全列完全一致」で、すべての列の値が完全に同じ行を重複とみなす場合だ。これは多くの場合、データの誤った二重登録やシステムの読み込み事故によって生じるもので、情報的な価値を持たないため、余分なコピーを安全に削除できることが多い。二つ目は「自然キーの一致」で、customer_idのような現実世界で一意なものを識別するキーは同じだが、他の列（例えば住所や最終更新日）が異なる場合だ。このケースでは、どの情報が最新か、あるいはどの情報が正しいかといったビジネス上の判断が求められ、安易な削除は重要な情報を失う危険を伴う。三つ目は「同じ人物だが表記が異なる」といった「近接マッチ」だ。これは「Ben Ortiz」と「Benjamin Ortiz」のように、微妙な違いがあるためにSQLの厳密な一致検索では重複とみなされないケースで、「エンティティ解決」というより高度な技術や専門ツールが必要になる。したがって、SQLクエリを作成する前に、どの列が一致したら「重複」とするのか、そのビジネス上の意味合いを明確にすることが、この作業の最初の、そして最も重要なステップとなる。この定義によって、実行するSQLクエリのGROUP BY句に指定する列が変わってくるのだ。

重複の有無を素早く確認する方法として、「10秒テスト」が非常に有効だ。これは、テーブルの全行数を数えるCOUNT(*)と、重複を除いたキー列（例：customer_id）の数を数えるCOUNT(DISTINCT key)を比較する簡単なクエリである。もし両者の数字が一致すれば、そのキー列には重複がない。しかし、数字が異なっていれば、その差が重複行の数を示している。例えば、テーブルに14行あり、customer_idの異なる値が11個しかなければ、3行が重複していることがすぐにわかる。このテストは、データ分析や他のテーブルとの結合（JOIN）を行う前に、データの基本的な健全性を確認するために不可欠だ。特にJOINを行う場合、結合キーに重複があると、結合結果の行数が意図せず倍増し、その後の集計結果に誤りが生じる原因となる。

重複の存在が確認できたら、次にGROUP BYとHAVING句を使って、どのキーが重複しているのかを特定する。GROUP BY句には、ステップ1で定義した「重複を判断する列」を指定する。これにより、指定した列の値が同じ行がグループとしてまとめられる。HAVING句は、GROUP BYによって作成された各グループに対して条件を適用するもので、「COUNT(*) > 1」と指定することで、グループ内の行数が2つ以上、つまり重複しているグループだけを抽出できる。このクエリによって、どのcustomer_idが複数回出現しているか、そしてそれぞれ何回出現しているかといったサマリー情報が得られる。例えば、customer_idのみでGROUP BYした場合、顧客103と顧客105が重複していることがわかるが、全列でGROUP BYすると、顧客105はcity列が異なるために別グループとなり、検出されない。これは、GROUP BY句に指定する列が、そのまま「重複の定義」をコードとして表現していることを示している。

重複しているキーを特定した後、その詳細な内容を確認するために、サブクエリとWHERE IN句を組み合わせて、該当するすべての重複行を元のテーブルから取得する。GROUP BY句は行をグループ化して集計してしまうため、個々の行の詳細情報を見ることはできないが、この方法を使えば、特定された重複キーを持つ全ての行が、元の状態のまま表示される。記事の例では、顧客103の完全に同じ3行と、顧客105の都市が異なる2行の両方が表示され、両者の重複の性質が異なることが一目でわかる。この詳細な表示は、どちらの行を保持すべきか、あるいはどのような修正が必要かを判断するための重要な情報源となる。

重複行を特定し、その詳細を確認した上で、どの行を「正しい」データとして扱うかを決定し、それを明確にマークすることが推奨される。この目的のために非常に有用なのがROW_NUMBER()というウィンドウ関数だ。ROW_NUMBER()は、指定した基準に基づいて各行に連番を付与する。PARTITION BY句にcustomer_idなどのキー列を指定することで、キーごとに連番が独立してリセットされる。さらに、ORDER BY句には、どの行を「一番目」とするかのルールを指定する。例えば、signup_dateをORDER BY句に指定すれば、サインアップ日が最も古い行が「1」となり、これが「保持すべき行」としてマークされる。続く行には2、3…と連番が振られ、これらが「余分なコピー」となる。このマーキング手法は、重複行を物理的に削除するよりも推奨される。なぜなら、マーキングは可逆的であり、後からルールの変更やデータの再確認が必要になった場合でも、元のデータが失われることなく対応できるからだ。また、ORDER BY句に設定したルールはクエリの中に明確に記述されるため、データ所有者や他の関係者がそのルールをレビューし、合意を形成しやすい。これにより、レポート作成時には「copy_number = 1の行のみを使用する」といったフィルタリングを適用し、正確なデータを提供できる。

場合によっては重複行を物理的に削除する必要があることもあるが、これには極めて慎重な対応が求められる。特に、全列完全一致の「読み込み事故」による重複行が削除の主な対象となる。削除を行う一般的なパターンは、各重複キー内で特定の条件（例えば、最も古いrowidなど）を満たす行以外を削除するというものだ。しかし、この操作は不可逆的であり、一度削除したデータは元に戻せないため、二つのガードレールが必須である。一つは、DELETE文を実行する前に、同じWHERE句を使ったSELECT文を実行し、実際に削除される行が何であるかを事前に確認することだ。もしこのSELECTの結果が予想と異なれば、DELETEを実行してはならない。もう一つは、削除を行う前に元のテーブルのバックアップを取っておくか、コピーのテーブルで作業を行うことである。また、この削除パターンは通常、任意で最も古い行を保持するため、顧客105のように情報が異なる重複では、意図せず古い、あるいは誤った情報を残してしまう可能性がある。このため、削除よりもマーキングが優先されることが多いのである。

SQLクエリを作成する際には、その目的やルール、背景をコメントとして明確に残すことが極めて重要だ。単に重複行を検出するだけのクエリでは、どの行を保持すべきか、なぜその行を保持するのかといった具体的な判断基準が不明瞭なままだ。ROW_NUMBER()を使って連番を付与し、どの行を「キーパー」とするかのルールをORDER BY句で明確にし、さらにその背景や具体的なビジネス上の競合についてコメントで言及したクエリは、単なるコード以上の価値を持つ。このようなクエリは、将来的なメンテナンスやチーム内のコミュニケーションにおいて、非常に役立つものとなる。

重複検出は単純な作業に見えるが、いくつかの落とし穴がある。重複の定義を誤ると、意図した重複が見つからないことがある。また、個々のテーブルでは重複がなくても、結合（JOIN）によって重複行が生成される可能性もあるため、結合キーの健全性も確認すべきだ。表記ゆれによる「近接マッチ」はSQLの厳密なGROUP BYでは検出できず、NULL値はGROUP BYで一つのグループとして扱われるため、欠損データと重複を混同しないよう注意が必要だ。

これらの知識と手順を習得することで、システムエンジニアを目指す初心者は、データベース内の重複データという一般的な課題に、より自信を持って、かつ正確に対処できるようになる。特に、どんなテーブルであっても、そのデータを使用する前に「10秒テスト」を実行する習慣は、データの問題を早期に発見し、手遅れになる前に対応するための最も基本的で効果的なステップである。