4. 数据清洗与预处理SQL：处理NULL值、去重、异常值过滤、日期格式统一、金额字段标准化

催收数据源通常来自贷后系统、第三方征信、通话记录等多个异构系统，原始数据普遍存在缺失、重复、异常、格式不一致等问题。本章聚焦催收场景下最核心的五个清洗步骤，确保下游特征工程建立在干净、可靠的数据基础上。

4.1 处理NULL值：缺失不是空白，是信息

催收数据中的NULL值往往代表不同的业务含义，不能简单统一填充。例如：last_contact_date 为NULL可能表示从未联系成功，而 income 为NULL可能表示客户未提供或系统未采集。

字段类型	常见NULL场景	推荐处理方式
联系时间	从未联系、联系失败	填充为 '1900-01-01' 或保留NULL并单独标记
金额字段	未采集、无此费用	填充为 0 或使用中位数/均值
分类标签	未分类、未知	填充为 'UNKNOWN' 或 'OTHERS'
数值型指标	系统未记录	使用同组均值或中位数填充

-- 催收场景NULL处理示例
SELECT
    loan_id,
    COALESCE(last_contact_date, '1900-01-01') AS contact_date_filled,
    COALESCE(overdue_days, 0) AS overdue_days_filled,
    COALESCE(income, (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY income) FROM collection_raw)) AS income_filled,
    COALESCE(contact_result, 'UNKNOWN') AS result_filled
FROM collection_raw;

4.2 去重：同一笔逾期，只留一条

催收系统中，同一笔贷款可能因多次催收动作、多系统同步产生重复记录。去重前必须明确“唯一键”定义。

• 业务主键：通常为 loan_id + batch_date 或 loan_id + contact_date
• 保留规则：取最新记录（按催收时间）、或取最完整记录（NULL字段最少）
• 去重时机：在聚合特征之前，避免重复计算导致指标虚高

-- 使用ROW_NUMBER去重，保留最新催收记录
WITH deduped AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY loan_id, batch_date
               ORDER BY collection_time DESC, update_time DESC
           ) AS rn
    FROM collection_raw
)
SELECT * FROM deduped WHERE rn = 1;

4.3 异常值过滤：识别并处理催收中的离群点

催收数据中的异常值通常来自录入错误、系统Bug或极端个案。例如：逾期天数出现负数、联系次数超过1000次、金额字段出现天文数字。

异常类型	典型字段	过滤规则
业务逻辑异常	overdue_days	必须 >= 0 且 <= 合同期限
统计离群点	contact_count	超过均值+3倍标准差 或 超过99.5%分位数
金额异常	overdue_amount	必须 > 0 且 <= 合同金额的1.5倍
日期异常	due_date	不能早于放款日，不能晚于当前日期+30天

-- 异常值过滤综合示例
SELECT *
FROM collection_raw
WHERE overdue_days BETWEEN 0 AND 365
  AND contact_count BETWEEN 0 AND 200
  AND overdue_amount BETWEEN 0 AND (SELECT MAX(loan_amount) * 1.5 FROM loan_info)
  AND due_date BETWEEN loan_date AND CURRENT_DATE + INTERVAL '30 days';

4.4 日期格式统一：让时间特征可计算

催收数据中日期格式五花八门：'2024-01-15'、'2024/01/15'、'15-JAN-2024'、'20240115'。统一格式后才能计算逾期天数、间隔天数等关键时间特征。

-- 日期格式统一标准化
SELECT
    loan_id,
    -- 统一为标准DATE类型
    CASE
        WHEN due_date ~ '^\d{4}-\d{2}-\d{2}$' THEN due_date::DATE
        WHEN due_date ~ '^\d{4}/\d{2}/\d{2}$' THEN TO_DATE(due_date, 'YYYY/MM/DD')
        WHEN due_date ~ '^\d{8}$' THEN TO_DATE(due_date, 'YYYYMMDD')
        WHEN due_date ~ '^\d{2}-[A-Z]{3}-\d{4}$' THEN TO_DATE(due_date, 'DD-MON-YYYY')
        ELSE NULL
    END AS due_date_std,
    -- 提取时间维度
    EXTRACT(YEAR FROM due_date_std) AS due_year,
    EXTRACT(MONTH FROM due_date_std) AS due_month,
    EXTRACT(DOW FROM due_date_std) AS due_day_of_week
FROM collection_raw;

4.5 金额字段标准化：统一单位与精度

催收金额字段常出现单位不一致（元 vs 分）、精度不一致（整数 vs 两位小数）、符号不一致（正负表示方向）。标准化后才能进行加总、平均等聚合计算。

问题	示例	标准化规则
单位不统一	10000 元 vs 1000000 分	统一转为“元”，分转元除以100
精度不一致	100 vs 100.00 vs 100.000	统一保留两位小数，使用 NUMERIC(18,2)
符号歧义	正数表示欠款，负数表示还款	统一为正数表示欠款金额，另建方向字段
空字符串	'' 被误认为0	先转为NULL，再按NULL规则处理

-- 金额字段标准化
SELECT
    loan_id,
    -- 统一单位为元，保留两位小数
    CASE
        WHEN amount_unit = 'cent' THEN ROUND(amount / 100.0, 2)
        WHEN amount_unit = 'yuan' THEN ROUND(amount::NUMERIC, 2)
        ELSE ROUND(amount::NUMERIC, 2)
    END AS amount_std,
    -- 统一方向：正数为欠款
    CASE
        WHEN amount_type = 'repay' THEN amount_std * -1
        ELSE amount_std
    END AS amount_direction_std
FROM collection_raw;

4.6 清洗流程总览

以下SVG图展示了催收数据清洗的完整流水线，五个步骤按顺序执行，每一步的输出作为下一步的输入。

4.7 清洗质量检查清单

完成清洗后，建议执行以下检查，确保数据质量达标：

• NULL率检查：每个字段的NULL比例是否在预期范围内
• 重复率检查：去重后主键唯一性是否满足100%
• 异常值检查：过滤后字段的最大/最小值是否在业务合理区间
• 格式检查：日期字段是否全部为DATE类型，金额字段是否全部为NUMERIC(18,2)
• 一致性检查：关联字段（如loan_id）能否与主表完美JOIN

-- 清洗质量检查SQL示例
SELECT
    'NULL率' AS check_type,
    COUNT(*) AS total_rows,
    SUM(CASE WHEN overdue_days IS NULL THEN 1 ELSE 0 END) AS null_count,
    ROUND(100.0 * SUM(CASE WHEN overdue_days IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS null_pct
FROM collection_cleaned
UNION ALL
SELECT
    '重复率',
    COUNT(*),
    COUNT(*) - COUNT(DISTINCT loan_id || '-' || batch_date),
    ROUND(100.0 * (COUNT(*) - COUNT(DISTINCT loan_id || '-' || batch_date)) / COUNT(*), 2)
FROM collection_cleaned;