3. 坏账率核心指标SQL：分子与分母的精确计算

坏账率是信贷与应收账款管理中最核心的风险指标之一。其计算逻辑看似简单——坏账金额 ÷ 总应收金额，但在实际数据仓库环境中，分子与分母的定义口径、数据来源、时间窗口以及异常值处理，直接决定了指标的准确性与可信度。本节将给出标准化的基础SQL实现，并逐层拆解每个字段的业务含义与技术约束。

3.1 指标定义与口径说明

在编写SQL之前，必须明确分子与分母的业务边界。下表列出了常见口径及其适用场景：

3.2 分子SQL：逾期/坏账金额计算

分子需要从还款计划表中筛选出逾期记录，并汇总其未还金额。以下SQL实现了按产品、按日期的逾期金额聚合：

WITH overdue_detail AS (
    SELECT
        rs.loan_id,
        rs.due_date,
        rs.principal_due + rs.interest_due + rs.penalty_due AS total_due,
        COALESCE(rp.total_paid, 0) AS total_paid,
        DATEDIFF('day', rs.due_date, CURRENT_DATE) AS overdue_days,
        CASE
            WHEN DATEDIFF('day', rs.due_date, CURRENT_DATE) >= 30
                THEN (rs.principal_due + rs.interest_due + rs.penalty_due)
                     - COALESCE(rp.total_paid, 0)
            ELSE 0
        END AS overdue_amount_30plus
    FROM repayment_schedule rs
    LEFT JOIN (
        SELECT
            loan_id,
            due_date,
            SUM(paid_amount) AS total_paid
        FROM repayment_record
        GROUP BY loan_id, due_date
    ) rp
        ON rs.loan_id = rp.loan_id
        AND rs.due_date = rp.due_date
    WHERE rs.due_date <= CURRENT_DATE
)
SELECT
    CURRENT_DATE AS report_date,
    SUM(overdue_amount_30plus) AS total_overdue_amount
FROM overdue_detail
WHERE overdue_amount_30plus > 0;

3.3 分母SQL：总应收金额计算

分母通常取报告日所有在贷资产的本金余额。以下SQL从贷款主表中获取当前有效贷款的本金合计：

WITH active_loans AS (
    SELECT
        loan_id,
        product_type,
        principal_amount,
        repaid_principal,
        (principal_amount - repaid_principal) AS outstanding_principal,
        loan_status
    FROM loan_detail
    WHERE loan_status IN ('ACTIVE', 'OVERDUE', 'RESTRUCTURED')
      AND disbursement_date <= CURRENT_DATE
)
SELECT
    CURRENT_DATE AS report_date,
    COUNT(loan_id) AS total_loan_count,
    SUM(outstanding_principal) AS total_outstanding_principal
FROM active_loans;

3.4 坏账率核心指标SQL（完整版）

将分子与分母合并，得到最终的坏账率指标。同时加入分组维度，支持按产品、按渠道、按地区下钻分析：

WITH
-- 分子：逾期30天+金额（按产品分组）
overdue_by_product AS (
    SELECT
        ld.product_type,
        SUM(
            CASE
                WHEN DATEDIFF('day', rs.due_date, CURRENT_DATE) >= 30
                    THEN (rs.principal_due + rs.interest_due + rs.penalty_due)
                         - COALESCE(rp.total_paid, 0)
                ELSE 0
            END
        ) AS overdue_amount
    FROM repayment_schedule rs
    JOIN loan_detail ld ON rs.loan_id = ld.loan_id
    LEFT JOIN (
        SELECT loan_id, due_date, SUM(paid_amount) AS total_paid
        FROM repayment_record
        GROUP BY loan_id, due_date
    ) rp ON rs.loan_id = rp.loan_id AND rs.due_date = rp.due_date
    WHERE rs.due_date <= CURRENT_DATE
    GROUP BY ld.product_type
),
-- 分母：期末在贷余额（按产品分组）
outstanding_by_product AS (
    SELECT
        product_type,
        SUM(principal_amount - repaid_principal) AS outstanding_principal
    FROM loan_detail
    WHERE loan_status IN ('ACTIVE', 'OVERDUE', 'RESTRUCTURED')
      AND disbursement_date <= CURRENT_DATE
    GROUP BY product_type
)
SELECT
    COALESCE(o.product_type, d.product_type) AS product_type,
    CURRENT_DATE AS report_date,
    ROUND(COALESCE(d.overdue_amount, 0), 2) AS overdue_amount,
    ROUND(COALESCE(o.outstanding_principal, 0), 2) AS outstanding_principal,
    ROUND(
        CASE
            WHEN COALESCE(o.outstanding_principal, 0) = 0 THEN 0
            ELSE COALESCE(d.overdue_amount, 0) / o.outstanding_principal * 100
        END,
        4
    ) AS bad_debt_rate_pct
FROM outstanding_by_product o
FULL OUTER JOIN overdue_by_product d ON o.product_type = d.product_type
ORDER BY bad_debt_rate_pct DESC;

3.5 坏账率计算数据流架构

下图展示了从源表到最终指标的数据流转过程，以及每个环节的数据质量校验点：

3.6 常见数据质量陷阱与规避

• 重复还款记录：同一笔还款被录入多次，导致分子被低估。应在 repayment_record 层按 loan_id + due_date + payment_seq 去重。
• 逾期天数计算基准不一致：部分系统使用自然日，部分使用工作日。建议在ETL层统一为业务日历，并在指标元数据中标注。
• 分母包含已核销贷款：已核销贷款不应计入分母，否则会低估坏账率。需在 loan_detail 中过滤 loan_status != 'WRITTEN_OFF'。
• 跨期还款冲抵：客户提前还款或部分还款时，需按合同约定的冲抵顺序（先息后本）分摊到各期，避免分子计算偏差。