如何用Oracle SQL查找首个未全额覆盖债务的月份

业务场景 #

某用户7月产生10美元债务并还款5美元，8月产生5美元债务并还款5美元，9月产生5美元债务并还款2美元，10月产生2美元债务未还款。规则为当月未还清的债务需用后续月份还款优先清偿，据此7月债务在8月全额清偿，但8月债务未全额覆盖，故8月为首个未覆盖债务月份。

现有表结构及测试数据 #

CREATE TABLE debt_payments (
    month VARCHAR2(10),
    debt_taken NUMBER,
    payment NUMBER
);

INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('July', 10, 5);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('August', 5, 5);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('September', 5, 2);
INSERT INTO debt_payments (month, debt_taken, payment) VALUES ('October', 2, 0);

现有SQL的问题 #

以下SQL仅能找出累积剩余债务大于0的首个月份，无法适配“优先清偿更早未偿债务”的业务逻辑，会错误返回July而非正确的August：

WITH debt_summary AS (
    SELECT 
        month,
        SUM(debt_taken) OVER (ORDER BY month) AS total_debt,
        SUM(payment) OVER (ORDER BY month) AS total_payment
    FROM 
        debt_payments
),
remaining_debt AS (
    SELECT 
        month,
        total_debt - total_payment AS remaining_debt
    FROM 
        debt_summary
)
SELECT 
    month
FROM 
    remaining_debt
WHERE 
    remaining_debt > 0
ORDER BY 
    month
FETCH FIRST ROW ONLY;  -- Get the first month where debt is uncovered

正确Oracle SQL脚本 #

WITH ordered_months AS (
    -- 按实际月份顺序排序，避免字符串排序的潜在问题
    SELECT 
        month,
        debt_taken,
        payment,
        TO_DATE(month, 'Month') AS month_dt,
        ROW_NUMBER() OVER (ORDER BY TO_DATE(month, 'Month')) AS rn
    FROM debt_payments
),
recursive_debt_tracking AS (
    -- 初始化第一个月的未偿情况
    SELECT 
        rn,
        month,
        month AS earliest_unpaid_month,
        GREATEST(debt_taken - payment, 0) AS total_unpaid
    FROM ordered_months
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归处理后续每个月，跟踪最早未偿债务的月份和总未偿金额
    SELECT 
        om.rn,
        om.month,
        CASE
            -- 如果当月还款足够覆盖之前的未偿金额，检查当月债务是否有剩余，有则最早未偿月份为当前月
            WHEN om.payment >= rdt.total_unpaid THEN 
                CASE WHEN om.debt_taken > (om.payment - rdt.total_unpaid) THEN om.month ELSE NULL END
            -- 还款不足以覆盖之前的未偿，最早未偿月份保持不变
            ELSE rdt.earliest_unpaid_month
        END AS earliest_unpaid_month,
        -- 计算新的总未偿金额：先还之前的未偿，剩余还款再还当月债务，未还清的部分累加
        GREATEST(rdt.total_unpaid - om.payment, 0) + 
        GREATEST(om.debt_taken - GREATEST(om.payment - rdt.total_unpaid, 0), 0) AS total_unpaid
    FROM recursive_debt_tracking rdt
    JOIN ordered_months om ON om.rn = rdt.rn + 1
)
-- 从所有存在未偿债务的记录中，取最早的未偿月份
SELECT DISTINCT earliest_unpaid_month AS first_uncovered_month
FROM recursive_debt_tracking
WHERE total_unpaid > 0
ORDER BY TO_DATE(earliest_unpaid_month, 'Month')
FETCH FIRST 1 ROW ONLY;

逻辑说明 #

1. ordered_months：将月份转换为日期类型并排序，确保按实际时间顺序处理数据。
2. recursive_debt_tracking：通过递归CTE逐月累加债务和还款，严格遵循“优先清偿更早未偿债务”的规则：
   • 每次处理当月时，先用还款覆盖之前的未偿债务，剩余还款再抵扣当月债务。
   • 跟踪当前未偿债务中最早的产生月份。
3. 最后从所有有未偿债务的记录中，筛选出最早的未偿月份，即为首个债务未被全额覆盖的月份。

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