4、数据清洗与ETL：数据去重、数据校验、字段补齐、时间戳对齐、脏数据处理
埋点数据进了数仓，你以为就万事大吉了？
太天真了。我见过太多团队，辛辛苦苦埋了上百个事件，结果跑出来的报表全是「异常值」——日活比公司总人数还多，用户时长出现负数，事件ID乱码成火星文。说白了，埋点数据天生就是脏的。
这一章，我们就来聊聊怎么把这些「垃圾」洗成能用的「黄金」。我个人习惯把数据清洗和ETL看作一道流水线：进来的是原矿，出去的是精炼品。你想想看，如果这一步没做好，后面所有的分析、建模、策略，全都是在垃圾上盖大楼。
核心原则：数据清洗不是「尽可能多保留」，而是「有策略地丢弃和修复」。宁可数据量少一点，也要保证每一条记录都是可信的。
4.1 数据去重：别让同一条用户行为算两次
数据去重，听起来简单，做起来全是坑。我在项目中遇到过最离谱的一次：某个客户端因为网络重试机制写得不严谨，同一个「点击购买」事件在1秒内上报了7次。结果运营同学看着转化率报表，以为产品突然爆了，差点提前庆祝。
去重一般分两个层面：
- 精确去重：基于事件ID或唯一标识（如UUID），完全相同的记录只保留一条。
- 业务去重：在时间窗口内，相同用户+相同事件+相同属性，视为重复。
我建议优先使用事件ID去重。为什么？因为业务去重有风险——万一用户真的在1秒内连续点了两次呢？你把它去掉了，真实行为就丢了。
我的习惯：在埋点规范里就强制要求每条事件携带一个全局唯一的event_id（UUID格式）。这样ETL阶段直接按event_id做distinct，干净利落。
下面是一个简单的去重SQL示例：
-- 基于event_id的精确去重
CREATE TABLE cleaned_events AS
SELECT DISTINCT ON (event_id)
event_id,
user_id,
event_name,
event_time,
properties
FROM raw_events
WHERE event_id IS NOT NULL
ORDER BY event_id, event_time DESC; -- 保留最新的一条嗯，这里要注意：如果数据源本身没有event_id，那就得用多字段组合去重。但组合去重一定要加时间窗口，否则容易误杀。
4.2 数据校验：给数据上个「安检门」
数据校验，说白了就是检查数据是否符合预期。我把它分成三类：
| 校验类型 | 检查内容 | 常见问题 |
|---|---|---|
| 格式校验 | 字段类型、长度、格式是否符合规范 | 时间字段传了字符串"null"、手机号出现11个9 |
| 逻辑校验 | 数值是否在合理范围内、事件顺序是否合理 | 用户年龄=999、下单时间早于注册时间 |
| 完整性校验 | 必填字段是否为空、关联字段是否存在 | 事件缺少user_id、页面浏览事件没有page_url |
我曾经接手过一个项目，发现「注册成功」事件里，有30%的记录user_id是空的。后来一查，是前端埋点代码在用户未登录状态下也触发了事件。这种数据如果不拦下来，后面做用户生命周期分析全是错的。
避坑指南：校验规则不要写死在ETL代码里。我建议用配置表管理，比如JSON格式的校验规则。这样业务变了，改配置就行，不用重新部署。
4.3 字段补齐：别让空值毁了你的分析
字段补齐，不是让你「瞎编数据」。而是基于已有信息，合理推断缺失值。
常见的补齐策略：
- 默认值补齐：比如设备类型为空，填"unknown"；国家/地区为空，填"未知"。
- 衍生补齐：比如有IP地址，通过IP库反查出城市、运营商。
- 上下文补齐：比如用户在一次会话中，前几个事件有设备信息，中间某个事件丢了，可以用会话内最近一条记录的设备信息来填充。
我个人最常用的是上下文补齐。举个例子：
-- 用会话内最近的非空设备信息补齐缺失值
WITH ranked_events AS (
SELECT *,
LAST_VALUE(device_type IGNORE NULLS) OVER (
PARTITION BY session_id
ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS filled_device_type
FROM raw_events
)
SELECT * FROM ranked_events;你想想看，如果直接丢弃那些设备类型为空的事件，可能就丢了20%的数据。但用上下文补齐，能救回大部分。
注意：补齐一定要记录日志。我习惯在每条数据上加一个字段叫fill_status，标记哪些字段是被补齐的。这样分析时可以选择「只看原始数据」或「包含补齐数据」。
4.4 时间戳对齐：跨时区、跨系统的「时间统一战」
时间戳对齐，是埋点数据清洗里最容易被忽视、但后果最严重的环节。
为什么会这样？因为用户可能在东八区打开App，事件经过CDN、负载均衡、后端服务，最终落库的时间可能已经变成了UTC。更麻烦的是，有些客户端上报的是本地时间，有些上报的是服务器时间，混在一起根本没法做时序分析。
我建议统一遵循以下规则：
- 所有时间戳统一转为UTC+0存储。展示时再根据用户时区转换。
- 区分事件时间和接收时间。事件时间是用户行为发生的时刻，接收时间是服务器收到数据的时刻。两个都要保留。
- 做时间戳合理性校验。比如事件时间不能晚于接收时间（除非有时区问题），不能早于当前时间24小时以上。
下面是一个时间对齐的示例流程：
-- 时间戳标准化
UPDATE cleaned_events
SET
event_time_utc = CASE
WHEN timezone = 'Asia/Shanghai' THEN event_time_local - INTERVAL '8 hours'
WHEN timezone = 'America/New_York' THEN event_time_local + INTERVAL '5 hours'
ELSE event_time_local -- 默认视为UTC
END,
receive_time_utc = receive_time_raw AT TIME ZONE 'UTC';避坑指南：我曾经遇到过一个坑——客户端上报的时间戳是毫秒级，但服务端解析时当成了秒级，结果所有事件时间都变成了1970年。所以，一定要在ETL入口处做时间戳单位校验：如果时间戳小于1e12，大概率是秒级，需要乘以1000。
4.5 脏数据处理：该扔就扔，该修就修
脏数据，就是那些「一眼假」的记录。比如：
- 用户ID为null或"undefined"
- 事件名称为空字符串或乱码
- 数值字段出现负数（比如页面停留时间=-100秒）
- 枚举字段出现未定义的值（比如性别字段出现"火星"）
我的处理原则是「三层过滤」：
| 层级 | 处理方式 | 适用场景 |
|---|---|---|
| 第一层：硬过滤 | 直接丢弃，记录到异常日志 | 关键字段为空、格式完全错误 |
| 第二层：软修复 | 尝试修复，修复失败再丢弃 | 时间戳单位错误、编码问题 |
| 第三层：标记保留 | 保留数据，但打上脏数据标签 | 数值轻微异常、枚举值可映射 |
举个例子：
-- 脏数据处理示例
WITH filtered AS (
SELECT *,
CASE
WHEN user_id IS NULL OR user_id = '' THEN 'DROP'
WHEN event_name ~ '[^\x20-\x7E]' THEN 'REPAIR' -- 含非ASCII字符
WHEN stay_duration < 0 THEN 'TAG_DIRTY'
ELSE 'CLEAN'
END AS data_quality
FROM raw_events
)
SELECT * FROM filtered
WHERE data_quality != 'DROP'; -- 只保留可修复和干净的嗯，这里要强调一点：脏数据不能直接删了事。我习惯把丢弃的数据写入一张rejected_events表，定期review。有时候你会发现，某些「脏数据」其实是业务新需求导致的，比如新增了一个埋点字段但校验规则没更新。
我的经验：数据清洗不是一次性工作。每次上线新埋点、改版旧埋点，都要重新审视清洗规则。我建议每两周review一次清洗日志，看看有没有新的脏数据模式出现。
4.6 本章知识体系总览
下面这张图，把数据清洗与ETL的核心流程串起来了。你可以把它当作操作手册：
这张图把整个流程串成了流水线：原始数据进来，先过去重，再过校验。校验通过的走字段补齐和时间戳对齐；校验失败的走脏数据处理，该扔扔、该修修、该标记标记。最终，两条支流汇聚成清洗后的可信数据。
数据清洗这件事，说白了就是「信任但验证」。你不能假设埋点团队上报的数据都是完美的，也不能假设所有脏数据都能修好。保持敬畏心，做好每一层过滤，你的分析模型才能站得稳。