4、数据转换核心：数据清洗、标准化、脱敏与聚合

数据转换这个环节，说白了就是 ELT 流程里最费功夫、也最见功力的部分。我见过太多团队，数据从源端抽过来了，加载也完成了，结果在转换这一步翻车——要么跑出来的报表对不上，要么跑着跑着任务就挂了。

这一章，咱们就把转换环节的四个核心动作拆开揉碎了讲。你想想看，数据进了仓库，第一件事是什么？

4.1 数据清洗：去重与空值处理

数据清洗是转换的第一步，也是我每次做 ELT 项目时盯得最紧的一环。脏数据进来，后面全是白费功夫。

4.1.1 去重：别让重复数据毁了你的报表

去重这件事，听起来简单，做起来坑不少。我个人习惯用 ROW_NUMBER() 配合窗口函数来处理，而不是简单的 DISTINCT。

-- 按业务主键去重，保留最新一条记录
WITH dedup AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, order_date
      ORDER BY load_timestamp DESC
    ) AS rn
  FROM raw_orders
)
SELECT * FROM dedup WHERE rn = 1;

4.1.2 空值处理：别让 NULL 悄悄搞破坏

空值处理，我见过三种主流策略：

• 直接丢弃：对于关键字段为空的记录，直接过滤掉。适合对数据完整性要求极高的场景。
• 填充默认值：用 COALESCE 或 IFNULL 填充业务默认值。比如年龄字段为空，填 0 或平均值。
• 标记保留：用特殊标记（如 -999、'UNKNOWN'）保留空值记录，方便后续排查。

-- 空值填充示例
SELECT 
  user_id,
  COALESCE(age, 0) AS age,
  COALESCE(email, 'unknown@email.com') AS email,
  CASE 
    WHEN phone IS NULL THEN 'missing_phone'
    ELSE phone 
  END AS phone_status
FROM raw_users;

4.2 数据标准化：让数据说同一种语言

数据标准化，说白了就是把不同来源的数据统一成同一套规范。我做过一个跨国项目，同一个日期字段，有的源用 '2024-01-15'，有的用 '01/15/2024'，还有用 '15-JAN-2024' 的。不标准化，后面根本没法做聚合。

标准化类型	常见问题	推荐做法
日期格式	多种格式混用	统一转为 ISO 8601 (YYYY-MM-DD)
货币单位	不同币种、不同精度	统一为基准币种，保留两位小数
编码规范	性别、状态等字段编码不一致	建立映射表，统一编码规则
文本大小写	大小写混用导致分组异常	统一转为小写或首字母大写

-- 日期标准化示例
SELECT 
  order_id,
  CASE 
    WHEN order_date LIKE '%/%' THEN 
      TO_DATE(order_date, 'MM/DD/YYYY')
    WHEN order_date LIKE '%-%' THEN 
      TO_DATE(order_date, 'YYYY-MM-DD')
    ELSE 
      TO_DATE(order_date, 'DD-MON-YYYY')
  END AS standardized_date
FROM raw_orders;

4.3 数据脱敏：保护敏感信息

数据脱敏这件事，现在越来越重要了。GDPR、个人信息保护法，哪个都不是闹着玩的。我在金融行业做项目时，脱敏是必须过的关卡。

常用的脱敏方法有这几种：

• 替换：用固定字符替换敏感字段。比如手机号中间四位用 **** 代替。
• 遮蔽：只显示部分信息。比如身份证号只显示前六位和后四位。
• 哈希：用哈希算法对敏感信息进行不可逆转换。适合需要保持唯一性的场景。
• 泛化：降低数据精度。比如把具体年龄变成年龄段。

-- 手机号脱敏示例
SELECT 
  user_id,
  CONCAT(
    SUBSTRING(phone, 1, 3),
    '****',
    SUBSTRING(phone, 8, 4)
  ) AS masked_phone,
  -- 哈希脱敏（保持唯一性）
  MD5(email) AS hashed_email,
  -- 泛化年龄
  CASE 
    WHEN age BETWEEN 0 AND 18 THEN '0-18'
    WHEN age BETWEEN 19 AND 35 THEN '19-35'
    WHEN age BETWEEN 36 AND 60 THEN '36-60'
    ELSE '60+'
  END AS age_group
FROM raw_users;

4.4 数据聚合与计算：从细节到洞察

数据聚合，是 ELT 流程中把原始数据转化为业务价值的关键一步。我习惯把聚合分为两类：

• 预聚合：在 ELT 阶段就计算好常用的汇总指标，比如每日销售额、用户留存率等。这样查询时直接取用，速度快得多。
• 实时计算：对于需要最新数据的场景，在查询时动态计算。适合临时分析和探索性分析。

-- 预聚合示例：每日销售汇总
INSERT INTO agg_daily_sales
SELECT 
  order_date,
  product_category,
  COUNT(DISTINCT user_id) AS unique_buyers,
  SUM(order_amount) AS total_revenue,
  AVG(order_amount) AS avg_order_value,
  COUNT(*) / COUNT(DISTINCT user_id) AS purchase_frequency
FROM fact_orders
WHERE order_date = CURRENT_DATE - 1
GROUP BY order_date, product_category;

计算字段这块，我建议把复杂的业务逻辑封装成 SQL 函数或存储过程。比如计算用户生命周期价值（LTV）、订单转化率这些，写一次，到处用。

-- 创建计算函数示例
CREATE FUNCTION calculate_ltv(
  user_id INT,
  lookback_days INT
) RETURNS DECIMAL(10,2)
AS $$
  SELECT SUM(order_amount) 
  FROM fact_orders
  WHERE user_id = $1
    AND order_date >= CURRENT_DATE - $2
$$ LANGUAGE SQL;

4.5 本章知识体系总览

下面这张图，把数据转换的四个核心动作串起来了。你可以把它当作 ELT 转换阶段的检查清单。

数据转换这四个动作，顺序很重要。我个人习惯的流程是：先做清洗，把脏数据处理干净；然后做标准化，让数据格式统一；接着做脱敏，保护敏感信息；最后做聚合计算，产出业务指标。这个顺序一旦乱了，后面返工的代价会非常大。