データクレンジングの実務――重複排除・名寄せ・欠損値処理の定石

データクレンジングとは

データクレンジングとは、分析や機械学習に利用できない「汚れたデータ」を特定し、修正・除去・補完する工程です。ETLの「変換(Transform)」フェーズの核心部分であり、重複排除・名寄せ・欠損値処理・外れ値対処・型変換・フォーマット統一を含みます。

データサイエンティストが業務時間の60〜80%をデータ準備に費やすという調査結果が示すように、クレンジングは地味ながら最も工数がかかる工程です。しかし、クレンジングを自動化・パターン化することで、この負荷を大幅に削減できます。本記事ではMDM(A-11)・品質管理(D-03)と連携した実践的なクレンジング手法をSQLコード例とともに解説します。

重複排除の手法

重複データはCRM・ERP・ECシステムなどで日常的に発生します。同一顧客が異なるメールアドレスで複数登録されているケース、バッチ処理の重複実行でデータが二重挿入されるケース、複数ソースのデータを結合した際に生じるケースなど、原因は様々です。

ROW_NUMBER()による重複排除(最新レコードを残す)

-- ROW_NUMBER()で重複排除: customer_idが重複する場合、updated_at最新を残す
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM raw_customers
)
SELECT * EXCEPT(rn) FROM ranked WHERE rn = 1;

このパターンは最も汎用性が高く、PostgreSQL・BigQuery・Snowflake・Redshiftすべてで動作します。PARTITION BYに重複判定キーを、ORDER BYに優先基準(最新日時・主キーの大小等)を指定します。

QUALIFY句による重複排除(BigQuery/Snowflake)

-- QUALIFY句: サブクエリなしで記述が短縮できる
SELECT *
FROM raw_customers
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY updated_at DESC
) = 1;

QUALIFY句はBigQueryとSnowflakeで利用可能で、CTEを使わずに1クエリで重複排除が完結します。可読性が高くdbtのモデルに直接記述することが多い構文です。

名寄せの手法

名寄せ(Entity Resolution)は、異なるシステムや表記ゆれで記録された「同一エンティティ」を特定・統合する処理です。「株式会社ABC」と「(株)ABC」と「ABC株式会社」が同一企業であると判定するのが典型例です。MDM(マスターデータ管理)の実装にも密接に関連します。

段階的名寄せSQL例(完全一致→あいまい一致)

-- 段階的名寄せ: メール完全一致 → 電話 → SOUNDEX社名あいまい一致
SELECT
    a.id           AS master_id,
    b.id           AS duplicate_id,
    a.email,
    SOUNDEX(a.company_name) AS soundex_a,
    SOUNDEX(b.company_name) AS soundex_b,
    CASE
        WHEN a.email = b.email THEN 'email_match'
        WHEN a.phone = b.phone THEN 'phone_match'
        ELSE 'fuzzy_name_match'
    END AS match_type
FROM customers a
JOIN customers b ON a.id < b.id
WHERE a.email = b.email
   OR a.phone = b.phone
   OR (a.postal_code = b.postal_code AND SOUNDEX(a.company_name) = SOUNDEX(b.company_name));

名寄せ手法比較

手法	仕組み	精度	速度	適するケース
完全一致	メール・ID等の完全一致	高	高速	多くの重複はこれで解決
SOUNDEX/あいまい一致	発音・編集距離ベース	中	中	氏名・社名の表記ゆれ
ルールベース組み合わせ	複数フィールドの条件組み合わせ	中〜高	中	ドメイン知識がある場合
機械学習(Record Linkage)	特徴量を学習しスコア化	高	低〜中	大規模・複雑な名寄せ

実務では「完全一致→あいまい一致→機械学習」の3段階で精度を上げるアプローチが効果的です。まずメールアドレス・電話番号などの一意キーで完全一致から始め、残った未名寄せレコードにあいまい一致を適用します。それでも残るケースに機械学習や人手確認を適用する段階的アプローチが実践的です。

欠損値処理の手法

欠損パターン	処理方法	適するケース
ビジネス上NULLが許容	NULLのまま保持	未設定項目、オプション項目
デフォルト値が存在する	COALESCE(col, default)	割引率0、地域「Unknown」等
前回値で補完したい	LAST_VALUE IGNORE NULLS	時系列データの欠損補完
データ収集失敗による欠損	レコードを除外	必須フィールドが欠損
外れ値扱いで発生した欠損	中央値・平均値で補完	数値データの異常欠損

