Day 36晚上-2：数据质量的其他三大维度与清洗方法实战

数据质量的其他三大维度

维度4：及时性（Timeliness）- 数据更新够快吗？

定义：数据从产生到可用之间的时间间隔是否满足业务需求

真实灾难案例4：3天延迟导致的连锁反应

某区域运营总监每天早上9点要查看各门店的昨日经营数据。但华西区因为"网络不稳定"，数据经常延迟3-5天上报。

2023年6月的一次事故：

• 6月12日：某门店客户投诉激增（从日均2起跳到15起）
• 6月13-14日：问题持续恶化，客户开始在社交媒体曝光
• 6月15日：总部才看到6月12日的数据，发现异常
• 6月16日：派人调查，发现是新来的技师操作失误
• 6月17日：问题解决，但已经造成18个客户流失，品牌声誉受损

如果数据实时：6月12日当天就能发现异常，当天解决，损失可以控制在2-3个客户。

及时性的黄金标准：

检测SQL示例：

-- 检测数据更新的及时性
SELECT 
    store_id AS 门店ID,
    MAX(DATE(complete_time)) AS 最新工单日期,
    CURDATE() AS 当前日期,
    DATEDIFF(CURDATE(), MAX(DATE(complete_time))) AS 延迟天数,
    CASE 
        WHEN DATEDIFF(CURDATE(), MAX(DATE(complete_time))) = 1 THEN '正常'
        WHEN DATEDIFF(CURDATE(), MAX(DATE(complete_time))) <= 3 THEN '轻微延迟'
        ELSE '严重延迟'
    END AS 及时性状态
FROM work_orders
GROUP BY store_id
HAVING DATEDIFF(CURDATE(), MAX(DATE(complete_time))) > 1  -- 标记延迟超过1天的门店
ORDER BY 延迟天数 DESC;

维度5：有效性（Validity）- 数据符合业务规则吗？

定义：数据值是否在合理的取值范围内，是否符合业务约束

真实灾难案例5：-500元的工时费

某数据分析师在做门店盈利分析时，发现了一些离奇的数据：

工单号: WO20230315-HD-SH-001-0156
工时费: -500元
配件费: 0元
总金额: -500元

他以为是退款工单，但查看工单状态是"已完工"。

再查，发现系统里有大量类似的异常数据：

• 负数金额：工时费-500元、配件费-1200元
• 异常时长：维修时长0.01小时（36秒修好一台车？）
• 不合理值：客户年龄150岁、行驶里程500万公里

影响：这些脏数据导致：

• 财务报表错误
• 成本分析失真
• 绩效考核不准
• 库存管理混乱

根本原因：系统缺少数据有效性校验规则。

有效性规则设计：

完整检测脚本：

-- 数据有效性全面检查
SELECT 
    '金额异常' AS 异常类型,
    COUNT(*) AS 异常记录数,
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM work_orders), 2), '%') AS 异常占比
FROM work_orders
WHERE labor_fee < 0 OR parts_fee < 0 OR total_amount < 0

UNION ALL

SELECT 
    '时长异常',
    COUNT(*),
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM work_orders), 2), '%')
FROM work_orders
WHERE repair_hours < 0.1 OR repair_hours > 48

UNION ALL

SELECT 
    '日期异常',
    COUNT(*),
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM work_orders), 2), '%')
FROM work_orders
WHERE complete_time < '2020-01-01' 
   OR complete_time > NOW()
   OR complete_time < create_time  -- 完工时间早于创建时间

UNION ALL

SELECT 
    '状态值异常',
    COUNT(*),
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM work_orders), 2), '%')
FROM work_orders
WHERE status NOT IN ('待接车','维修中','已完工','已取消');

维度6：唯一性（Uniqueness）- 有重复数据吗？

定义：同一实体不应该有多条记录

真实灾难案例6：一个客户变成了三个人

客户张伟（化名）是某品牌的忠实用户，3年来在不同门店维修过6次。

但在CRM系统里，他有3个账户：

影响：

