MySQL

 

MySQLで名寄せする

MySQLで名寄せをします。ストアドプロシージャを使い、電話番号またはメールアドレスが同一の人をグループ分けします。ソースコード一式も掲載しています。

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告

最初に断っておくと、この記事は長いので、気を楽にして読んで下さい。

前半はテーブルで名寄せされていく過程を示し、後半はソースコード一式を掲載しています。

今回行う名寄せは、「電話番号」または「メールアドレス」のどちらかが同じ場合、同一人物とみなします。

ただし、都道府県が異なる場合は同一人物とみなしません。

名寄せするログデータは以下とします。

メールアドレスはドメイン部分を微妙に変えています。ログ日時は全て同じにしています

都道府県コード1と都道府県コード2のデータは全く同じです。

電話番号またはメールアドレスのどちらかは必ずデータが入っているものとします

ログテーブル
id都道府県コード電話番号メールアドレスログ日時
11111-111-111aaa@gmail.com2013-02-01 00:00:00
21111-111-111bbb@gmail.com2013-02-01 00:00:00
31111-111-111ccc@gmail.com2013-02-01 00:00:00
41222-222-222aaa@gmail.com2013-02-01 00:00:00
51222-222-222bbb@gmail.com2013-02-01 00:00:00
61222-222-222ccc@gmail.com2013-02-01 00:00:00
71333-333-333aaa@gmail.com2013-02-01 00:00:00
81333-333-333bbb@gmail.com2013-02-01 00:00:00
91333-333-333ccc@gmail.com2013-02-01 00:00:00
101NULLaaa@gmail.com2013-02-01 00:00:00
111NULLbbb@gmail.com2013-02-01 00:00:00
121NULLccc@gmail.com2013-02-01 00:00:00
131111-111-111NULL2013-02-01 00:00:00
141222-222-222NULL2013-02-01 00:00:00
151333-333-333NULL2013-02-01 00:00:00
161444-444-444aaa@yahoo.com2013-02-01 00:00:00
171555-555-555bbb@yahoo.com2013-02-01 00:00:00
181666-666-666ccc@yahoo.com2013-02-01 00:00:00
191777-777-777NULL2013-02-01 00:00:00
201888-888-888NULL2013-02-01 00:00:00
211999-999-999NULL2013-02-01 00:00:00
221NULLaaa@bing.com2013-02-01 00:00:00
231NULLbbb@bing.com2013-02-01 00:00:00
241NULLccc@bing.com2013-02-01 00:00:00
492111-111-111aaa@gmail.com2013-02-01 00:00:00
502111-111-111bbb@gmail.com2013-02-01 00:00:00
512111-111-111ccc@gmail.com2013-02-01 00:00:00
522222-222-222aaa@gmail.com2013-02-01 00:00:00
532222-222-222bbb@gmail.com2013-02-01 00:00:00
542222-222-222ccc@gmail.com2013-02-01 00:00:00
552333-333-333aaa@gmail.com2013-02-01 00:00:00
562333-333-333bbb@gmail.com2013-02-01 00:00:00
572333-333-333ccc@gmail.com2013-02-01 00:00:00
582NULLaaa@gmail.com2013-02-01 00:00:00
592NULLbbb@gmail.com2013-02-01 00:00:00
602NULLccc@gmail.com2013-02-01 00:00:00
612111-111-111NULL2013-02-01 00:00:00
622222-222-222NULL2013-02-01 00:00:00
632333-333-333NULL2013-02-01 00:00:00
642444-444-444aaa@yahoo.com2013-02-01 00:00:00
652555-555-555bbb@yahoo.com2013-02-01 00:00:00
662666-666-666ccc@yahoo.com2013-02-01 00:00:00
672777-777-777NULL2013-02-01 00:00:00
682888-888-888NULL2013-02-01 00:00:00
692999-999-999NULL2013-02-01 00:00:00
702NULLaaa@bing.com2013-02-01 00:00:00
712NULLbbb@bing.com2013-02-01 00:00:00
722NULLccc@bing.com2013-02-01 00:00:00

ログテーブルのデータを、ログ日時で絞って都道府県毎に処理します。

都道府県コード=1 → 名寄せ → 都道府県コード=2 → 名寄せ → ・・・ループ

まずはログテーブルから、2013/02/01のデータで都道府県コード=1のデータをグループテーブルにinsertします。

ID・都道府県コード・ログ日時は省いています。ロググループテーブルにはグループIDカラムがあります。

(省いている理由は、説明するのに特に必要の無い項目だからです。)

