SQL

Standard SQL CookBook - 6/N

異なるサービスの会員データを複数カラムをマッチさせて名前寄せをする

公開日: 2022-07-17
更新日: 2022-09-01

  概要
目的 異なるサービスの会員データを複数カラムをマッチさせて名前寄せをする
分類 SQL Cookbook
SQL Standard SQL
環境 BigQuery

Table of Contents

問題設定

とある会社Aではこれまで店頭販売のみで自社商品を売っていたところ, 新たに自社ECサイトを立ち上げることになったとします. そのECサイトで消費者がショッピングするためには, 電話番号と誕生日を入力して, EC会員を作成する必要があるとします. このような会員制度は店頭でも実施しており, 同じく電話番号と誕生日を入力して作成されていたとします.

このとき, とある分析プロジェクトの流れの中で以下のことを見たいとなったとします：

  • EC会員のうち, 本日時点で元々店舗で会員登録していた人はどのくらいいたのだろうか？
  • EC会員番号と店舗会員番号の名前寄せはできないか？

EC会員マスターデータ例

ec_member_idがPKとして, (ec_member_id, main_tel, sub_tel, birthday, regist_date)のレコードからなるデータが以下のように与えられるとします:

1
2
3
4

(123456, 08011112222,  0289111222, 1980-01-01, 2022-01-01),
(123457, 08011112223,  0289111222, 1981-01-01, 2022-02-01)
(123458,  0801111232, 02891121122, 1982-01-01, 2022-03-01)
(123459, 08011112243,        NULL, 1983-01-01, 2022-04-01)
  • ec_member_id: PK, NOT NULL, STRING
  • main_tel: NOT NULL, STRING
  • sub_tel: NULLABLE, STRING
  • birthday: NOT NULL, DATE
  • regist_data: NOT NULL, DATE

EC会員作成には少なくとも１つの電話番号と誕生日の入力が必須のため, main_telとbirthdayはNOT NULLとなります. なお, 登録する電話番号は固定電話でも携帯電話でもどちらでも良いとします.

REMARKS

  • (main_tel, birthday)は会員間で重複がないものとする
  • sub_telは過去に登録されものものも会員登録に使用できる設計とする = main_telと同じものを入力してもよいし, 実家の連絡先を入力する人もいるとする

店舗会員マスターデータ例

shop_member_idがPKとして, (shop_member_id, main_tel, sub_tel, birthday, regist_date)のレコードからなるデータが以下のように与えられるとします:

1
2
3
4

(SB123456, 08011112222,  0289111222, 1980-01-01, 2022-01-01),
(SB123457,  0289111222,        NULL, 1980-01-01, 2022-02-01)
(SB123458,  0801111232, 02891121122, 1982-01-01, 2022-03-01)
(SB123459, 09011112243, 08011112243, 1983-01-01, 2022-04-01)
  • shop_member_id: PK, NOT NULL, STRING
  • main_tel: NOT NULL, STRING
  • sub_tel: NULLABLE, STRING
  • birthday: NOT NULL, DATE
  • regist_data: NOT NULL, DATE

EC会員と同じく店舗会員作成と同様のものを想定します.

目指すもの

あくまでEC会員IDを起点として, 登録電話番号と登録生年月日ベースの両方がマッチする店舗会員IDの名寄せをしたいとします.

成果物イメージ

ec_member_id(PK) shop_member_id_list 生年月日
123456 {SB123456} 1980-01-01
… … …
  • ec_member_idがPK
  • 対応するshop_member_idはリストとして格納する

上のデータ例と合わせると以下のクエリが作れたら勝ちとします:

1
2
3
4

(123456, {SB123456, SB123457}, 1980-01-01),
(123457, {NULL},               1981-01-01),
(123458, {SB123458},           1982-01-01),
(123459, {SB123459},           1983-01-01)

なぜ店舗会員IDをリストで格納するのか？

shop_member_idをリストとして持つ理由は, EC_member_id登録時に携帯電話と固定電話両方を入力している人がいた時, 同じくその人が店舗会員IDを登録する時, (固定電話,生年月日)と(携帯電話,生年月日)で２回登録 = 同じ人だけど複数の店舗会員IDを持っている可能性があるためです.

Assumptions

  • 消費者が電話番号をシェアする際は, 同一世帯の人と仮定
  • 同一世帯の中に同じ誕生日の人はいない

Limitations

  • 同一世帯の中の同じ誕生日かつ固定電話を登録に使った人(例:双子)は区別できないものとする
  • EC会員ID間の名寄せは実行しないとする

SQL実行例の紹介

データ構築クエリ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

--- ec_member_table
SELECT
    *