COALESCEを使った欠損値デフォルト補完

-- COALESCE: 複数候補から最初のNULLでない値を使う
SELECT
    customer_id,
    COALESCE(mobile_phone, office_phone, '')  AS contact_phone,
    COALESCE(region, country, 'Unknown')      AS region,
    COALESCE(discount_rate, 0.0)              AS discount_rate
FROM raw_customers;

NULLの扱いはビジネスルールに基づいて判断します。「NULLのままにしておく」「デフォルト値で埋める」「前回値で補完する(フォワードフィル)」のどれが適切かは、データの意味と利用目的によって異なります。画一的な処理は避け、カラムごとに処理方針を文書化することを推奨します。

外れ値・異常値の検出と対処

外れ値は統計的に極端な値を持つレコードで、データ入力ミス・センサー誤作動・詐欺データなど様々な原因で発生します。ビジネス上の意味のある外れ値(高額購入顧客等)を誤って除外しないよう、機械的な除外ではなく検出と確認が基本方針です。

主な検出手法として、IQR法(Q1 − 1.5×IQR〜Q3 + 1.5×IQR の範囲外を外れ値とする)、Z-score法(平均から標準偏差3倍以上の乖離)、ドメインルールベース(「注文金額が0以下」「年齢が150以上」等の論理的なあり得ない値)があります。

対処方針は「除外・キャップ処理(上下限クリッピング)・NaN化してから補完・フラグを立てて分析に活用」の4つです。機械学習用途ではキャップ処理が、BIレポート用途ではフラグ付きで保持が一般的です。

クレンジングの自動化パイプライン設計

ソースデータ(raw層)
    │
    ▼ Step 1: 重複排除
    │  ROW_NUMBER() / QUALIFYで最新レコードを特定
    │
    ▼ Step 2: 名寄せ
    │  完全一致 → SOUNDEX → ルールベース → ML
    │
    ▼ Step 3: 欠損値処理
    │  COALESCEでデフォルト補完 / フォワードフィル
    │
    ▼ Step 4: 外れ値検出・対処
    │  IQR / Z-score / ドメインルール
    │
    ▼ Step 5: 型変換・フォーマット統一
    │  DATE型統一 / 電話番号正規化 / 住所表記統一
    │
クレンジング済みデータ(staging / cleaned層)
    │
    ▼ dbt test で品質確認
    │
marts層 → BI・分析

このフローはdbtのstagingモデルとして実装するのが推奨です。各ステップをSQL変換として記述し、dbt tests(D-04)でアウトプットの品質を自動確認します。クレンジングロジックはバージョン管理されたSQLとして保存するため、ロジックの変更追跡と再現性が確保されます。

まとめ

データクレンジングは地道ですが、データ活用の品質を左右する最重要工程です。SQL中心の自動化で工数を大幅に削減できます。

• 重複排除はROW_NUMBER()かQUALIFY句で「最新1件を残す」パターンが基本
• 名寄せは完全一致→あいまい一致→機械学習の3段階で精度を上げる
• 欠損値処理はビジネスルールに基づき、カラムごとに方針を定める
• 外れ値は機械的に除外せず、検出→確認→方針決定のフローを踏む
• dbt stagingモデルとして実装し、テスト自動化で品質を担保する

よくある質問(FAQ)

Q. データクレンジングとETLの違いは?

A. ETLはデータの抽出・変換・格納の全体プロセスで、クレンジングはETLの「変換(Transform)」工程の一部です。具体的には重複排除・名寄せ・欠損値処理・型変換を含みます。クレンジングはパイプラインの変換ステップとして自動化します。

Q. 名寄せの精度を上げるにはどうすればよいですか?

A. 完全一致→あいまい一致→機械学習の3段階で精度を上げるアプローチが効果的です。まずメールアドレスなどの一意キーで完全一致から始め、残った未名寄せレコードにSOUNDEXやLevenshtein距離を適用します。大規模な名寄せにはpython-record-linkageやSpark MLが活用できます。

Q. 欠損値はどう処理すべきですか?

A. ビジネスルールに基づいて判断します。NULLのまま保持・デフォルト値で埋める・前回値で補完(フォワードフィル)など、データの意味に応じて使い分けます。「一律0で埋める」などの機械的な処理は分析結果を歪める可能性があるため、カラムごとに方針を決めて文書化することが重要です。