なお、現在のデータは全てグループID=0です。

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com0
111-111-111bbb@gmail.com0
111-111-111ccc@gmail.com0
222-222-222aaa@gmail.com0
222-222-222bbb@gmail.com0
222-222-222ccc@gmail.com0
333-333-333aaa@gmail.com0
333-333-333bbb@gmail.com0
333-333-333ccc@gmail.com0
NULLaaa@gmail.com0
NULLbbb@gmail.com0
NULLccc@gmail.com0
111-111-111NULL0
222-222-222NULL0
333-333-333NULL0
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

これから同一人物を探しだして、同一のグループIDを振っていきます

ループ処理をするため、まずはランダム1件のグループIDを1に更新します。

以下のデータを基準に名寄せを開始します。

オレンジ背景色は基準データ黄色背景色は新たにグループID=1になったデータです。

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com0
111-111-111ccc@gmail.com0
222-222-222aaa@gmail.com0
222-222-222bbb@gmail.com0
222-222-222ccc@gmail.com0
333-333-333aaa@gmail.com0
333-333-333bbb@gmail.com0
333-333-333ccc@gmail.com0
NULLaaa@gmail.com0
NULLbbb@gmail.com0
NULLccc@gmail.com0
111-111-111NULL0
222-222-222NULL0
333-333-333NULL0
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

まずは電話番号から名寄せします。

グループID=1のデータと同じ電話番号のデータを全てブループID=1で更新します。

オレンジ背景色は基準データ黄色背景色は新たにグループID=1になったデータです。

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com1
111-111-111ccc@gmail.com1
222-222-222aaa@gmail.com0
222-222-222bbb@gmail.com0
222-222-222ccc@gmail.com0
333-333-333aaa@gmail.com0
333-333-333bbb@gmail.com0
333-333-333ccc@gmail.com0
NULLaaa@gmail.com0
NULLbbb@gmail.com0
NULLccc@gmail.com0
111-111-111NULL1
222-222-222NULL0
333-333-333NULL0
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

続いてメールアドレスを名寄せします。

グループID=1のデータと同じメールアドレスのデータを全てブループID=1で更新します。

オレンジ背景色は基準データ黄色背景色は新たにグループID=1になったデータです。

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com1
111-111-111ccc@gmail.com1
222-222-222aaa@gmail.com1
222-222-222bbb@gmail.com1
222-222-222ccc@gmail.com1
333-333-333aaa@gmail.com1
333-333-333bbb@gmail.com1
333-333-333ccc@gmail.com1
NULLaaa@gmail.com1
NULLbbb@gmail.com1
NULLccc@gmail.com1
111-111-111NULL1
222-222-222NULL0
333-333-333NULL0
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

電話番号111-111-111は実は沢山のメールアドレスと同一人部であることが解りましたね。

さて、もう一度基準データを元に電話番号を名寄せします。

メールアドレス視点で見ると、同一人物が沢山いることが解りました。

メールアドレス視点で新たに解った電話番号を、更に名寄せします。

オレンジ背景色は基準データ黄色背景色は新たにグループID=1になったデータです。

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com1
111-111-111ccc@gmail.com1
222-222-222aaa@gmail.com1
222-222-222bbb@gmail.com1
222-222-222ccc@gmail.com1
333-333-333aaa@gmail.com1
333-333-333bbb@gmail.com1
333-333-333ccc@gmail.com1
NULLaaa@gmail.com1
NULLbbb@gmail.com1
NULLccc@gmail.com1
111-111-111NULL1
222-222-222NULL1
333-333-333NULL1
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0
ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com1
111-111-111ccc@gmail.com1
222-222-222aaa@gmail.com1
222-222-222bbb@gmail.com1
222-222-222ccc@gmail.com1
333-333-333aaa@gmail.com1
333-333-333bbb@gmail.com1
333-333-333ccc@gmail.com1
NULLaaa@gmail.com1
NULLbbb@gmail.com1
NULLccc@gmail.com1
111-111-111NULL1
222-222-222NULL1
333-333-333NULL1
444-444-444aaa@yahoo.com0
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

現在のデータを見てみましょう。

改めて見ると、以下は全て同一グループ、つまり全て同一人物なのです

ロググループテーブル
電話番号メールアドレスグループID
111-111-111aaa@gmail.com1
111-111-111bbb@gmail.com1
111-111-111ccc@gmail.com1
222-222-222aaa@gmail.com1
222-222-222bbb@gmail.com1
222-222-222ccc@gmail.com1
333-333-333aaa@gmail.com1
333-333-333bbb@gmail.com1
333-333-333ccc@gmail.com1
NULLaaa@gmail.com1
NULLbbb@gmail.com1
NULLccc@gmail.com1
111-111-111NULL1
222-222-222NULL1
333-333-333NULL1

