顧客名寄せをSQLで行う方法｜重複IDを統合する実務クエリ

-- customers テーブル
-- customer_id    : 顧客ID（主キー）
-- email          : メールアドレス
-- phone          : 電話番号（ハイフンありなし混在）
-- last_name      : 姓
-- first_name     : 名
-- birth_date     : 生年月日
-- postal_code    : 郵便番号
-- registered_at  : 登録日時
-- channel        : 登録チャネル

-- STEP1: メールアドレスの完全一致グループを特定する

WITH normalized_email AS (
    SELECT
        customer_id,
        registered_at,
        -- 小文字化・前後空白除去で正規化
        LOWER(TRIM(email))  AS normalized_email
    FROM customers
    WHERE email IS NOT NULL
),
email_groups AS (
    SELECT
        normalized_email,
        COUNT(customer_id)                                         AS id_count,
        -- グループ内で最も古い登録日のIDを「代表ID」とする
        MIN(CASE WHEN registered_at = MIN(registered_at) OVER (PARTITION BY normalized_email)
                 THEN customer_id END)
            OVER (PARTITION BY normalized_email)                   AS representative_id,
        -- すべての重複IDをカンマ区切りで並べる
        ARRAY_JOIN(
            ARRAY_AGG(customer_id ORDER BY registered_at ASC)
            OVER (PARTITION BY normalized_email)
        , ',')                                                     AS all_ids
    FROM normalized_email
)
SELECT DISTINCT
    normalized_email,
    id_count,
    representative_id,
    all_ids
FROM email_groups
WHERE id_count > 1  -- 2件以上のIDが同じメアドを持つグループのみ
ORDER BY id_count DESC, normalized_email;

-- STEP2: 電話番号を正規化して重複グループを特定する

WITH normalized_phone AS (
    SELECT
        customer_id,
        registered_at,
        phone,
        -- 数字以外をすべて除去（ハイフン・スペース・括弧など）
        REGEXP_REPLACE(phone, '[^0-9]', '')  AS normalized_phone
    FROM customers
    WHERE phone IS NOT NULL
    AND phone <> ''
),
phone_groups AS (
    SELECT
        normalized_phone,
        COUNT(DISTINCT customer_id)  AS id_count,
        MIN(customer_id)             AS representative_id,
        ARRAY_JOIN(
            ARRAY_AGG(customer_id ORDER BY registered_at ASC)
        , ',')                       AS all_ids
    FROM normalized_phone
    -- 桁数チェック：日本の電話番号は10〜11桁
    WHERE LENGTH(normalized_phone) BETWEEN 10 AND 11
    GROUP BY normalized_phone
    HAVING COUNT(DISTINCT customer_id) > 1
)
SELECT *
FROM phone_groups
ORDER BY id_count DESC;

-- STEP3: 姓名＋生年月日の一致グループを特定する

WITH normalized_name AS (
    SELECT
        customer_id,
        registered_at,
        birth_date,
        -- 姓名を結合・全角スペース除去・半角スペース除去
        REGEXP_REPLACE(
            last_name || first_name,
            '\s',  -- スペース（全角・半角）を除去
            ''
        )  AS normalized_fullname
    FROM customers
    WHERE last_name  IS NOT NULL
    AND first_name   IS NOT NULL
    AND birth_date   IS NOT NULL
),
name_birth_groups AS (
    SELECT
        normalized_fullname,
        birth_date,
        COUNT(DISTINCT customer_id)   AS id_count,
        MIN(customer_id)              AS representative_id,
        ARRAY_JOIN(
            ARRAY_AGG(customer_id ORDER BY registered_at ASC)
        , ',')                        AS all_ids
    FROM normalized_name
    GROUP BY normalized_fullname, birth_date
    HAVING COUNT(DISTINCT customer_id) > 1
)
SELECT *
FROM name_birth_groups
ORDER BY id_count DESC;

-- STEP4: 複数属性スコアリングによる確率的マッチング

