2. SQL基础回顾：SELECT、WHERE、GROUP BY、HAVING、ORDER BY、JOIN等核心语法在催收数据中的应用

在催收特征筛选工作中，SQL是提取、清洗、聚合和关联数据的核心工具。无论是计算逾期天数、统计客户历史还款行为，还是拼接多张业务表以构建完整的特征宽表，都离不开SQL基础语法的灵活运用。本节将结合催收业务场景，系统回顾最常用的SQL核心语法。

2.1 数据查询与过滤：SELECT + WHERE

SELECT 用于指定要查询的字段，WHERE 用于按条件过滤记录。在催收数据中，最常见的过滤条件是逾期状态、案件阶段、客户分组等。

SELECT 
    case_id,
    customer_name,
    overdue_days,
    current_balance
FROM overdue_case
WHERE overdue_days > 30
  AND current_balance > 5000;

2.2 数据聚合与分组：GROUP BY + HAVING

GROUP BY 用于按一个或多个字段对数据进行分组，配合聚合函数（如 COUNT、SUM、AVG、MAX、MIN）计算每组的统计值。HAVING 用于对分组后的结果进行过滤，类似于 WHERE 但作用于聚合后的数据。

SELECT 
    collector_id,
    COUNT(case_id) AS high_risk_case_count,
    AVG(current_balance) AS avg_balance
FROM overdue_case
WHERE overdue_days > 60
GROUP BY collector_id
HAVING COUNT(case_id) > 10
ORDER BY high_risk_case_count DESC;

2.3 数据排序：ORDER BY

ORDER BY 用于对查询结果按一个或多个字段进行升序（ASC）或降序（DESC）排序。在催收特征筛选中，排序常用于查看风险最高的案件、最紧急的待办事项等。

-- 按逾期天数降序排列，查看风险最高的前20个案件
SELECT 
    case_id,
    customer_name,
    overdue_days,
    current_balance
FROM overdue_case
WHERE case_status = '待催收'
ORDER BY overdue_days DESC, current_balance DESC
LIMIT 20;

2.4 多表关联：JOIN

催收数据通常分散在多张表中，如客户信息表、案件表、还款记录表、催收通话记录表等。JOIN 用于将这些表按关联键连接起来，构建包含完整信息的特征宽表。

SELECT 
    oc.case_id,
    oc.overdue_days,
    oc.current_balance,
    ci.customer_name,
    ci.credit_score,
    ci.income_level,
    rp.last_repayment_date,
    rp.last_repayment_amount
FROM overdue_case oc
INNER JOIN customer_info ci ON oc.customer_id = ci.customer_id
LEFT JOIN (
    SELECT 
        customer_id,
        MAX(repayment_date) AS last_repayment_date,
        MAX(repayment_amount) AS last_repayment_amount
    FROM repayment_record
    GROUP BY customer_id
) rp ON oc.customer_id = rp.customer_id
WHERE oc.overdue_days > 30;

2.5 子查询与公用表表达式（CTE）

在催收特征筛选过程中，经常需要先计算一些中间统计量，再基于这些统计量进行进一步筛选。子查询和 CTE 是组织复杂查询的两种有效方式。

-- 使用 CTE 方式（推荐，可读性更好）
WITH collector_avg AS (
    SELECT 
        collector_id,
        AVG(current_balance) AS avg_balance
    FROM overdue_case
    WHERE overdue_days > 0
    GROUP BY collector_id
)
SELECT 
    oc.case_id,
    oc.collector_id,
    oc.current_balance,
    ca.avg_balance
FROM overdue_case oc
INNER JOIN collector_avg ca ON oc.collector_id = ca.collector_id
WHERE oc.current_balance > ca.avg_balance
ORDER BY oc.collector_id, oc.current_balance DESC;

2.6 综合实战：构建催收特征基础宽表

将上述语法综合运用，构建一个用于催收模型训练的客户特征宽表。该宽表将包含客户基本信息、逾期行为特征、历史还款特征和催收交互特征。

WITH 
-- 1. 客户基础信息
customer_base AS (
    SELECT 
        customer_id,
        customer_name,
        credit_score,
        income_level,
        city,
        registration_date
    FROM customer_info
    WHERE is_active = 1
),
-- 2. 逾期行为特征
overdue_features AS (
    SELECT 
        customer_id,
        COUNT(case_id) AS total_overdue_cases,
        MAX(overdue_days) AS max_overdue_days,
        AVG(overdue_days) AS avg_overdue_days,
        SUM(current_balance) AS total_overdue_balance,
        AVG(current_balance) AS avg_overdue_balance
    FROM overdue_case
    WHERE case_status IN ('待催收', '催收中')
    GROUP BY customer_id
),
-- 3. 历史还款特征
repayment_features AS (
    SELECT 
        customer_id,
        COUNT(repayment_id) AS total_repayment_count,
        SUM(repayment_amount) AS total_repayment_amount,
        AVG(repayment_amount) AS avg_repayment_amount,
        MAX(repayment_date) AS last_repayment_date,
        DATEDIFF(CURRENT_DATE, MAX(repayment_date)) AS days_since_last_repayment
    FROM repayment_record
    GROUP BY customer_id
)
-- 4. 关联生成特征宽表
SELECT 
    cb.customer_id,
    cb.customer_name,
    cb.credit_score,
    cb.income_level,
    cb.city,
    COALESCE(of.total_overdue_cases, 0) AS total_overdue_cases,
    COALESCE(of.max_overdue_days, 0) AS max_overdue_days,
    COALESCE(of.avg_overdue_days, 0) AS avg_overdue_days,
    COALESCE(of.total_overdue_balance, 0) AS total_overdue_balance,
    COALESCE(rf.total_repayment_count, 0) AS total_repayment_count,
    COALESCE(rf.total_repayment_amount, 0) AS total_repayment_amount,
    rf.days_since_last_repayment
FROM customer_base cb
LEFT JOIN overdue_features of ON cb.customer_id = of.customer_id
LEFT JOIN repayment_features rf ON cb.customer_id = rf.customer_id
WHERE of.total_overdue_cases IS NOT NULL  -- 只保留有逾期记录的客户
ORDER BY total_overdue_balance DESC;

2.7 本章小结

本节系统回顾了SQL核心语法在催收数据中的具体应用：

• SELECT + WHERE：基础数据提取与条件过滤，注意索引优化和NULL处理
• GROUP BY + HAVING：分组聚合统计，用于计算催收员绩效、逾期区间分布等
• ORDER BY：结果排序，常用于风险排序和优先级管理
• JOIN：多表关联，构建特征宽表的核心手段，注意关联键的唯一性
• 子查询与CTE：组织复杂查询，提升可读性和维护性