第5章数据转换核心

5、数据转换核心：字段映射、数据类型转换、数据脱敏、数据标准化、数据聚合

数据转换，说白了就是ETL里最磨人的那部分活儿。很多新手觉得这步就是「把A表搬到B表」，其实不然。我做了这么多年，踩过的坑有一半都埋在这里。今天咱们就把这五个核心操作掰开揉碎了讲清楚。

5.1 字段映射：别让名字害了你

字段映射听着简单，就是把源字段对应到目标字段。但实际项目中，我见过太多因为字段名不一致导致的血案。

举个例子：源系统叫 cust_id，目标系统叫 customer_number。你直接写个 SELECT cust_id AS customer_number 就完事了？嗯，这里要注意——字段映射不只是改名，还涉及类型、长度、默认值等一系列问题。

  核心原则：
  一对一映射：最基础，直接对应
多对一映射：多个源字段合并成一个目标字段（比如姓+名→全名）
一对多映射：一个源字段拆成多个目标字段（比如地址拆成省、市、区）
条件映射：根据某个条件决定映射关系（比如性别代码1→男，2→女）

我个人习惯在映射前先画一张映射矩阵表，把源字段、目标字段、转换规则、异常处理都列清楚。别偷懒，这一步省了，后面调试能让你怀疑人生。

5.2 数据类型转换：隐式转换是魔鬼

数据类型转换，每个ETL工程师都绕不开。但你知道吗？很多线上事故都是隐式类型转换搞出来的。

我曾经在一个项目中，源系统把日期存成了字符串 '2024-01-15'，目标系统要求 DATE 类型。开发小哥图省事，直接写 CAST(date_str AS DATE)。结果呢？有一批数据格式是 '2024/01/15'，直接跑挂了。

避坑指南： 我曾经因为没做数据质量检查，直接做类型转换，导致整张表数据错乱。后来我养成了一个习惯——转换前先做数据探查，看看有没有脏数据。

常见的转换场景：

字符串→数值：注意空字符串、特殊字符（如 $、,）
字符串→日期：统一格式，处理时区问题
数值→字符串：注意精度丢失，比如 0.1+0.2 在浮点数里不是精确的 0.3
布尔→整数：统一映射规则，别一会儿 true→1，一会儿 true→-1

-- 安全的类型转换示例（SQL Server）
SELECT 
    TRY_CAST(order_date AS DATE) AS safe_date,  -- 转换失败返回NULL
    TRY_CONVERT(DECIMAL(10,2), amount) AS safe_amount
FROM source_table;

5.3 数据脱敏：合规不是闹着玩的

数据脱敏，说白了就是给敏感数据「打码」。现在数据合规越来越严，这步做不好，公司可能被罚到破产。

你想想看，如果开发环境里直接暴露了用户的身份证号、手机号、银行卡号，那后果有多严重？我在项目中遇到过，测试库里的数据是从生产库直接拷贝的，结果一个实习生把数据导出到了个人电脑上...嗯，那场面我不想回忆。

脱敏策略：

替换：用固定字符替换，比如手机号变成 138****1234
遮蔽：部分显示，部分隐藏，比如身份证号只显示后四位
加密：用哈希算法处理，比如 SHA-256，但注意不可逆
动态脱敏：根据用户权限动态决定是否脱敏

-- 手机号脱敏示例（MySQL）
SELECT 
    CONCAT(LEFT(phone, 3), '****', RIGHT(phone, 4)) AS masked_phone
FROM user_table;

我个人建议，脱敏规则一定要文档化，并且经过安全团队审核。别自己拍脑袋想一套规则就上线，出了事你扛不住。

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

数据标准化，就是让不同来源的数据「对齐」。比如有的系统用 M/F 表示性别，有的用 0/1，有的用 男/女。不标准化，分析的时候根本没法用。

标准化通常包括：

编码统一：比如国家代码用ISO标准，别一会儿 CN，一会儿 CHN
单位统一：金额统一到元还是分？重量统一到千克还是克？
格式统一：日期格式、电话号码格式、邮政编码格式
值域统一：枚举值要一致，比如订单状态 已支付/未支付/已取消

  我的经验：
  标准化规则最好做成配置表，而不是硬编码在ETL脚本里。这样业务变了，改配置就行，不用改代码。

-- 性别标准化示例
SELECT 
    CASE 
        WHEN gender IN ('M', '1', '男', 'male') THEN '男'
        WHEN gender IN ('F', '0', '女', 'female') THEN '女'
        ELSE '未知'
    END AS standardized_gender
FROM source_table;

5.5 数据聚合：从细节到全局

数据聚合，就是把细粒度数据汇总成粗粒度数据。比如把每天的订单数据，聚合成月度报表。

聚合操作看似简单，但坑也不少。我记得有一次，业务方要「月度活跃用户数」，我直接 COUNT(DISTINCT user_id) 就完了。结果呢？同一个用户在同一个月内多次活跃，我只算了一次。业务方说不对，他们要的是「月度活跃用户总次数」——这就是聚合粒度的理解偏差。

常见的聚合函数：

聚合类型	常用函数	注意事项
计数	COUNT, COUNT(DISTINCT)	注意NULL值处理，DISTINCT性能问题
求和	SUM	注意溢出问题，大数求和用DECIMAL
平均值	AVG	注意异常值影响，考虑用中位数
最大/最小	MAX, MIN	注意日期类型比较
分组	GROUP BY	注意分组字段的选择，避免过度聚合

避坑指南： 我曾经在聚合时没考虑数据去重，导致报表数据翻了一倍。后来我养成了一个习惯——聚合前先做数据质量检查，看看有没有重复记录。

-- 月度销售聚合示例
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(DISTINCT user_id) AS active_users,
    SUM(order_amount) AS total_revenue,
    AVG(order_amount) AS avg_order_value
FROM orders
WHERE order_status = 'completed'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

5.6 知识体系总览

下面这张图，是我梳理的数据转换核心知识体系。你可以把它当成一张「地图」，遇到具体问题时，回来看看属于哪个环节。

这五个核心操作，说白了就是数据转换的「五虎上将」。每个都重要，每个都有坑。我的建议是：别想着一步到位，先把基础打牢，再慢慢优化。

记住一句话：数据转换做得好，后面分析没烦恼。数据转换做得糙，加班改bug到老。