数据清洗指南

以下为面向业务分析场景的清洗与去重方案，覆盖字段类型定义、标准化与校验规则、去重与幸存者选择逻辑、实施步骤与质量控制要点。设计目标是：在不虚构数据的前提下，最大化保留最新且有效的联系方式与销售跟进状态，输出可追溯与可复现的结果。

一、字段与数据类型建议

• lead_id：分类型（主键/标识）
• name：文本型
• phone：文本型（标准化后保留E.164主号码及补充字段）
• email：文本型（标准化后保留域名校验信息）
• city、省份：分类型（建议对齐行政区字典）
• 行业：分类型
• 来源 source、utm_*：分类型
• tags：分类型（多值，清洗为数组/集合）
• create_time、last_contact_time：时间型（存UTC与Asia/Shanghai两列或一列UTC+一列时区）
• is_qualified：布尔型
• owner_id：分类型（ID作为类别变量更利于下游分组）
• company_size：分类型（如“0-49”、“50-199”等标准档位；若是纯数值人数再映射）
• remark：文本型（保留raw与clean两列）

二、标准化与校验规则

1. 电话 phone

• 目标：统一到E.164（中国为+86），拆分并识别手机/固话，处理区号、空格、连字符、+86/086/0086等前缀，保留分机信息。
• 处理流程
  • 统一字符集：全角转半角，去除不可见字符和多余空白，标准化破折号、括号。
  • 提取与清洗：仅保留数字与前导+，识别并剥离扩展号（ext/分机），分机存入phone_ext。
  • 标准化国家码：
    • 将0086、086、86前缀统一为+86；无国家码但明显为中国号段时补+86。
  • 手机号识别：
    • 规则：11位且以1[3-9]开头（中国大陆手机）→ 标准化为+861XXXXXXXXXX。
  • 固话识别：
    • 国内格式示例：0AA-XXXXXXX/XXXXXXXX（AA为2-3位区号，如010/021/0755），去掉国内长途前缀0后转E.164：+86AA……（不带0）。
    • 如原始为(010)12345678 → +861012345678；(0755)87654321 → +8675587654321。
  • 校验与标注：
    • 标注phone_type ∈ {mobile, landline, unknown}。
    • 无法识别或长度非法的记为invalid，保留raw_phone与error_code。
  • 去噪与规范：
    • 移除重复国家码、多个号码拼接情况，仅保留第一个可解析号码为主号码；其他存入phone_alt数组（最多N个）。
• 关键正则（示意）
  • 手机检测（宽松）：^(?:\+?86)?1[3-9]\d{9}$
  • 固话国内（含0）：^(?:\+?86)?0\d{2,3}\d{7,8}$
• 输出字段：phone_e164（主）、phone_type、phone_ext、phone_alt（数组）、phone_valid（布尔）。

2. 邮箱 email

• 标准化：
  • 转小写；去左右空格；全角转半角。
  • 针对gmail.com/googlemail.com：移除本地部分中的点(.)，去除+标签（只对这两个域名生效，避免误伤其他服务商）。
  • 保留原始邮箱于email_raw。
• 校验：
  • 基础语法校验（本地部分与域名部分基本正则）。
  • DNS校验：优先检查MX，无MX则回退到A记录；无法解析则标记为invalid_domain。
  • 输出：email_canonical、email_valid（语法）、email_domain_valid（DNS）、email_provider（如qq/163/aliyun/gmail等）。

3. 姓名 name（中文为主）

• 规范：
  • 全角转半角；去除多余空格（前后与中间的随机空格）。
  • 繁简体统一（采用OpenCC等将繁体→简体，另保存name_trad作为备份）。
  • 去除含有Emoji或控制字符，必要时保留name_raw。
• 结果：name_std（简体、无空格）、name_raw、name_trad（可选）。

4. 城市/省份

• 对齐标准行政区名称与代码（推荐GB/T 2260及民政部最新更新）。
• 容错映射（“广州市/广州”、“重庆市/重庆”），输出省市代码字段province_code、city_code并保留原值。

5. 时间 create_time、last_contact_time

• 解析：
  • 尝试解析包含时区偏移的字符串（ISO-8601优先）；无偏移的视为“未知时区”。
• 标准化：
  • 内部存UTC（_utc）与Asia/Shanghai（_cst）两列，或仅存UTC并在分析时按CST显示。
