MySQL5で、重複レコードのみを抽出する方法を教えてください。


23個のフィールドを持つテーブルに、8万件弱のレコードがあります。
この中で、特定の19フィールドが完全に同じものを重複レコードとし、この重複レコードのみを抽出したいと考えています。(重複しているレコードを全て抽出)

どのような方法が良いでしょうか?

回答の条件
  • 1人2回まで
  • 登録:2009/02/08 11:10:06
  • 終了:2009/02/09 19:30:30

回答(2件)

id:mupic No.1

mupic回答回数8ベストアンサー獲得回数02009/02/08 11:24:05

ポイント35pt

試していませんが、参考になれば。

http://oshiete1.watch.impress.co.jp/qa4281665.html


抽出したいデータのフィールドでgroup byして

count(*)してhavingでカウントが1より大きいデータをとれば?

SELECT hoge,count(*) AS count

FROM テーブル

GROUP BY hoge

HAVING count>1

id:redice

回答ありがとうございました。試してみます。

追記:

上記を参考に色々と試したのですが、希望通りに重複データを全て取得できませんでした。

2009/02/09 19:03:07
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542009/02/08 13:37:17

ポイント35pt

3個の列で、重複チェックする例です。

19個の列の場合、「c1,c2,c3」と例示している3箇所を、それぞれ19個の列を指定してください。

select  * from t1
 where (c1,c2,c3) in(select c1,c2,c3      -- 19列の重複チェックの場合、19列すべてを指定する
                               from t1
                               group by c1,c2,c3      -- 19列の重複チェックの場合、19列すべてを指定す
                                having count(*)>1
                             )
id:redice

回答ありがとうございました。試してみます。

追記:

試してみましたが、タイムアウト(?)になるようで、結果を取得できませんでした。

2009/02/09 19:03:00
  • id:kn1967
    「どのようなSQLを書けば効率良く処理してくれるのか」については
    レコード件数だけでは何とも言えない部分があるので
    既に付いていた回答がオープンされるまで待っていたのだけど・・・。
    (23フィールドで8万レコード程度は小規模と言っても過言ではない)

    ユニークキーが存在しているなら
    in よりも existsのほうが早いと思うのだが・・・。
    どのようなつもりで回答されておられるのかコメント追加されると嬉しいですね。
  • id:chuken_kenkou
    最初はユニークキーがあることを前提条件として提示し、SQLを考えていましたが、「19個の列で、重複」ということからも、テーブル設計がどうなっているのか疑問で、重複チェックする列のみでの検索条件としました。

    group byで発生する「ファイルを使ったソート」を抑止するため、事前に「重複可のインデクスを定義しておいた方が、重複チェック自体は早く行える」といった提案も考えたのですが、まずはすぐに思いついた内容を回答しておけば、参考にはなるだろうという程度の考えで書き込みました。

    行数も、8万件程度ですしね。
  • id:redice
    コメントありがとうございます。
    テーブル設計に原因があるのか、サーバー環境に原因があるのかはよく分かりませんが、これまでに回答いただいた方法では希望通りの結果を得ることが出来ませんでした。

    とりあえず今回は、SQLで一気に取得するのは諦めて、PHPの配列に一度全部収めて、PHPで重複の抽出を行う形にしようと思います。
  • id:chuken_kenkou
    create indexで、重複を許すインデクスを、その19列の複合キーとして定義してみるのも一つの方法かも知れません。
    どういう環境なのか分からず、create index自体がそのタイムアウト(?)になってしまうかも知れませんが。。。

    create indexでも、内部的にソートが発生するのですが、通常のSQLのorder by、group by、distinctなどで発生するソートに比べ、特別なインターフェースで効率的な処理を取り入れているRDBMSが多いです。

    2009-02-08 14:28:38の私のコメントは、そのことを言っています。

    つまり、

    「create indexで重複可のインデクスを定義」

    「インデクスを活用し、重複チェックのための検索では、作業ファイルを使ったソートを発生なせない」

    が、

    「重複チェックのための検索で、作業ファイルを使ったソートを発生する」

    より、速い可能性があるということです。

    これは、対象のデータ件数にもよりますが、「"in"と"exists"のどちらが速いか」といったこととは、次元の違う性能差が生じる場合があります。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません