CodeZine(コードジン)

特集ページ一覧

SQLによる条件付き重複データの抽出と単純な名寄せ

既存のRDBシステムに会員情報の名寄せ機能を追加する 第1回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2012/09/13 14:00
目次

【理論編】名寄せ処理の理論

 人物の同一性をプログラミングのみで判断することは不可能です。本連載では、システムは操作者の指定に従い、重複値抽出の自動化に特化し、判断は人間が行う方式で進めます。

図3 二重登録と名寄せ処理
図3 二重登録と名寄せ処理

会員テーブルの前提条件

 データに「ゆらぎ」があると、単純に重複値の抽出が行えません。その回避のため、会員テーブルは下記の条件を満たす(仮に「標準化」と呼んでおきます)ものとします。

  • 姓名は別々のフィールドに分離されていること
  • 姓名それぞれ、フリガナ(または、ふりがな)のフィールドを持つこと
  • データ内に空白文字を含まないこと
  • データ内の英数字は半角(または、全角)で統一されていること

 標準化はテーブル設計時に考慮し、登録/修正時には適切な検証または変換処理を行い、条件を保つようにするべきと筆者は考えます。皆様のRDBシステムに機能追加される際には、必要に応じて事前に変換/修正して「標準化」することを強くお勧めします。

SQLの基礎知識の復習とRDBごとの方言

 下記、漢字部分は実名への置換、キーワードと名前の間には半角スペースが必要です。

  • 「SELECT * FROM 表名」は、ある表の全行の全列を取り出します。
  • 列を指定する場合は、「*」を「列名1, 列名2, …, 列名n」に置き換えます。
  • 結果をソートしたい場合は、「ORDER BY 列名1, …」を付加します。
  • 行を絞り込む場合は、「WHERE 条件式」(例:WHERE Sei='遠藤')を付加します。
    条件式には「AND」「OR」「NOT」も利用可能です。
    「Sei='岸' OR Sei='森' OR Sei='菅'」は「Sei IN ('岸','森','菅')」とも書けます。
  • 列でグループ化し、集計関数を使用する場合「GROUP BY 列名1, …」を付加します。
  • グループの条件には「HAVING 条件式」(例:HAVING COUNT(*) >= 2)を付加します。
  • SELECT文の中にもう一つSELECT文「(SELECT …)」を入れ子(「サブクエリー」と言います)にし、その結果を利用することができます。
  • 同じ表を別扱いしたい場合は「表名 AS 別名」とし、接頭辞「別名.」を付けます。
  • 「DELETE FROM 表名 WHERE 条件式」とすると該当レコードだけが削除されます。
  • ワイルドカード「%」(半角のパーセント)を利用して前方一致「Adr LIKE '三重県%'」などが可能です。「Adr LIKE '三重県津市'」は「Adr='三重県津市'」と同義です。
  • 列名の条件に空文字でなくNULLを使う場合、「列名 IS NULL」と書きます。

 本連載ではSQL Serverで記述しますが、他のRDBでは以下の読み替えをお願いします。

図4 RDBごとの方言(Windows 7 32bit版での確認)
図4 RDBごとの方言(Windows 7 32bit版での確認)

重複値を抽出するためのSQL命令

 例えば、会員テーブル(Member)から、同姓同名で姓が「渡辺」のデータを抽出するSQL命令を考えてみましょう。まず、第1段階として、以下のSQL命令を記述してみます。

リスト1 SQL命令(第1段階)
SELECT ID, Sei, Mei, Adr, Tel FROM Member
ORDER BY Sei, Mei

 結果セットは次のようになります。ORDER句を追加するのは、同姓同名データを隣接させ、名寄せ対象の選択操作を行いやすくするためです(この例はShift_JISコード順)。