• 营销失败：因为系统认为他只消费过¥12,000，没达到VIP标准（¥30,000），所以没给他发VIP邀请
• 用户体验差：每次去新门店都要重新注册，客户感觉不被重视
• 数据分析错误：明明是1个忠诚客户，被算成了3个普通客户

实际情况：这个客户累计消费¥35,500，早就应该是VIP了，结果因为数据重复，白白流失。

唯一性检测SQL：

-- 检测客户表中的重复记录（基于手机号）
SELECT 
    phone_number AS 手机号,
    COUNT(DISTINCT customer_id) AS 重复账户数,
    GROUP_CONCAT(customer_id ORDER BY customer_id) AS 账户ID列表,
    SUM(total_spending) AS 累计消费总额
FROM customers
GROUP BY phone_number
HAVING COUNT(DISTINCT customer_id) > 1
ORDER BY 重复账户数 DESC, 累计消费总额 DESC
LIMIT 100;

唯一性修复策略：

1. 账户合并：将重复账户合并为主账户
2. 数据迁移：将历史数据迁移到主账户
3. 规则优化：建立唯一性约束，防止新增重复

数据清洗实战方法

方法1：规则清洗（Rule-based Cleaning）

适用场景：已知明确的数据质量规则

清洗步骤：

步骤1：识别脏数据

-- 创建脏数据标记表
CREATE TABLE data_quality_issues AS
SELECT 
    work_order_no,
    CASE 
        WHEN labor_fee < 0 THEN '负数工时费'
        WHEN repair_hours < 0.1 THEN '异常维修时长'
        WHEN complete_time < create_time THEN '完工早于创建'
        ELSE NULL
    END AS issue_type,
    labor_fee AS original_value
FROM work_orders
WHERE labor_fee < 0 
   OR repair_hours < 0.1 
   OR complete_time < create_time;

步骤2：制定清洗规则

步骤3：执行清洗

-- 清洗负数金额
UPDATE work_orders 
SET labor_fee = ABS(labor_fee),
    data_quality_flag = 'CLEANED_NEG_AMOUNT',
    last_clean_time = NOW()
WHERE labor_fee < 0;