WITH normalized AS (
    SELECT
        customer_id,
        LOWER(TRIM(email))                       AS norm_email,
        REGEXP_REPLACE(phone, '[^0-9]', '')      AS norm_phone,
        REGEXP_REPLACE(last_name || first_name, '\s', '')  AS norm_name,
        birth_date,
        postal_code
    FROM customers
),
-- 全顧客ペアを生成（計算コストが高いため件数が多い場合は要注意）
candidate_pairs AS (
    SELECT
        a.customer_id  AS id_a,
        b.customer_id  AS id_b,

        -- 各属性の一致スコア（一致で加点）
        CASE WHEN a.norm_email    = b.norm_email
             AND a.norm_email IS NOT NULL         THEN 40 ELSE 0 END  AS email_score,
        CASE WHEN a.norm_phone    = b.norm_phone
             AND a.norm_phone IS NOT NULL         THEN 30 ELSE 0 END  AS phone_score,
        CASE WHEN a.norm_name     = b.norm_name
             AND a.norm_name IS NOT NULL          THEN 20 ELSE 0 END  AS name_score,
        CASE WHEN a.birth_date    = b.birth_date
             AND a.birth_date IS NOT NULL         THEN 20 ELSE 0 END  AS birth_score,
        CASE WHEN a.postal_code   = b.postal_code
             AND a.postal_code IS NOT NULL        THEN 10 ELSE 0 END  AS postal_score
    FROM normalized  a
    JOIN normalized  b
        ON a.customer_id < b.customer_id  -- 重複ペアを防ぐ
)
SELECT
    id_a,
    id_b,
    email_score,
    phone_score,
    name_score,
    birth_score,
    postal_score,
    -- 合計スコア
    (email_score + phone_score + name_score + birth_score + postal_score)  AS total_score,
    -- 判定
    CASE
        WHEN (email_score + phone_score + name_score + birth_score + postal_score) >= 60
            THEN '同一人物の可能性が高い（要確認）'
        WHEN (email_score + phone_score + name_score + birth_score + postal_score) >= 40
            THEN '同一人物の可能性あり（要目視）'
        ELSE
            '別人の可能性が高い'
    END  AS match_judgment
FROM candidate_pairs
WHERE (email_score + phone_score + name_score + birth_score + postal_score) >= 40
ORDER BY total_score DESC;

-- STEP5: 名寄せマッピングテーブルの作成

CREATE TABLE customer_id_mapping AS

-- メール一致グループ
WITH email_match AS (
    SELECT
        customer_id                                              AS old_id,
        FIRST_VALUE(customer_id) OVER (
            PARTITION BY LOWER(TRIM(email))
            ORDER BY registered_at ASC
        )                                                       AS representative_id,
        'email_match'                                           AS match_type
    FROM customers
    WHERE email IS NOT NULL
),
-- 電話一致グループ（メール一致で既に統合されたIDを除く）
phone_match AS (
    SELECT
        c.customer_id                                           AS old_id,
        FIRST_VALUE(c.customer_id) OVER (
            PARTITION BY REGEXP_REPLACE(c.phone, '[^0-9]', '')
            ORDER BY c.registered_at ASC
        )                                                       AS representative_id,
        'phone_match'                                           AS match_type
    FROM customers  c
    WHERE c.phone IS NOT NULL
    AND c.customer_id NOT IN (
        SELECT old_id FROM email_match WHERE old_id <> representative_id
    )
)
-- マッピングを統合（old_id = representative_id のものは名寄せ不要）
SELECT
    old_id,
    representative_id,
    match_type,
    old_id <> representative_id  AS is_merged,  -- 統合されたかどうか
    CURRENT_DATE                  AS created_at
FROM email_match
WHERE old_id <> representative_id  -- 代表ID以外の重複IDのみ出力

UNION ALL

SELECT
    old_id,
    representative_id,
    match_type,
    old_id <> representative_id,
    CURRENT_DATE
FROM phone_match
WHERE old_id <> representative_id;

-- 名寄せ後のLTV計算（マッピングテーブルを介して代表IDに集約する）

SELECT
    COALESCE(m.representative_id, o.customer_id)  AS unified_customer_id,
    SUM(o.total_amount)                            AS total_ltv
FROM orders               o
LEFT JOIN customer_id_mapping  m  ON o.customer_id = m.old_id
WHERE o.status = 'completed'
GROUP BY COALESCE(m.representative_id, o.customer_id)
ORDER BY total_ltv DESC;