• 时间异常修正策略：
  • 若两者均为“未知时区”，默认按Asia/Shanghai解释。
  • 若last_contact_time_utc < create_time_utc：
    • 若二者原串分别包含“Z/+0000/UTC”和无时区或+08:00，优先以带偏移的一方为准，不做硬修正。
    • 若差值在0~16小时内且来源字段显示跨时区（例如海外站表单），可推断最可能的固定偏移（如+8）进行一次性校正；无法判断则仅标注time_conflict=true，不改值。
  • 输出：create_time_utc/last_contact_time_utc、time_conflict（布尔）。

6. 来源/UTM

• 解析utm_source/utm_medium/utm_campaign，统一大小写、空白与常见同义词（cpc/paid/sem）。
• 对source建立枚举映射表，异常值入“other”。

7. tags

• 拆分：按[,;、，；空格]任一分隔；去重；去空；去前后空白。
• 统一：英文标记统一小写，中文不变；可与字典对齐（如“意向强/strong_intent”映射）。
• 输出：tags_array（集合）、tags_raw。

8. remark

• 保留原文remark_raw。
• 清洗remark：移除Emoji（可选保留emoji_list字段），标准化换行为\n；去除多余空白。
• 输出：remark_clean、emoji_present（布尔）。

9. 其他字段

• is_qualified：统一到布尔：{1, true, 是, Y}→true；{0, false, 否, N}→false；异常为空。
• owner_id：转字符串或整型，但用作分类型；非法字符标为null并保留raw。
• company_size：将“50-100”“50—100”“50~100”统一到标准档位；如为精确人数则映射至档位并保留原数值列company_size_raw。

三、去重策略（实体解析） 目标：多渠道重复线索合并，优先保留最新有效联系方式与销售跟进状态，保证可追溯。

1. 阻塞键（blocking keys）与标准化键

• phone_key：若phone_valid=true且为中国号，使用phone_e164（手机号优先；固话也可作为次要键）。
• email_key：若email_domain_valid=true，使用email_canonical。
• name_key：name_std。
• 组合阻塞：
  • B1：同一phone_key
  • B2：同一email_key
  • B3（补充）：无有效phone/email时，用 name_key + 省份/城市 的组合作为弱阻塞，并辅以同一来源窗口内时间近（如30天）条件；该类匹配标记为low_confidence。

2. 聚类（连通分量）

• 构建边：
  • 共享同一phone_key的两条线索相连；
  • 或共享同一email_key的两条线索相连；
  • 对低置信度匹配（B3）单独聚类，后续需要人工或更严格规则确认。
• 对全部边求连通分量，生成cluster_id；220K级可用SQL递归CTE/多次自连接或Python NetworkX/Spark GraphFrames。

3. 幸存者（主记录）选择规则

• 排序优先级（从高到低）：
  1. is_qualified=true 优先
  2. last_contact_time_utc 最大者优先
  3. 若2相同，则create_time_utc 最大者
  4. 联系方式有效性：拥有有效手机 > 仅有效邮箱 > 仅固话 > 无效
  5. 来源优先级（可选，按业务价值排序：呼叫中心跟进 > 官网表单 > 线下活动）
• 幸存者记为survivor，其他为duplicates。

4. 字段合并策略（字段级合并）

• phone：主号码取survivor的优先级最高有效号码；聚合所有有效号码为phone_all（去重集合）。手机优先于固话。分机保留并关联对应固话。
• email：同理，email_primary取survivor有效邮箱；email_all集合去重。
• is_qualified：若集群内存在true，则survivor.is_qualified=true。
• last_contact_time：取集群最大值；同时保留survivor_last_contact_owner（若存在owner_id）。
• owner_id：优先取最近一次联系对应的owner_id；保留owners_all集合（含出现次数/最近时间可选）。
• 来源/UTM：保留survivor来源；同时聚合source_all（统计各来源出现次数便于归因）。
• tags：集合并集去重。
• remark：按时间倒序合并（保留最近N条），每段前缀添加[yyyy-MM-dd HH:mm][source][owner_id]；保留remark_raw_ids便于追溯。
• 行业/省市：若冲突，采用survivor；保留conflicts_industry/province标记。
• company_size：若冲突，按数据质量（标准化档位>原始无序）及填写时间新者优先；保留所有观测值及频次统计。

5. 输出表设计

• leads_clean（主表）：cluster_id、survivor_lead_id、name_std、phone_e164、email_primary、…（标准化与合并后字段）、data_quality_flags
• leads_contacts（从表）：cluster_id，contact_type（phone/email），value_e164/canonical，valid，is_primary
• leads_lineage（血缘表）：cluster_id，lead_id，is_survivor，合并决策要点（排名、时间戳、原因代码）
• leads_conflicts（可选）：cluster_id，字段名，冲突值列表

