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

```
個人用メモです。
```

## 突合概要

* 各エンティティごとに、突合のための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 |