グループ1の処理が全て完了したので、次はグループ2を作っていきます。

手順は1グループ目と全く同じ事をするだけです。

唯一異なるのは、グループIDが2になったことだけです。

ロググループテーブル
電話番号メールアドレスグループID
444-444-444aaa@yahoo.com2
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0
ロググループテーブル
電話番号メールアドレスグループID
444-444-444aaa@yahoo.com2
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

名寄せできるデータはありませんね。

ロググループテーブル
電話番号メールアドレスグループID
444-444-444aaa@yahoo.com2
555-555-555bbb@yahoo.com0
666-666-666ccc@yahoo.com0
777-777-777NULL0
888-888-888NULL0
999-999-999NULL0
NULLaaa@bing.com0
NULLbbb@bing.com0
NULLccc@bing.com0

名寄せできるデータはありませんね。

名寄せできるデータが無かったので、グループ2のデータはこの1件のみだった、という事になります。

後はループ処理でグループID=0が無くなるまで同じ事を繰り返します。

用意したサンプルデータは、以降は全て名寄せできないデータになっています。

ループと処理の概要は以下の通りです。

while 都道府県 do
    while グループが無くなるまで do
        電話番号で名寄せ     :1回目;
        メールアドレスで名寄せ:1回目;
        電話番号で名寄せ     :2回目;
        メールアドレスで名寄せ:2回目;
    end while;
end while;

こんな漢字で名寄せをし、都道府県毎にユニークユーザが何人いるか数える事ができます。

次項でソースコード一式を載せたので、そちらを見ると更に解るかと思います。

-- ログテーブル
drop table if exists log;
create table log (
    id bigint unsigned auto_increment
    ,pref_cd int
    ,tel varchar(20)
    ,email varchar(100)
    ,log_date datetime not null
    ,primary key(id)
) engine=innodb charset=utf8mb4;
alter table log add key idx1 (pref_cd, tel, email, log_date);