四、实施步骤顺序

1. 基础预处理：字符规范（全角/半角、空白）、字段裁剪与类型转换。
2. 联系方式标准化与校验：phone、email。
3. 名称与行政区标准化：name、city、省份。
4. 时间解析与时区标准化：生成UTC与CST列，标注time_conflict。
5. 来源/UTM与tags/remark清洗。
6. 构建标准化键（phone_key、email_key、name_key）。
7. 去重聚类（连通分量）与幸存者选择。
8. 字段级合并生成主表与从表，写入血缘与冲突信息。
9. 质量校验与指标生成。
10. 固化字典与规则（版本号）以保证复现。

五、关键实现要点（伪规则/SQL思路）

• phone标准化（要点）
  • 去噪：s = normalize_width(s).strip(); s = re.sub(r'[^0-9+xX+]', '', s)
  • 提取分机：分离 x\d+ 或 ext\d+；存ext
  • 国家码：将开头的 0086|086|86 归一为 +86；无则若符合中国号段补+86
  • 手机判断：去除前缀后为 1[3-9]\d{9} → +86拼接
  • 固话判断：去除前缀后为 0\d{2,3}\d{7,8} → 去0后拼 +86
• email标准化（要点）
  • lowercase；gmail域名移除本地部分点与+标签；DNS MX校验。
• 去重连通分量（SQL示意）
  • 生成键表：select lead_id, phone_key from … where phone_valid union select lead_id, email_key …
  • 对相同键的lead_id做自连接得到边(edge_u, edge_v)
  • 用递归CTE求连通分量或在Python/Spark中求connected components
• 幸存者选择（窗口函数）
  • 在每个cluster_id内按规则排序 row_number() over (partition by cluster_id order by is_qualified desc, last_contact_time desc, create_time desc, contact_score desc) = 1

六、数据质量校验与监控指标

• 唯一性与完整性
  • cluster_id唯一；每cluster仅1个survivor
  • 每条记录至少一个联系方式存在（phone或email或两者之一），否则标注contact_missing
• 联系方式有效率
  • phone_valid_rate、email_domain_valid_rate、mobile_vs_landline占比
• 去重效果
  • 重复率 = 1 - 幸存者数 / 原始线索数
  • 每cluster大小分布（2条、3条以上）与高/低置信度匹配占比
• 时间一致性
  • time_conflict比例；被自动时区修正的比例（若启用）
• 字段冲突
  • 行业/省市/owner_id冲突率及解决策略覆盖率
• 溯源可用性
  • leads_lineage覆盖率、remark合并完整率

七、注意事项与边界

• 不应对时间做无法证实的强行改写；无法判断的时区冲突仅标注，不校正。
• Gmail的点与+规则仅适用于gmail.com/googlemail.com；其他邮箱不做该项标准化。
• 电话固话区号复杂且存在新老号段交叠；对无法判定的固话不应强制归类，保留unknown并保持原值可追溯。
• 对低置信度去重（仅基于姓名+地理+时间接近）建议单独输出待复核清单，避免误并。
• PII合规：在测试/分享数据样例时应做脱敏（如phone中间四位打码、email本地部分部分打码）。

通过以上流程，能够在不夸大、不丢失关键信息的前提下，完成联系方式与时间的标准化、跨渠道的重复线索合并，并保留血缘与冲突信息，满足后续业务分析的准确性与可追溯性要求。

以下是针对该物联网遥测数据集的清洗与异常检测方案，面向预测分析场景，目标是：统一单位与时区、修正时序缺陷、识别并屏蔽异常点，保证特征稳定性。流程按可实施步骤组织，并给出可操作的阈值与规则。

总体产出

• 原始数据的强类型化与行级质量标签。
• 统一到 UTC 的分钟级时间索引（每设备），保证 device_id–ts 唯一。
• 统一单位（尤其温度），剪裁/屏蔽量程外值。
• 针对每个数值列的异常标记：is_outlier、is_spike、is_stuck_zero、is_unit_converted、is_range_violation。
• 规范化分类字段（status_code、alarm_flag、fw_version），可用于建模的稳定特征集。

1. 架构与类型强制

