会員データのメールアドレス入力間違い探し。全てに曖昧な重複検索を行いたい。

Question

どこからともなく舞い降りてきた "会員リストデータ" の
整理をお願いされたのですが、悩んでおります。

お力添えをいただけますと幸いです。

###概要・説明
会員サイトなどのユーザー登録時に
メールアドレスを入力することがあると思います。

そこで、
「[e-mail]」と入力するつもりが
「[e-mail]」と入力して登録を完了。

間違いに気付き、再び、ユーザー登録をし直すことで
結果としてデータベースには

• 「[e-mail]」
• 「[e-mail]」

両方とも登録されている状態です。

そんなデータが約4000件があり、ひとつひとつ

例：
SELECT email FROM users WHERE email LIKE '%文字列%'

としていくには、あまりにも手間と時間が
掛かり過ぎるので悩んでおります…。

###希望の動作

一言でいうなら、"曖昧な重複検索" を全てのレコードで行いたい。
その上で引っ掛かったものは、手作業でと考えておりまして。。。

// 頭の中ではこんな風にしたい！
SELECT email FROM users WHERE email IN (LIKE ('%',select email form users,'%') HAVING count(*) > 1);

※ もちろん上記では動作しません。

考えとしまして、要は、LIKEのキーワードにSELECTした結果を入れ、
カウントが1件以上あった場合にはデータを取得…と思ったのですが。

###最後に

最終手段として、PHPと組み合わせてHTML上でフォームを作成し、
SUBMITを4000回叩く…くらいしか方法が思いつきません。

もし「同じような経験をした方」「他の案が思いついた方」…などなど。
ヒント・考え方だけでも非常にありがたいです。

みなさま、どうかよろしくお願い致します。

Answer 1

追記
ハミング距離について、文字数が異なる場合は
レーベンシュタイン距離
と言われるそうです。リンク先にアルゴリズムも書かれています。

---

自分だったらこうします。

先ずは@マークで区切り名前とドメインに分けます。ドメインをdistinctかけて不正なドメインを洗い出します。もし不正なドメインがあればそれを持つメアドはゴミ確定。

これで4000からどれだけ減るか。

続いてハミング距離を求める。

ドメイン違いは別人とみなすかどうかも決めですね。別人であればドメイン単位でハミング距離を求めましょう。

ある1人のメアドの名前データに対して残りのメアドのハミング距離を片っ端から求める。
閾値を2とかにしておいてハミング距離3以上離れたデータの組み合わせは捨てる。

残りの組み合わせを人力で判断する。

Answer 2

総数で4000件ぐらいであれば、変にアルゴリズムを考えるより、アルファベット順にソートして、目視の方が早いと思います。休憩挟んで3時間ってところじゃないでしょうか。

Comments

[Tak1016]

[e-mail]さんが間違えて
[e-mail]と打ってしまった場合、アルファベット順だとだいぶ離れてしまいませんか。

[退会済みユーザー]

必要であれば、ドメインでもアルファベット順にソートすれば良いですよ。
回答主旨は手作業のほうが下手にアルゴリズム考えるより早いってことなので。

プログラム組んで、網羅性の確認や動作テスト実施するだけで3時間程度はすぐに経ってしまうので、力技のほうが早いこともあるよって提案です。

時間があって興味があれば、なんらかのプログラム組みますが、業務で効率重視なら、私なら適当な人捕まえて、目視確認してもらう分量です。

Answer 3

あまり回答にはなっていませんが ...

この手の作業、「名寄せ」や「データクレンジング」と呼ばれるような作業において、4,000 件というのはどちらかというと少ないほうです。
オンメモリで処理できる範囲内なので、全データを読み込んで処理すればいいのではないでしょうか。Tak1016 さんのおっしゃるように全アドレスの名前部分どおしのハミング距離を計算するのも、計算しながらハミング距離が小さいのだけ残していくので、メモリの消費も千件のオーダーで済みます。

「名寄せ」や「データクレンジング」でググると、別のアイディア、やりかたの参考になる情報がヒットするかもです。