2. SQL 基础回顾与催收数据环境：SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY 在催收数据表上的应用

在进入催收特征工程之前，必须牢固掌握 SQL 核心查询语法。催收数据通常存储在关系型数据库中，涉及客户信息表、账户表、还款计划表、催收记录表等多个实体。本章通过催收场景中的真实表结构，逐一演示 SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY 的用法，帮助你在实际催收数据环境中快速上手。

2.1 催收核心数据表结构

以下四张表是催收分析中最常用的基础表，后续所有 SQL 示例均基于此结构。

2.2 SELECT 与 WHERE：筛选逾期账户

SELECT 用于指定返回的列，WHERE 用于按条件过滤行。在催收中，最常见的操作是找出所有逾期账户。

-- 查询所有逾期账户的ID、客户ID、余额和到期日
SELECT acct_id, cust_id, balance, due_date
FROM loan_account
WHERE status = 'OVERDUE';

多条件组合查询：

-- 查询逾期超过30天且余额大于5000的账户
SELECT acct_id, cust_id, balance, due_date
FROM loan_account
WHERE status = 'OVERDUE'
  AND DATEDIFF(CURRENT_DATE, due_date) > 30
  AND balance > 5000;

2.3 JOIN：关联客户与催收记录

催收分析很少只查单表。JOIN 用于将多张表按关联键合并。下图展示了催收数据中四张表的关联关系：

INNER JOIN 示例：查询逾期账户最近7天的催收记录。

SELECT a.acct_id, a.cust_id, a.balance, c.call_date, c.call_result, c.duration_sec
FROM loan_account a
INNER JOIN collection_log c ON a.acct_id = c.acct_id
WHERE a.status = 'OVERDUE'
  AND c.call_date >= CURRENT_DATE - INTERVAL 7 DAY;

LEFT JOIN 示例：查询所有逾期账户及其最近一期还款计划，无还款计划的账户也保留。

SELECT a.acct_id, a.balance, p.due_date, p.prin_due, p.paid_flag
FROM loan_account a
LEFT JOIN repay_plan p ON a.acct_id = p.acct_id AND p.paid_flag = 'N'
WHERE a.status = 'OVERDUE';

2.4 GROUP BY 与 HAVING：按维度聚合催收指标

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

催收场景：统计每个催收员本周的通话量、接通量和承诺还款量。

SELECT agent_id,
       COUNT(*) AS total_calls,
       SUM(CASE WHEN call_result = 'CONTACTED' THEN 1 ELSE 0 END) AS contacted_cnt,
       SUM(CASE WHEN call_result = 'PROMISE' THEN 1 ELSE 0 END) AS promise_cnt,
       ROUND(AVG(duration_sec), 0) AS avg_duration
FROM collection_log
WHERE call_date >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY agent_id;

HAVING 过滤：只保留接通率低于 40% 的催收员。

SELECT agent_id,
       COUNT(*) AS total_calls,
       SUM(CASE WHEN call_result = 'CONTACTED' THEN 1 ELSE 0 END) AS contacted_cnt,
       ROUND(SUM(CASE WHEN call_result = 'CONTACTED' THEN 1 ELSE 0 END) / COUNT(*), 2) AS contact_rate
FROM collection_log
WHERE call_date >= CURRENT_DATE - INTERVAL 7 DAY
GROUP BY agent_id
HAVING contact_rate < 0.40;

2.5 ORDER BY：排序与优先级队列

ORDER BY 用于对结果集按指定列排序，默认升序（ASC），可指定降序（DESC）。催收中常用于生成催收优先级队列。

-- 按逾期天数降序、余额降序排列，生成催收优先级清单
SELECT acct_id, cust_id, balance, due_date,
       DATEDIFF(CURRENT_DATE, due_date) AS overdue_days
FROM loan_account
WHERE status = 'OVERDUE'
ORDER BY overdue_days DESC, balance DESC;

多字段排序时，先按第一个字段排序，相同值再按第二个字段排序。

2.6 综合实战：催收日报统计

将上述所有子句组合，生成一份完整的催收日报统计：

SELECT a.acct_id,
       c.name,
       a.balance,
       DATEDIFF(CURRENT_DATE, a.due_date) AS overdue_days,
       COUNT(cl.log_id) AS total_calls,
       SUM(CASE WHEN cl.call_result = 'CONTACTED' THEN 1 ELSE 0 END) AS contacted_calls,
       MAX(cl.call_date) AS last_call_date
FROM loan_account a
INNER JOIN customer c ON a.cust_id = c.cust_id
LEFT JOIN collection_log cl ON a.acct_id = cl.acct_id
    AND cl.call_date >= CURRENT_DATE - INTERVAL 30 DAY
WHERE a.status = 'OVERDUE'
  AND a.balance > 1000
GROUP BY a.acct_id, c.name, a.balance, a.due_date
HAVING contacted_calls < 3
ORDER BY overdue_days DESC, balance DESC
LIMIT 100;

该查询的含义：提取逾期且余额大于1000的账户，关联客户姓名和近30天催收记录，只保留接通次数少于3次的账户，按逾期天数和余额降序排列，取前100条作为今日重点催收清单。

2.7 本章小结