• 明确定义目标模式：
  • 数值型：temp、vibration_rms、current_a、pressure_kpa、speed_rpm。
  • 时间型：ts（统一为 UTC timestamp 到分钟精度）。
  • 布尔型：alarm_flag。
  • 分类型：status_code、fw_version、site_id、device_id。
  • 地理坐标：lat、lon（double）。
  • 结构化：diag（JSON），解析出若干列（见第8步）。
• 解析并强制类型：
  • 将 “N/A”“—”“null”“NaN”“Infinity” 等字符串统一转换为 Null。
  • alarm_flag：统一到布尔（接受 0/1、"true"/"false"、"Y"/"N"）。
  • fw_version：解析为规范化语义版本（主.次.修订），无法解析的保留为原字符串。

2. 时间戳与时区处理

• 解析 ts 为 timestamp，优先使用：
  • ts 自带时区偏移或 ISO-8601 信息；
  • diag 中的 tz_offset/tz 字段；
  • site_id 映射到时区（维护站点时区字典）；若无，依据 lat/lon 推断时区。
• 全量转换到 UTC，保留原始时区来源字段 ts_tz_source 以用于审计。
• 归一到分钟粒度：floor/round 到分钟，并创建分钟级索引。
• 每设备去重：
  • 对 device_id+ts（分钟）重复记录：选择最新一条（若有采集序号/ingest_ts），否则对数值列做稳健聚合（中位数）。
• 时序一致性校验：
  • 设备内 ts 需单调非降；若检测到跳点（负时间差或超大正差），标记 is_time_jump。
  • 统计每设备的缺失分钟率，必要时补齐索引并将缺失度量置为 Null（不做插值，建模阶段再处理）。

3. 温度单位统一（℃/℉）

• 优先策略：diag.temp_unit 或设备/站点配置表提供单位，直接据此转换。
• 备选判定（设备级，基于分布）：
  • 计算设备级温度分布：若中位数在 [100, 180] 且最大值 ≤ 212，且落在 [70, 212] 的比例 ≥ 0.7，判定为℉。
  • 若温度值大多落在 [-40, 90] 且最大值 < 120，判定为℃。
• 转换公式：C = (F − 32) × 5 / 9。
• 输出标记：temp_converted(bool)、temp_unit_source(enum: diag/site/infer/disputed)。判定不明确（介于两类的混合分布）则保留为 Null 并标记为 temp_unit_disputed，避免错误转换。

4. 量程与极端值处理（剪裁/校准）

• 依据 diag 或设备规格表的量程对数值列进行校验：
  • temp（℃）：[-50, 250]（工业常见；若设备提供更严格规格，以设备为准）。
  • vibration_rms：依据传感器规格（例如 [0, 100] mm/s 或 [0, 5] g），无规格则用经验上界（如 99th 百分位 × 1.5）。
  • current_a、pressure_kpa、speed_rpm：使用设备提供量程；没有则采用历史稳健范围（1st/99th 百分位）并保守扩展。
• 处理策略：
  • 明显越界（小于最小或大于最大）：置 Null 并标记 is_range_violation=1。
  • 对接近边界的极端值：可选择 winsorization 到量程边界用于建模版本，同时保留原始值列以备诊断。

5. 异常点检测（分钟级，设备内）

• 突刺/尖峰（spike）：
  • Hampel 过滤（窗口 15 分钟，k=3 或 4）对每数值列计算中位数与 MAD，单点偏差大且在 1–3 分钟内回归，标记 is_spike=1。
  • 重置相关尖峰：若 diag.reset_event=true 或 reset_counter 增加，在重置后的±2 分钟内的异常统一置为 Null。
• 长时间恒零（stuck-at-zero）：
  • 连续零阈值（设备内）：
    • vibration_rms：≥ 5 分钟；
    • current_a：≥ 5 分钟（非停机状态）；
    • speed_rpm：≥ 5 分钟（非停机状态）；
    • pressure_kpa：≥ 10 分钟；
    • temp：≥ 15 分钟。
  • 标记 is_stuck_zero=1，建议置 Null；同时生成辅助特征 stuck_zero_duration（分钟）。
• 平稳异常/漂移：
  • 低变异报警：滚动窗口（60 分钟）标准差过低且偏离历史分位数范围，标记 is_flatline。
• 交叉一致性：
  • 规则示例：speed_rpm=0 且 status_code=OFF/停机，则 current_a 应近似为 0（< 1A）；违规则标记 is_cross_inconsistent。

6. 缺失值标准化与后续填补策略

