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

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

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

重複レコードを手っ取り早く削除する方法に DISTINCT という方法がありますが、重複レコードを手っ取り早く抽出する方法はありません。

方法として GROUP BY と HAVING を使って、サブクエリで対応することで抽出できます。

たまにしか使わないので、忘れないように備忘録的に書いておきます。

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


環境

  • ORACLE 11g

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

テーブルとデータ

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

・テーブル名:TEST_TBL

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

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

重複レコードをまとめて抽出

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

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

結果はこうなります。

SQLで重複レコードをまとめて抽出

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

重複レコードを全件抽出する

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

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 では返ってこない場合があるとか・・。

重複しているレコードを検索する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で重複レコードを抽出する方法を紹介しました。

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

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

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

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。