3. SQL 基础回顾：SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY、窗口函数基础

在信用评分模型的特征工程中，SQL 是提取、清洗和聚合数据的核心工具。本章将系统回顾 SQL 的六大基础子句及窗口函数，并结合信用评分场景说明其应用要点。

3.1 核心子句速览

3.2 SELECT 与 WHERE：精准定位风险样本

SELECT 用于定义输出字段，WHERE 用于行级过滤。在信用评分中，通常先通过 WHERE 缩小分析范围，再提取特征。

-- 提取近6个月有逾期记录的客户ID及最大逾期天数
SELECT 
    customer_id,
    MAX(dpd_days) AS max_dpd
FROM repayment_history
WHERE 
    record_date >= DATEADD(MONTH, -6, GETDATE())
    AND dpd_days > 0
GROUP BY customer_id;

3.3 JOIN：多表特征关联

信用评分模型通常需要整合客户基本信息表、交易流水表、还款计划表、外部征信表等。JOIN 是连接这些表的桥梁。

-- 关联客户主表与最近3个月交易汇总
SELECT 
    c.customer_id,
    c.credit_score,
    t.total_trans_amount,
    t.trans_count
FROM customer_master c
LEFT JOIN (
    SELECT 
        customer_id,
        SUM(amount) AS total_trans_amount,
        COUNT(*) AS trans_count
    FROM transaction_log
    WHERE trans_date >= DATEADD(MONTH, -3, GETDATE())
    GROUP BY customer_id
) t ON c.customer_id = t.customer_id;

3.4 GROUP BY 与 HAVING：聚合特征与风险过滤

GROUP BY 将数据按客户分组，计算聚合指标（如总借款额、平均逾期天数、最大负债率）。HAVING 在聚合后过滤高风险群体。

-- 提取高风险客户：总借款超过50万且平均逾期天数 > 30
SELECT 
    customer_id,
    SUM(loan_amount) AS total_loan,
    AVG(dpd_days) AS avg_dpd
FROM loan_records
GROUP BY customer_id
HAVING 
    SUM(loan_amount) > 500000
    AND AVG(dpd_days) > 30;

3.5 ORDER BY：排序输出

ORDER BY 用于对最终结果排序。在信用评分中，常用于按风险等级或评分值排序，便于人工审核。

-- 按风险评分升序排列，输出前100名高风险客户
SELECT 
    customer_id,
    risk_score
FROM customer_risk
ORDER BY risk_score ASC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

3.6 窗口函数基础：滚动特征与排名

窗口函数在不减少行数的情况下，为每一行计算基于分组或排序的分析值。在信用评分中，窗口函数常用于计算滚动逾期率、历史最大负债、客户行为排名等。

-- 计算每个客户按时间排序的滚动平均逾期天数（前3笔）
SELECT 
    customer_id,
    record_date,
    dpd_days,
    AVG(dpd_days) OVER (
        PARTITION BY customer_id 
        ORDER BY record_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_avg_dpd
FROM repayment_history;

3.7 SQL 子句执行顺序与优化

理解 SQL 子句的逻辑执行顺序，有助于写出高效的信用评分特征提取脚本。

3.8 信用评分特征提取中的子句组合示例

以下示例展示如何组合多个子句，提取用于信用评分模型的关键特征。

-- 提取客户级特征：近12个月行为汇总
SELECT 
    c.customer_id,
    c.age,
    c.income_level,
    COALESCE(SUM(l.loan_amount), 0) AS total_loan_12m,
    COALESCE(AVG(l.dpd_days), 0) AS avg_dpd_12m,
    MAX(l.dpd_days) AS max_dpd_12m,
    COUNT(l.loan_id) AS loan_count_12m,
    -- 窗口函数：最近一次逾期天数
    LAST_VALUE(l.dpd_days) OVER (
        PARTITION BY c.customer_id 
        ORDER BY l.loan_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_dpd
FROM customer_master c
LEFT JOIN loan_records l 
    ON c.customer_id = l.customer_id
    AND l.loan_date >= DATEADD(YEAR, -1, GETDATE())
WHERE c.is_active = 1
GROUP BY 
    c.customer_id, c.age, c.income_level, l.loan_date, l.dpd_days
HAVING COUNT(l.loan_id) >= 1
ORDER BY total_loan_12m DESC;