• 所有 “N/A”“—”“ ” 等统一为 Null。
• 字符型数值（含逗号、小数点、单位后缀）解析为数值，解析失败置 Null。
• 建模前的简化填补（不在原始清洗阶段执行，可在特征工程中）：
  • 设备内中位数或时间邻域（前后 5–15 分钟）中位数填补；
  • 或使用跨设备站点内稳健均值/中位数。
• 保留缺失指示变量 per-feature：is_null_temp 等，以利模型学习缺失机制。

7. 枚举与分类字段规范化

• status_code 统一字典（示例）：
  • 正常：{"OK","0","正常","Normal"} → OK
  • 警告：{"WARN","1","警告"} → WARN
  • 告警：{"ALARM","2","告警"} → ALARM
  • 停机/离线：{"OFF","OFFLINE","停机","-1"} → OFFLINE
  • 维护：{"MAINT","维护"} → MAINT
• 生成 status_code_norm，并保留原字段以备追溯。无法映射的标记为 UNKNOWN 并统计比例。
• alarm_flag 统一布尔，来源不一致时保留 alarm_flag_source。

8. diag(JSON)解析与校验

• 建议解析的键：
  • 单位与量程：temp_unit、vibration_unit、current_range、pressure_range、speed_range。
  • 设备状态：reset_counter、reset_event、uptime、firmware_build、sensor_ok_flags。
  • 环境与时区：tz、tz_offset、calibration_date。
• 校验：
  • 单位/量程与数值列一致性（见第4步），不一致时标记 diag_conflict。
  • reset_counter 单调递增；异常跳变提示设备复位。
• 输出：结构化列并保留原始 diag 原文；记录解析失败率。

9. 地理坐标校验

• 范围：lat ∈ [-90, 90]，lon ∈ [-180, 180]；越界置 Null 并标记 geo_invalid。
• 可选：检测 lat/lon 互换（若 |lat|>90 且 |lon|≤90，交换并标记 swapped）。
• 设备级位置稳定性：标准差过大（如 >0.01°）且非移动设备，标记 geo_drift。

10. 去重与重采样

• 以 device_id+ts（分钟）为唯一键。
• 有多条记录同一分钟：稳健聚合（中位数）或选择带最新 diag 的记录；对分类字段选择最频繁值。
• 统一输出为完整分钟网格（每设备），缺失分钟插入 Null 行，便于时序建模与滑窗特征。

11. 输出与特征稳定性保障

• 为每数值列生成质量标签与处理后列：
  • 原始列：temp_raw 等；
  • 处理列：temp（单位统一、越界 Null、尖峰与stuck屏蔽）；
  • 质量标记：is_spike、is_stuck_zero、is_range_violation、is_flatline、is_cross_inconsistent、temp_converted。
• 行级质量分数（可选）：按标记加权汇总，用于筛除低质量样本。
• 版本化输出：raw、clean、features 三层，便于回溯与对比。

12. 质量评估与监控指标

• 唯一性：device_id–ts 唯一率=100%。
• 时间覆盖：每设备有效分钟覆盖率；时间跳点率<0.1%。
• 单位一致性：温度单位争议率<1%。
• 异常率：is_spike、is_stuck_zero、is_range_violation 的比例按设备/站点统计并设阈报警。
• 枚举规范化：UNKNOWN 比例<0.5%。

实施要点（针对近30天约5GB，建议用分布式处理如 PySpark）

• 读入与分区：按 event_date（UTC）和 device_id 分区，schema-on-read 强制类型。
• 缺失标准化：统一替换占位字符串→Null。
• 时间与去重：使用窗口函数按 device_id 排序，去重与跳点标记。
• 异常检测：
  • 使用窗口 UDF 实现 Hampel 过滤与连续零检测（设备内滚动）。
  • 量程校验与越界置 Null。
• diag 解析：定义 JSON 解析 UDF，落地关键字段并做一致性校验。
• 枚举映射：加载字典表统一 status_code。
• 审计与落地：生成清洗审计日志（行级标记计数、失败原因），产出分层数据集。

关键规则摘要

• 温度转换：优先 diag/site；次选分布判定；公式 C=(F−32)×5/9；不确定则不转换并标记争议。
• 异常屏蔽优先顺序：量程越界 > 设备复位窗口 > 尖峰 > stuck-zero/flatline。
• 时间统一到 UTC、分钟级唯一索引；不做插值，仅在特征工程阶段考虑稳健填补。
• 所有清洗均保留来源标记，确保可追溯与可复现。

该方案能够在建模前系统性地处理单位、时序与异常点问题，生成稳定的特征，并提供必要的质量度量用于后续模型训练与监控。