SQLで重複レコードを抽出する方法

• ORACLE 11g

ここでは ORACLE を使っていますが、DB はなんでもいいです。

こんなデータがあったと仮定します。

・テーブル名：TEST_TBL

ID と PRICE は違えど、色付きが重複しているレコードと仮定します。

重複レコードをまとめて抽出するには、こんな SQL になります。重複件数も出しちゃいましょう。

SELECT MODEL, MANUFACTURER, CATEGORY, COUNT(1)
FROM TEST_TBL
GROUP BY MODEL, MANUFACTURER, CATEGORY
HAVING COUNT(1) > 1

結果はこうなります。

おおお、重複レコードを絞り込めましたねー＾＾

重複レコードを全件抽出するには、サブクエリ(副問合せ)を使います。

SELECT *
FROM TEST_TBL
WHERE (MODEL, MANUFACTURER, CATEGORY) in (
  SELECT MODEL, MANUFACTURER, CATEGORY
  FROM TEST_TBL
  GROUP BY MODEL, MANUFACTURER, CATEGORY
  HAVING COUNT(*) > 1
)
ORDER BY MODEL

わかりやすくするために ORDER BY 句を使っていますが、使わなくても OK です。

結果はこうなります。

おおお、重複レコードを全件絞り込めましたねー＾＾

この記事を書くに辺り、ネットで調査したところ、データ量が多いテーブルの場合には、サブクエリを使った SQL では返ってこない場合があるとか・・。

・重複しているレコードを検索するSQL(大量データも対応) - Qiita

なるほど・・、確かに・・。

よく考えれば上記のサブクエリは条件指定なしで実行していますからね、あり得るかも。

・・・と、今のところ自分は遭遇したことがありませんけど・・・＾＾；

下記のようにすればデータ量の多いテーブルでも対応できます。

SELECT * FROM TEST_TBL A
WHERE EXISTS (
  SELECT B.MODEL, B.MANUFACTURER, B.CATEGORY
  FROM TEST_TBL B
  WHERE A.MODEL = B.MODEL
  AND A.MANUFACTURER = B.MANUFACTURER
  AND A.CATEGORY = B.CATEGORY
  GROUP BY B.MODEL, B.MANUFACTURER, B.CATEGORY
  HAVING COUNT(1) > 1
)
ORDER BY A.MODEL

ここで使っている EXISTS 句は、副問合せによって返されたレコードが一つでもあれば True，一つもなければ False を返します。副問合せ側で，その外側(主問合せ)の列を参照する形式で、相関副問合せともいいます。詳しいことは割愛しますけど、これでデータ量が多くても重複レコードを抽出できます。

結果はこのとおり。

ふむ、いい感じ＾＾

SQLで重複レコードを抽出する方法を紹介しました。

重複レコードを抽出する SQL を知っていると、データ分析に大いに利用できます。前年・当年の同一条件で価格がどう変動したとか、ニーズの変化があったとか、それによるトレンドがどうだったとか、様々な場面で利用できます。

システム不具合なんかで、重複してしまったレコードを探したりするときにも役立ちます。私がずいぶん昔に携わった案件では、システム不具合で発生した重複している保守契約内容を調査し、本来の保守契約内容(紙と一致)にする・・なーんてお仕事がありました。その時にも使った記憶がありますねー、懐かしい。

できれば前向きな案件で使いたいですね＾＾