MySQL 8.0中基于还款累计额计算指定日期的未偿余额

还款表（repayments）结构 #

规则说明 #

• 客户可在任意日期还款任意金额，同一客户每月可进行2次还款
• disbursed_date为放款日期（早于首笔EMI），同一cus_id的放款日期一致
• 每个客户的总欠款为按cus_id分组的principal总和：客户1总欠款50，客户2总欠款100，客户3总欠款100

需求与预期结果 #

需要计算每个due_date对应的未偿余额，预期结果如下：

计算逻辑示例 #

• 2022年2月1日时，客户1已还2笔各10的EMI，未偿余额为50-(10+10)=30
• 2022年3月1日时，客户1已还3笔10，客户2于2022年2月15日放款100，未偿余额为(50-30)+100=120
• 2022年3月15日时，客户2已还20的EMI，未偿余额为(50-30)+(100-20)=100
• 2022年4月1日时，客户1已还4笔10，客户2已还1笔20，客户3于2022年3月20日放款100且已还15，未偿余额为(50-40)+(100-20)+(100-15)=175

尝试的SQL（存在问题） #

之前尝试的SQL未考虑disbursed_date，导致计算错误，代码如下：

select *, (osp_as_on - principal) balance from (
    select due_date, principal, sum(net_repayment) over(order by due_date desc) osp_as_on from (
        select due_date, principal, sum(principal) net_repayment
            from repayments
        group by 1
    ) t1 
) t2 order by 1;

解决方案（MySQL 8.0） #

核心逻辑是：仅在放款日期之后将客户总欠款纳入计算，同时累计截至当前日期的所有还款金额，最终用已放款总欠款减去累计还款得到未偿余额。具体SQL代码如下：

WITH customer_totals AS (
    -- 聚合每个客户的总欠款和放款日期
    SELECT 
        cus_id,
        SUM(principal) AS total_debt,
        disbursed_date
    FROM repayments
    GROUP BY cus_id, disbursed_date
),
all_dates AS (
    -- 提取所有唯一到期日并按时间排序
    SELECT DISTINCT due_date AS date_as_of
    FROM repayments
    ORDER BY STR_TO_DATE(date_as_of, '%d-%m-%Y')
),
cumulative_repayments AS (
    -- 计算截至每个日期的累计还款总额
    SELECT 
        ad.date_as_of,
        SUM(r.principal) AS total_repaid
    FROM all_dates ad
    LEFT JOIN repayments r 
        ON STR_TO_DATE(r.due_date, '%d-%m-%Y') <= STR_TO_DATE(ad.date_as_of, '%d-%m-%Y')
    GROUP BY ad.date_as_of
),
total_eligible_debt AS (
    -- 计算每个日期所有已放款客户的总欠款之和
    SELECT 
        ad.date_as_of,
        SUM(ct.total_debt) AS total_eligible
    FROM all_dates ad
    LEFT JOIN customer_totals ct 
        ON STR_TO_DATE(ct.disbursed_date, '%d-%m-%Y') <= STR_TO_DATE(ad.date_as_of, '%d-%m-%Y')
    GROUP BY ad.date_as_of
)
-- 最终计算未偿余额
SELECT 
    ad.date_as_of,
    COALESCE(ted.total_eligible, 0) - COALESCE(cr.total_repaid, 0) AS outstanding
FROM all_dates ad
LEFT JOIN cumulative_repayments cr ON ad.date_as_of = cr.date_as_of
LEFT JOIN total_eligible_debt ted ON ad.date_as_of = ted.date_as_of
ORDER BY STR_TO_DATE(ad.date_as_of, '%d-%m-%Y');

代码说明 #

• customer_totals：一次性计算每个客户的总欠款和对应的放款日期，避免重复计算
• all_dates：生成所有需要计算的时间节点（即所有到期日）
• cumulative_repayments：统计截至每个日期的所有还款金额总和
• total_eligible_debt：统计每个日期前已放款的所有客户总欠款之和
• 最后通过COALESCE处理空值，确保未放款或无还款时计算正确，最终得到符合预期的未偿余额

内容的提问来源于stack exchange，提问作者iamawesome