名寄せ処理について考える

個人用メモです。

突合概要

• 各エンティティごとに、突合のためのmapテーブルを作成する。

• mapテーブルには下記に該当するプロパティを、後述の突合データ用クレンジングルールに従って変換したものを保持する。

  エンティティ主キー、顧客名、住所、電話番号、メールアドレス
  

• 名寄せを行いたいエンティティの情報を元に、mapテーブルの各プロパティに対して突合を行う。

1項目突合

• 電話番号
• メールアドレス

2項目突合

• 顧客名、住所

名寄せ用データクレンジング関数を定義する

• dwh_util.cleansing_person()
• dwh_util.cleansing_address()
• dwh_util.cleansing_tel()

突合データ用クレンジングルール

基本 (メールアドレスを除く)

• 全角・半角英小文字を半角英大文字に変換。
• 全角・半角スペースを除去。
• ハイフン・横棒を除去。
• 半角カナを全角カナに変換。
• 全角数字を半角数字に変換。
• 全角・半角丸括弧を除去。
• 全角・半角中黒を除去。

住所

• 漢数字(〇〜九)を半角数字に変換。

• 以下のキーワードを除外。

  丁目、番地、番、号室、号
  

人名

• 基本ルールのみ適用

電話番号

• 基本ルールのみ適用

メールアドレス

• なし

突合データクレンジングのテスト

テストデータ

準備

• テスト用のテーブル+表記ゆれレコードを複数行作成。

-- テーブル作成
DROP TABLE IF EXISTS dwh_util.test_cleansing;
CREATE TABLE dwh_util.test_cleansing (
	id			INTEGER(11)		NOT NULL AUTO_INCREMENT,
	test_key	VARCHAR(10)		DEFAULT NULL,
	test_value	VARCHAR(100)	DEFAULT NULL,
	PRIMARY KEY (`id`)
);

-- レコード作成
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','田中 小太郎');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','田中　小太郎');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','田中小太郎');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','トーマス・ヴィクトリア・ウィリアムズ');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','ﾄｰﾏｽ･ｳﾞｨｸﾄﾘｱ･ｳｨﾘｱﾑｽﾞ');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','トーマスヴィクトリアウィリアムズ');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','thomaswilliamsoneal');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','Thomas Williams O\'neal');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('person','Ｔｈｏｍａｓ　Ｗｉｌｌｉａｍｓ Ｏ’ｎｅａｌ');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','090-1234-1234');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','090ー1234ー1234');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','０９０−１２３４−１２３４');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','09012341234');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','090(1234)1234');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('tel','090 1234 1234');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('address','三重県四日市市五日町3丁目21番4号四日市ハイツC-201');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('address','三重県四日市市五日町三丁目21番4号 四日市ハイツC-201');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('address','三重県四日市市五日町３−２１−４　四日市ハイツＣ２０１');
INSERT INTO dwh_util.test_cleansing (test_key,test_value) VALUES ('address','三重県四日市市五日町３-２１-４　四日市ハイツＣ二〇一');

テスト実施

• 変換前後の値を取得

SELECT              
	id,
	test_key,
	test_value,
	(
		CASE
			WHEN test_key    = 'person'
			THEN cleansing_person(test_value)
			WHEN test_key    = 'tel'
			THEN cleansing_tel(test_value)
			WHEN test_key    = 'address'
			THEN cleansing_address(test_value)
		END
	)                                AS test_result
FROM
	dwh_util.test_cleansing;

結果

• けっこうイケてる！！！

id	test_key	test_value	test_result
1	person	田中 小太郎	田中小太郎
2	person	田中　小太郎	田中小太郎
3	person	田中小太郎	田中小太郎
4	person	トーマス・ヴィクトリア・ウィリアムズ	トマスヴィクトリアウィリアムズ
5	person	ﾄｰﾏｽ･ｳﾞｨｸﾄﾘｱ･ｳｨﾘｱﾑｽﾞ	トマスヴィクトリアウィリアムズ
6	person	トーマスヴィクトリアウィリアムズ	トマスヴィクトリアウィリアムズ
7	person	thomaswilliamsoneal	THOMASWILLIAMSONEAL
8	person	Thomas Williams O'neal	THOMASWILLIAMSONEAL
9	person	Ｔｈｏｍａｓ　Ｗｉｌｌｉａｍｓ Ｏ’ｎｅａｌ	THOMASWILLIAMSONEAL
10	tel	090-1234-1234	09012341234
11	tel	090ー1234ー1234	09012341234
12	tel	０９０−１２３４−１２３４	09012341234
13	tel	09012341234	09012341234
14	tel	090(1234)1234	09012341234
15	tel	090 1234 1234	09012341234
16	address	三重県四日市市五日町3丁目21番4号四日市ハイツC-201	3重県4日市市5日町32144日市ハイツC201
17	address	三重県四日市市五日町三丁目21番4号 四日市ハイツC-201	3重県4日市市5日町32144日市ハイツC201
18	address	三重県四日市市五日町３−２１−４　四日市ハイツＣ２０１	3重県4日市市5日町32144日市ハイツC201
19	address	三重県四日市市五日町３-２１-４　四日市ハイツＣ二〇一	3重県4日市市5日町32144日市ハイツC201