-- 清洗异常时长（用中位数替代）
UPDATE work_orders wo
JOIN (
    SELECT 
        store_id,
        fault_code,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY repair_hours) AS median_hours
    FROM work_orders
    WHERE repair_hours BETWEEN 0.5 AND 24
    GROUP BY store_id, fault_code
) med ON [wo.store](http://wo.store)_id = [med.store](http://med.store)_id AND wo.fault_code = med.fault_code
SET [wo.repair](http://wo.repair)_hours = med.median_hours,
    [wo.data](http://wo.data)_quality_flag = 'CLEANED_ABNORMAL_HOURS',
    wo.last_clean_time = NOW()
WHERE [wo.repair](http://wo.repair)_hours < 0.1 OR [wo.repair](http://wo.repair)_hours > 48;

方法2：去重清洗（Deduplication）

客户去重实战：

-- 步骤1：找出重复记录（基于手机号+姓名的模糊匹配）
CREATE TABLE duplicate_customers AS
SELECT 
    c1.customer_id AS master_id,
    c2.customer_id AS duplicate_id,
    [c1.phone](http://c1.phone)_number,
    c1.customer_name,
    c1.register_date AS master_register_date,
    c2.register_date AS duplicate_register_date,
    [c1.total](http://c1.total)_spending + [c2.total](http://c2.total)_spending AS merged_spending
FROM customers c1
JOIN customers c2 
    ON [c1.phone](http://c1.phone)_number = [c2.phone](http://c2.phone)_number 
    AND c1.customer_id < c2.customer_id  -- 保证不重复配对
    AND SOUNDEX(c1.customer_name) = SOUNDEX(c2.customer_name);  -- 姓名相似

-- 步骤2：迁移历史数据到主账户
UPDATE work_orders
SET customer_id = (
    SELECT master_id 
    FROM duplicate_customers 
    WHERE duplicate_customers.duplicate_id = work_orders.customer_id
)
WHERE customer_id IN (SELECT duplicate_id FROM duplicate_customers);

-- 步骤3：合并账户数据
UPDATE customers c
JOIN (
    SELECT master_id, SUM(merged_spending) AS total
    FROM duplicate_customers
    GROUP BY master_id
) agg ON c.customer_id = agg.master_id
SET [c.total](http://c.total)_spending = [agg.total](http://agg.total),
    c.last_update_time = NOW();

-- 步骤4：删除重复账户
DELETE FROM customers
WHERE customer_id IN (SELECT duplicate_id FROM duplicate_customers);

方法3：自动化质量监控

建立每日自动检查脚本：

# Python脚本示例
import pymysql
import pandas as pd
from datetime import datetime, timedelta

def daily_data_quality_check():
    # 连接数据库
    conn = pymysql.connect(host='[db.example.com](http://db.example.com)', user='analyst', password='***')

    # 检查1：完整性
    completeness_check = [pd.read](http://pd.read)_sql("""
        SELECT 
            'technician_id' AS field,
            COUNT(*) AS total,
            SUM(CASE WHEN technician_id IS NULL THEN 1 ELSE 0 END) AS missing,
            ROUND(SUM(CASE WHEN technician_id IS NOT NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS completeness
        FROM work_orders
        WHERE DATE(complete_time) = CURDATE() - INTERVAL 1 DAY
    """, conn)

    # 检查2：有效性
    validity_check = [pd.read](http://pd.read)_sql("""
        SELECT COUNT(*) AS invalid_records
        FROM work_orders
        WHERE DATE(complete_time) = CURDATE() - INTERVAL 1 DAY
          AND (labor_fee < 0 OR repair_hours < 0.1 OR repair_hours > 48)
    """, conn)

    # 检查3：及时性
    timeliness_check = [pd.read](http://pd.read)_sql("""
        SELECT 
            store_id,
            MAX(DATE(complete_time)) AS latest_date,
            DATEDIFF(CURDATE(), MAX(DATE(complete_time))) AS delay_days
        FROM work_orders
        GROUP BY store_id
        HAVING delay_days > 1
    """, conn)

    # 生成报告
    report = f"""
    数据质量日报 - {[datetime.now](http://datetime.now)().strftime('%Y-%m-%d')}

    1. 完整性检查:
    {completeness_[check.to](http://check.to)_string()}

    2. 有效性检查:
    异常记录数: {validity_check['invalid_records'].values[0]}

    3. 及时性检查:
    延迟门店数: {len(timeliness_check)}
    {timeliness_[check.to](http://check.to)_string() if len(timeliness_check) > 0 else '所有门店数据及时'}
    """

    # 发送邮件/钉钉通知
    send_alert(report)

    conn.close()

if __name__ == '__main__':
    daily_data_quality_check()

数据质量评估综合案例

场景：某车企2024年Q1发现客户满意度数据异常

问题发现：

• 华南区满意度突然从92分降到78分
• 但客户投诉并未增加
• 门店反馈服务质量没有变化

质量评估过程：

第1步：完整性检查 ✅ 通过（无缺失）

第2步：准确性检查 ❌ 发现问题

• 抽查100条记录与原始问卷对比
• 发现35%的5分制评分被错误记录为10分制

第3步：及时性检查 ✅ 通过

第4步：一致性检查 ✅ 通过

第5步：有效性检查 ❌ 发现问题

• 发现评分出现了6、7、8、9、10分（应该只有1-5分）

第6步：唯一性检查 ✅ 通过

根本原因：2月份系统升级，华南区的评分字段从5分制改成了10分制，但没有同步修改其他系统

修复方案：

1. 将10分制数据转换回5分制（score_new = score_old / 2）
2. 重新计算2-4月的满意度数据
3. 建立评分范围校验规则，防止再次发生

修复后结果：华南区满意度恢复到91分，符合实际情况

今日思考题

1. 在你负责的业务中，哪个数据质量维度的问题最严重？为什么？
2. 如果让你设计一套数据质量自动监控系统，你会监控哪些指标？
3. 数据清洗时，什么情况下应该删除脏数据，什么情况下应该修复？

明日预告：Day 37，我们将进入数据建模与指标体系设计，学习如何构建售后业务的完整数据模型和指标体系。