三表数据源获取与清洗：从原始数据到可建模的坚实基础
在《三表联动建模完整实战方案》中，数据准备与清洗是整个项目成败的基石。无论后续的关联逻辑设计得多么精妙、模型算法多么先进，如果输入的数据源本身存在缺失、重复、类型混乱等问题，最终的分析结论都将失去可信度。本章将聚焦于三表（通常指销售明细表、产品信息表、区域/客户维度表）的数据源获取策略、缺失值处理、重复值去重以及数据类型统一四大核心环节。我们将从工程实践的角度，构建一套可复用、可验证的数据预处理流水线，确保进入建模阶段的数据是干净、一致且完整的。
上图展示了三表数据清洗的整体流水线。从左侧的数据源获取开始，经过中间的核心清洗步骤（缺失值、重复值、类型统一），再到质量校验，最终输出可直接用于建模的标准化数据集。值得注意的是，整个流程并非单向的线性操作，而是包含多次反馈循环——例如，当质量校验发现外键匹配率过低时，需要回溯到数据源获取阶段检查是否存在编码不一致问题。下面我们将逐一拆解每个环节的具体实施方法。
3.1 三表数据源获取：多源异构数据的统一接入
核心原理：在实际业务场景中，三张表往往来自不同的业务系统。销售明细表可能存储在MySQL数据库中，产品信息表由市场部门维护在Excel文件中，而区域维度表则可能通过第三方API接口实时获取。这种多源异构的特性要求我们建立统一的数据接入层（Data Ingestion Layer），屏蔽底层数据源的差异，向上层提供一致的DataFrame或Table对象。
判断方法与操作流程：获取数据源时，需要遵循以下步骤：
- 数据源盘点：明确三张表的物理位置、访问权限、更新频率和数据量级。例如，销售表每日新增约5万条记录，产品表每周全量更新一次（约2000条），区域表为静态维度表（约300条）。
- 连接方式选择：对于数据库，使用连接池（如HikariCP）获取连接；对于文件，注意文件编码（UTF-8 vs GBK）和分隔符（逗号 vs 制表符）；对于API，设置合理的超时时间和重试机制。
- 增量与全量策略：大表（销售明细）采用增量抽取，以时间戳字段为增量标记；小表（产品、区域）采用全量覆盖策略。
| 数据源 | 存储类型 | 关键字段 | 更新频率 | 接入方式 |
|---|---|---|---|---|
| 销售明细表 | MySQL 8.0 | order_id, product_id, region_id, amount, date | 每日增量 | JDBC + 时间戳增量 |
| 产品信息表 | Excel (.xlsx) | product_id, name, category, price | 每周全量 | Pandas read_excel + dtype指定 |
| 区域维度表 | RESTful API | region_id, region_name, manager | 每月全量 | Requests + JSON解析 |
3.2 缺失值处理：从删除到填充的策略矩阵
核心原理：缺失值处理没有放之四海而皆准的方法，需要根据缺失机制（完全随机缺失MCAR、随机缺失MAR、非随机缺失MNAR）和业务含义来决定策略。对于三表联动建模，我们最关心的是主键（如product_id）和外键（如region_id）的缺失，因为这些字段的缺失将直接导致关联失败。
判断方法与操作流程：建议按照以下决策树处理：
- 主键缺失：直接删除该记录，因为无法参与关联。
- 外键缺失：先尝试通过其他字段推断（如通过product_name反查product_id），若无法推断则标记为"未知"并单独分组。
- 数值型字段缺失：缺失率<5%用中位数填充；5%-20%用多重插补（MICE）；>20%则考虑将该字段转为分类变量（有值/无值）。
- 日期型字段缺失：如果业务上允许，使用该记录的其他时间字段（如发货日期）替代；否则删除。
# 缺失值处理示例（Python伪代码）
import pandas as pd
import numpy as np
def handle_missing(df, key_cols, numeric_cols, date_cols):
# 1. 删除主键缺失的记录
df = df.dropna(subset=key_cols, how='any')
# 2. 数值列填充策略
for col in numeric_cols:
missing_rate = df[col].isnull().mean()
if missing_rate < 0.05:
df[col].fillna(df[col].median(), inplace=True)
elif missing_rate < 0.20:
# 使用简单插补（此处用分组均值）
df[col] = df.groupby('region_id')[col].transform(lambda x: x.fillna(x.mean()))
else:
df[col + '_is_missing'] = df[col].isnull().astype(int)
df[col].fillna(0, inplace=True)
# 3. 日期列处理
for col in date_cols:
df[col] = pd.to_datetime(df[col], errors='coerce')
# 删除日期解析失败且无法推断的记录
df = df.dropna(subset=[col])
return df3.3 重复值去重：基于业务语义的精确去重
核心原理：重复值去重不能简单地使用"所有列完全相同"作为判断标准。在三表场景中，更常见的是部分列重复——例如，同一订单号下有多条记录，但只有一条是有效的；或者产品信息表中同一产品ID出现多次，但不同行的价格不同。去重的关键在于定义业务主键（Business Key）。
判断方法与操作流程：
- 定义业务主键：销售明细表的主键为(order_id, product_id)；产品信息表的主键为product_id；区域表的主键为region_id。
- 排序去重：对于重复记录，根据时间戳或版本号字段排序，保留最新的一条。
- 聚合去重：如果重复记录的不同字段需要保留（如不同来源的价格），则进行聚合操作（取平均、取最大等）。
| 去重场景 | 判断依据 | 保留策略 | 实现方式 |
|---|---|---|---|
| 完全重复 | 所有列相同 | 保留任意一条 | df.drop_duplicates() |
| 主键重复 | 业务主键相同 | 保留时间戳最新 | sort_values + drop_duplicates(subset) |
| 部分字段冲突 | 主键相同但数值不同 | 根据业务规则（如取结算价） | groupby + agg |
3.4 数据类型统一：消除隐性的格式陷阱
核心原理：数据类型统一是数据清洗中最容易被忽视却影响最大的环节。在三表关联时，如果product_id在销售表中是整数类型（123），而在产品表中是字符串类型（"00123"），关联就会失败。更隐蔽的问题是日期格式不一致（"2024/01/01" vs "2024-01-01"）和数值字段混入文本（"1,200元"）。
判断方法与操作流程：建立字段级的数据字典，明确每个字段的目标数据类型和格式规范：
- ID类字段：统一转为字符串，并补齐前导零（如产品ID统一为5位字符串）。
- 日期字段：统一转为datetime类型，并格式化为YYYY-MM-DD。
- 金额字段：去除货币符号和千分位逗号，转为DECIMAL类型。
- 分类字段：去除首尾空格，统一大小写（如"Active"和"active"视为同一类别）。
# 数据类型统一示例
# 1. 产品ID统一为5位字符串
df_sales['product_id'] = df_sales['product_id'].astype(str).str.zfill(5)
df_product['product_id'] = df_product['product_id'].astype(str).str.zfill(5)
# 2. 日期统一
df_sales['order_date'] = pd.to_datetime(df_sales['order_date'], format='%Y-%m-%d')
# 3. 金额清洗
df_sales['amount'] = df_sales['amount'].replace('[¥,元]', '', regex=True).astype(float)3.5 案例复盘：某电商平台三表清洗实战
背景与约束：某电商平台需要建立销售预测模型，涉及三张表：订单事实表（约800万行/月）、商品维度表（约12万行）、门店维度表（约3500行）。业务要求数据清洗在2小时内完成，且清洗后的数据必须支持后续的日粒度聚合分析。
初始现象与目标：初步探查发现：订单表中order_id存在约0.3%的重复；商品表中category字段有17种不同的写法（如"Electronics"、"electronics"、"电子"）；门店表中region_id与订单表中的store_region_id存在类型不一致（前者为整数，后者为字符串）。目标是将三表整合为一张宽表，且主键唯一、外键匹配率≥99.8%。
关键输入数据与参数：订单表包含order_id, product_id, store_id, order_date, sales_amount, discount；商品表包含product_id, product_name, category, unit_cost；门店表包含store_id, store_name, region_id, region_name。
分析判断过程：首先，我们使用数据剖析工具（如Great Expectations）生成了数据质量报告。报告显示：订单表的order_id重复是由于同一订单被多次同步导致；商品表的category字段混乱是因为不同团队维护时使用了不同语言和大小写；门店表的region_id类型不一致是因为历史遗留的系统迁移问题。
实际操作步骤：
- 数据源接入：使用Sqoop将MySQL订单表增量导入Hive，使用Spark读取Excel商品表并转为Parquet格式，门店表通过API拉取后存入HBase。
- 缺失值处理：订单表的discount字段缺失率12%，采用按商品类别分组的中位数填充；商品表的unit_cost缺失率0.8%，直接删除对应记录（仅96条）。
- 重复值去重：订单表按(order_id, product_id)去重，保留最新etl_time的记录；商品表按product_id去重，保留product_name最长的一条。
- 类型统一：所有ID字段转为STRING并补齐前导零；日期统一为DATE类型；金额字段统一为DECIMAL(10,2)。
- 质量校验：编写数据质量检查脚本，验证主键唯一性、外键匹配率、日期范围合理性。
结果对比与复盘：清洗后，订单表从800万行降至796万行（去重+删除缺失），外键匹配率从97.2%提升至99.9%。建模时间缩短了40%，模型AUC提升了0.03。复盘经验：① 数据清洗必须与业务方确认去重和填充规则，不能纯技术决策；② 建立数据质量监控看板，每次ETL后自动生成质量报告；③ 类型统一规范应写入团队开发规范，避免后续重复踩坑。