表1 SQL命令(第1段階)を実行した結果セット
ID Sei Mei Adr Tel
5 高橋 裕子 神奈川県横浜市 045-210-XXXX
9 高橋 裕子 沖縄県那覇市 098-866-XXXX
3 渡辺 京子 愛媛県松山市 089-941-XXXX
2 渡辺 恵美 島根県松江市 0852-22-XXXX
4 渡辺 恵美 静岡県静岡市 042-722-XXXX
1 渡辺 美咲 福島県福島市 024-521-XXXX
6 渡辺 石川県金沢市 054-221-XXXX
7 渡辺 大阪府大阪市 06-6941-XXXX
10 渡辺 鹿児島県鹿児島市 099-286-XXXX
8 沙織 宮崎県宮崎市 0985-24-XXXX

 ここで颯爽と「EXISTS」演算子の登場です。この演算子は、後続のサブクエリーに対する結果セットの有無により論理値(1行でもあればTRUE、なければFALSE)を返します。

 さて、サブクエリーに「SELECT Sei, COUNT(*) FROM Member GROUP BY Sei HAVING COUNT(*) >= 2」と書けば、「高橋|2」「渡辺|7」の2行のみ、結果セットが返ってきます。

 実はこの「EXISTS」演算子、サブクエリーの対象列や集計結果ではなく、結果セットの有無にしか関心がありません。SELECTの対象列には固定値「1」も書け、むしろ効率的なので、「SELECT 1 FROM Member GROUP BY Sei HAVING COUNT(*) >= 2」と書きましょう。

 以上を踏まえ、第2段階として、同姓同名がいないデータを排除する条件を加えます。

リスト2 SQL命令(第2段階)
SELECT ID, Sei, Mei, Adr, Tel FROM Member AS x
WHERE EXISTS
    ( SELECT 1 FROM Member
        WHERE Sei = x.Sei AND Mei = x.Mei
        GROUP BY Sei, Mei HAVING COUNT(*) >= 2 ) 
ORDER BY Sei, Mei

 第1行の末尾に「AS x」を追加し、Memberテーブルに「x」という別名を与えました。

 第2行のEXISTS演算子は、後続のサブクエリーの結果の有無を論理値で返すのでした。

 ここで第4行のWHERE句に注目してください。接頭辞なしのSeiとMeiはサブクエリー内のMemberテーブルの列を表しますが、x.Seiとx.Meiは外側のMemberテーブルの列を表します。

 そして第5行では、同じSeiとMeiのグループ内の件数が複数という条件を加えています。

 ここで「高橋裕子」に絞れば、「x.列」は外側の値なので、以下の命令と同値です。

リスト3 リスト2を「高橋裕子」に絞ったSQL命令
SELECT ID, Sei, Mei, Adr, Tel FROM Member AS x
WHERE Sei = '高橋' AND Mei = '裕子'
AND EXISTS
    ( SELECT 1 FROM Member
        WHERE Sei = '高橋' AND Mei = '裕子'
        GROUP BY Sei, Mei HAVING COUNT(*) >= 2 ) 
ORDER BY Sei, Mei

 「高橋裕子」は2人いるのでHAVING句の条件を満たします。サブクエリーの結果セットとして「1」が2行返るのでEXISTS句はTRUEとなり、表1の第1行と第2行が出力されます。

 1人の「渡辺京子」はHAVING句条件を満たさず、EXISTS句はFALSEで、出力されません。

 第1段階の結果セット(表1)を走査する処理は、下図のようになります。

図5 SQL命令(第2段階)の実行過程と結果セット
図5 SQL命令(第2段階)の実行過程と結果セット

 最終段階として、サブクエリー内のWHERE句に姓が「渡辺」という条件を加えます。

リスト4 SQL命令(最終段階)
SELECT ID, Sei, Mei, Adr, Tel FROM Member AS x
WHERE EXISTS
    ( SELECT 1 FROM Member
        WHERE Sei = x.Sei AND Mei = x.Mei
        AND Sei = '渡辺'
        GROUP BY Sei, Mei HAVING COUNT(*) >= 2 ) 
ORDER BY Sei, Mei

 最終段階での結果セットは以下のようになります。

表2 SQL命令(最終段階)を実行した結果セット
ID Sei Mei Adr Tel
2 渡辺 恵美 島根県松江市 0852-22-XXXX
4 渡辺 恵美 静岡県静岡市 042-722-XXXX
6 渡辺 石川県金沢市 054-221-XXXX
7 渡辺 大阪府大阪市 06-6941-XXXX
10 渡辺 鹿児島県鹿児島市 099-286-XXXX

 なお、条件が重複チェック列以外の場合は、リスト4の第5行を「AND SeiKana='ワタナベ' AND x. SeiKana='ワタナベ'」のように書く必要がありますので注意してください。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:既存のRDBシステムに会員情報の名寄せ機能を追加する

著者プロフィール

  • WINGSプロジェクト 遠藤 存(エンドウ アリ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2018年11月時点での登録メンバは55名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XM...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5