FROM 
    UNNEST([STRUCT<ec_member_id STRING, main_tel STRING, sub_tel STRING, birthday DATE, regist_date DATE>
            ('123456', '08011112222', '0289111222', '1980-01-01', '2022-01-01'),
            ('123457', '08011112223', '0289111222', '1981-01-01', '2022-02-01'),
            ('123458', '0801111232', '02891121122', '1982-01-01', '2022-03-01'),
            ('123459', '08011112243',        NULL, '1983-01-01', '2022-04-01')
        ]);

--- shop_member_table
SELECT
    *
FROM 
    UNNEST([STRUCT<shop_member_id STRING, main_tel STRING, sub_tel STRING, birthday DATE, regist_date DATE>
            ('SB123456', '08011112222',  '0289111222', '1980-01-01', '2022-01-01'),
            ('SB123457',  '0289111222',        NULL, '1980-01-01', '2022-02-01'),
            ('SB123458',  '0801111232', '02891121122', '1982-01-01', '2022-03-01'),
            ('SB123459', '09011112243', '08011112243', '1983-01-01', '2022-04-01')
        ]);

名寄実行クエリ

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

WITH
    ec_member_table AS(
        SELECT
            *
        FROM 
            UNNEST([STRUCT<ec_member_id STRING, main_tel STRING, sub_tel STRING, birthday DATE, regist_date DATE>
                    ('123456', '08011112222', '0289111222', '1980-01-01', '2022-01-01'),
                    ('123457', '08011112223', '0289111222', '1981-01-01', '2022-02-01'),
                    ('123458', '0801111232', '02891121122', '1982-01-01', '2022-03-01'),
                    ('123459', '08011112243',        NULL, '1983-01-01', '2022-04-01')
                ])
    ),
    shop_member_table AS(
        SELECT
            *
        FROM 
            UNNEST([STRUCT<shop_member_id STRING, main_tel STRING, sub_tel STRING, birthday DATE, regist_date DATE>
                    ('SB123456', '08011112222',  '0289112222', '1980-01-01', '2022-01-01'),
                    ('SB123457',  '0289111222',        NULL, '1980-01-01', '2022-02-01'),
                    ('SB123458',  '0801111232', '02891121122', '1982-01-01', '2022-03-01'),
                    ('SB123459', '09011112243', '08011112243', '1983-01-01', '2022-04-01')
                ])
    ),
    ec_member_table_unnest AS(
        SELECT
            ec_member_id,
            main_tel AS tel_key,
            birthday
        FROM
            ec_member_table
        UNION DISTINCT
        SELECT
            ec_member_id,
            sub_tel AS tel_key,
            birthday
        FROM
            ec_member_table
    ),
    shop_member_table_unnest AS(
        SELECT
            shop_member_id,
            main_tel AS tel_key,
            birthday
        FROM
            shop_member_table
        UNION DISTINCT
        SELECT
            shop_member_id,
            sub_tel AS tel_key,
            birthday
        FROM
            shop_member_table
    )
SELECT
    emtu.ec_member_id,
    emtu.birthday,
    ARRAY_AGG(DISTINCT smtu.shop_member_id IGNORE NULLS) AS shop_member_list
FROM
    ec_member_table_unnest AS emtu
    LEFT OUTER JOIN shop_member_table_unnest AS smtu
        ON emtu.tel_key  = smtu.tel_key
       AND emtu.birthday = smtu.birthday
GROUP BY
    1, 2

Then,

1
2
3
4
5

ec_member_id	birthday	shop_member_list
123456	1980-01-01	"[SB123456,SB123457]"
123457	1981-01-01	[]
123458	1982-01-01	[SB123458]
123459	1983-01-01	[SB123459]

ARRAY_AGGの注意点

DISTINCT optionについて

DISTINCT optionをつけない場合, 複数レコードにマッチした場合その時の値がそのままARRAY_AGGの中に残ります：

1
2
3
4
5

ec_member_id	birthday	shop_member_list
123456	1980-01-01	"[SB123456,SB123457]"
123457	1981-01-01	[]
123458	1982-01-01	"[SB123458,SB123458]" <-- ここが重複している
123459	1983-01-01	[SB123459]

IGNORE NULLS optionについて

ARRAY_AGGで集計対象をarrayに集約する際, 集計対象内にNULLがあると、以下のエラーが発生します:

1

Array cannot have a null element; error in writing field ids

上記名寄せ例ではLEFT OUTER JOINでテーブルを結合させているため, 上記のエラーの発生は高い確率で発生します. これを回避するオプションとして, IGNORE NULLS optionを用いています.

References

オンラインマテリアル

Share Buttons
 Share on:
Feature Tags
Leave a Comment
(注意：GitHub Accountが必要となります）
CONTENTS