MySQL中如何基于金额字段以累计方式确定发票的最终支付月份

现有表结构与数据 #

发票表（invoice） #

customer_id,invoice_id,invoice_month,payment_month,invoice_amount,payment_amt,balance_amt
1,1,"DEC 23","JAN 24",400.00,400.00,0.00
1,2,"DEC 23","JAN 24",600.00,600.00,0.00
1,3,"JAN 24","JAN 24",200.00,50.00,150.00
2,4,"SEP 23","MAY 24",800.00,800.00,0.00
2,5,"OCT 23","MAY 24",750.00,750.00,0.00
3,6,"FEB 24","MAY 24",925.00,525.00,400.00
4,7,"NOV 22","JUN 23",325.00,325.00,0.00
4,8,"DEC 22","JUN 23",425.00,425.00,0.00
5,9,"JUN 24",NULL,500.00,NULL,500.00

支付表（payment） #

customer_id,payment_month,amount
1,JAN 24,500
1,FEB 24,550
2,MAR 24,450
2,APR 24,900
2,MAY 24,450
3,MAY 24,525
4,JUN 23,350
4,JUN 23,400

需求说明 #

需要为发票表新增payment_final_month字段，取值规则：按客户维度累计支付金额匹配发票金额，确定每笔发票对应的支付月份。
例如：

• 客户1首笔支付500，覆盖第1张发票400，剩余100覆盖第2张发票的部分金额，因此这两张发票的payment_final_month均为JAN 24；
• 第3张发票需使用FEB 24的支付金额，故取值FEB 24。

尝试的无效方法 #

i.invoice_amount - COALESCE(LAG(i.payment_amt, 1) OVER (PARTITION BY i.customer_id ORDER BY i.invoice_id), 0) AS remaining_amount

可行SQL实现方案 #

核心思路是先计算支付表的累计支付区间，再计算发票表的累计应收区间，最后通过区间匹配确定每笔发票对应的支付月份。

WITH customer_payments AS (
    -- 计算客户的累计支付金额，生成支付区间（start_amount, end_amount）
    SELECT 
        customer_id,
        payment_month,
        amount,
        SUM(amount) OVER (PARTITION BY customer_id ORDER BY STR_TO_DATE(payment_month, '%b %y')) - amount AS start_amount,
        SUM(amount) OVER (PARTITION BY customer_id ORDER BY STR_TO_DATE(payment_month, '%b %y')) AS end_amount
    FROM payment
),
invoice_due AS (
    -- 计算客户的累计发票金额，生成应收区间（start_due, end_due）
    SELECT 
        *,
        COALESCE(SUM(invoice_amount) OVER (PARTITION BY customer_id ORDER BY invoice_id) - invoice_amount, 0) AS start_due,
        SUM(invoice_amount) OVER (PARTITION BY customer_id ORDER BY invoice_id) AS end_due
    FROM invoice
)
-- 关联匹配应收区间和支付区间，确定最终支付月份
SELECT 
    i.*,
    cp.payment_month AS payment_final_month
FROM invoice_due i
LEFT JOIN customer_payments cp 
    ON i.customer_id = cp.customer_id
    -- 匹配条件：应收区间与支付区间有重叠
    AND i.start_due < cp.end_amount
    AND i.end_due > cp.start_amount
ORDER BY i.customer_id, i.invoice_id;

逻辑说明 #

1. customer_payments CTE：将支付表按客户分组，按支付月份排序（通过STR_TO_DATE转换为日期确保排序正确），计算每笔支付的累计起始金额和结束金额，形成支付金额区间。
2. invoice_due CTE：将发票表按客户分组，按发票ID排序，计算每笔发票的累计应收起始金额和结束金额，形成应收金额区间。
3. 关联匹配：通过客户ID关联，判断应收区间与支付区间是否重叠，重叠的支付月份即为该发票的payment_final_month。
4. 对于未匹配到支付的发票（如客户5的发票），payment_final_month将返回NULL，符合预期。

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