-- ログのグルーピング用テーブル
drop table if exists log_group;
create table log_group like log;
alter table log_group add group_id int default 0;
insert into log (pref_cd, tel, email, log_date) values
(1, '111-111-111', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(1, '111-111-111', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(1, '111-111-111', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(1, '222-222-222', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(1, '222-222-222', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(1, '222-222-222', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(1, '333-333-333', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(1, '333-333-333', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(1, '333-333-333', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(1, null         , 'aaa@gmail.com', '2013-02-01 00:00:00'),
(1, null         , 'bbb@gmail.com', '2013-02-01 00:00:00'),
(1, null         , 'ccc@gmail.com', '2013-02-01 00:00:00'),
(1, '111-111-111', null           , '2013-02-01 00:00:00'),
(1, '222-222-222', null           , '2013-02-01 00:00:00'),
(1, '333-333-333', null           , '2013-02-01 00:00:00'),
(1, '444-444-444', 'aaa@yahoo.com', '2013-02-01 00:00:00'),
(1, '555-555-555', 'bbb@yahoo.com', '2013-02-01 00:00:00'),
(1, '666-666-666', 'ccc@yahoo.com', '2013-02-01 00:00:00'),
(1, '777-777-777', null           , '2013-02-01 00:00:00'),
(1, '888-888-888', null           , '2013-02-01 00:00:00'),
(1, '999-999-999', null           , '2013-02-01 00:00:00'),
(1, null         , 'aaa@bing.com' , '2013-02-01 00:00:00'),
(1, null         , 'bbb@bing.com' , '2013-02-01 00:00:00'),
(1, null         , 'ccc@bing.com' , '2013-02-01 00:00:00'),

(2, '111-111-111', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(2, '111-111-111', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(2, '111-111-111', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(2, '222-222-222', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(2, '222-222-222', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(2, '222-222-222', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(2, '333-333-333', 'aaa@gmail.com', '2013-02-01 00:00:00'),
(2, '333-333-333', 'bbb@gmail.com', '2013-02-01 00:00:00'),
(2, '333-333-333', 'ccc@gmail.com', '2013-02-01 00:00:00'),
(2, null         , 'aaa@gmail.com', '2013-02-01 00:00:00'),
(2, null         , 'bbb@gmail.com', '2013-02-01 00:00:00'),
(2, null         , 'ccc@gmail.com', '2013-02-01 00:00:00'),
(2, '111-111-111', null           , '2013-02-01 00:00:00'),
(2, '222-222-222', null           , '2013-02-01 00:00:00'),
(2, '333-333-333', null           , '2013-02-01 00:00:00'),
(2, '444-444-444', 'aaa@yahoo.com', '2013-02-01 00:00:00'),
(2, '555-555-555', 'bbb@yahoo.com', '2013-02-01 00:00:00'),
(2, '666-666-666', 'ccc@yahoo.com', '2013-02-01 00:00:00'),
(2, '777-777-777', null           , '2013-02-01 00:00:00'),
(2, '888-888-888', null           , '2013-02-01 00:00:00'),
(2, '999-999-999', null           , '2013-02-01 00:00:00'),
(2, null         , 'aaa@bing.com' , '2013-02-01 00:00:00'),
(2, null         , 'bbb@bing.com' , '2013-02-01 00:00:00'),
(2, null         , 'ccc@bing.com' , '2013-02-01 00:00:00')
;
set names utf8mb4;
delimiter //

-- ######################################################
-- 名寄せします
-- ######################################################
drop procedure if exists nayose;
create procedure nayose (
    in p_log_date_from datetime
    ,in p_log_date_to datetime
)
begin

declare p_pref_done int default 1;
declare p_pref_cd int;
declare p_group_done int default 1;
declare p_group_id int default 1;

declare cur cursor for
    select
            pref_cd
        from
            log
        where
            log_date >= p_log_date_from
            and log_date < p_log_date_to
        group by
            pref_cd
;
declare exit handler for not found set p_pref_done = 0;

-- 初期化
truncate log_group;

-- ======================================================
-- 都道府県ごとに名寄せする
-- ======================================================
open cur;
while p_pref_done do

    set p_group_id = 0;

    fetch cur into p_pref_cd;

    -- 可能な限り対象データを減らして検索速度を上げる
    insert into log_group
    select
            *, 0
        from
            log
        where
            pref_cd = p_pref_cd
            and log_date >= p_log_date_from
            and log_date < p_log_date_to
    ;

    -- ======================================================
    -- グループが無くなるまでループする
    -- ======================================================
    set p_group_done = 1;
    while p_group_done do

        -- 処理開始データを1件抽出する
        update log_group set group_id = p_group_id where pref_cd = p_pref_cd and group_id = 0 limit 1;

        -- 電話番号で名寄せ:1回目
        update
                log_group t1
                inner join log_group t2
                    on t2.group_id = p_group_id
                    and t1.tel = t2.tel
            set
                t1.group_id = p_group_id
            where
                t1.pref_cd = p_pref_cd
                and t1.group_id = 0
        ;

        -- メールアドレスで名寄せ:1回目
        update
                log_group t1
                inner join log_group t2
                    on t2.group_id = p_group_id
                    and t1.email = t2.email
            set
                t1.group_id = p_group_id
            where
                t1.pref_cd = p_pref_cd
                and t1.group_id = 0
        ;

        -- 電話番号で名寄せ:2回目
        update
                log_group t1
                inner join log_group t2
                    on t2.group_id = p_group_id
                    and t1.tel = t2.tel
            set
                t1.group_id = p_group_id
            where
                t1.pref_cd = p_pref_cd
                and t1.group_id = 0
        ;

        -- メールアドレスで名寄せ:2回目
        update
                log_group t1
                inner join log_group t2
                    on t2.group_id = p_group_id
                    and t1.email = t2.email
            set
                t1.group_id = p_group_id
            where
                t1.pref_cd = p_pref_cd
                and t1.group_id = 0
        ;

        -- グループ未設定が無くなったらループ終了
        select count(*) into p_group_done from log_group where pref_cd = p_pref_cd and group_id = 0;

        if p_group_done != 0 then
            set p_group_id = p_group_id + 1;
        end if;

    end while;

end while;

close cur;

end
//

log_groupテーブルにデータを蓄積していってますが、処理が完了したグループは即別テーブルに結果を移し、log_groupテーブルからdeleteした方がパフォーマンス的にいいかと思います。

引数に開始日時と終了日時を指定できるので、日時・週次・月次・年次の集計が可能です。

mysql> call nayose ('2013-02-01 00:00:00', '2013-02-02 00:00:00');
Query OK, 0 rows affected (0.05 sec)

名寄せ実行後のロググループテーブルを元にカウントします。

従って、事前にストアドプロシージャを実行しておく必要があります。

select
        pref_cd
        ,count(*)
    from (
        select
                pref_cd
            from
                log_group
            group by
                pref_cd
                ,group_id
        ) tmp
    group by
	    pref_cd
;
+---------+----------+
| pref_cd | count(*) |
+---------+----------+
|       1 |       10 |
|       2 |       10 |
+---------+----------+
2 rows in set (0.00 sec)
treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告