SQL实现按日期（年月）的债务比例分配问题求助

问题背景 #

核心需求是按D_Date（年、月）对债务进行支付分配：

• 支付金额优先扣减最早月份的债务，仅对该月内的债务按金额占比比例扣减，剩余月份债务保持不变
• 若支付金额足够覆盖多笔甚至全部债务，则按月份先后顺序依次完成扣减

需要实现三个具体场景：

• 当@payment=280时，完全覆盖第一个月所有债务
• 当@payment=430时，覆盖第一个月后，对第二个月债务按比例扣减
• 当@payment=940时，覆盖全部债务

核心计算逻辑：A_Rest_new = 原A_Rest - (原A_Rest/当月总债务)*分配到该月的支付金额

现有代码问题 #

原代码直接对所有月份应用支付金额扣减，没有按月份优先级处理，导致所有月份的债务都被扣除，不符合"先扣最早月份"的要求：

declare @payment money = 200.00;
declare @F_Subscr int = 1;
SELECT DISTINCT 
     d_date
    ,N_Amount
    ,N_Amount_Rest - ((N_Amount_Rest / SUM(N_Amount_Rest) OVER (PARTITION BY d_date) ) * @payment ) as N_Amount_Rest
    
FROM 

(
SELECT 
     FORMAT(D_Date, 'yyyyMM') d_date
     ,N_Amount
     ,N_Amount_Rest
FROM
    dbo.FD_Bills
WHERE
    F_Subscr = @F_Subscr

) tt

修改后的实现代码 #

通过计算每个月份的累计债务总和，确定支付金额的分配范围，再按比例计算每笔债务的剩余金额：

declare @payment money = 200.00;
declare @F_Subscr int = 1;

-- 预处理：按月份分组计算当月总债务，给月份按时间排序
WITH MonthlyDebts AS (
    SELECT 
        FORMAT(D_Date, 'yyyyMM') AS d_date,
        N_Amount,
        N_Amount_Rest,
        SUM(N_Amount_Rest) OVER (PARTITION BY FORMAT(D_Date, 'yyyyMM')) AS MonthlyTotal,
        ROW_NUMBER() OVER (ORDER BY MIN(D_Date)) AS MonthOrder
    FROM dbo.FD_Bills
    WHERE F_Subscr = @F_Subscr
),
-- 计算从最早月份到当前月的累计债务总和，判断支付覆盖范围
CumulativeDebts AS (
    SELECT 
        *,
        SUM(MonthlyTotal) OVER (ORDER BY MonthOrder ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeTotal
    FROM MonthlyDebts
),
-- 确定每个月份可分配的支付金额
PaymentAllocation AS (
    SELECT 
        *,
        CASE
            -- 累计债务≤支付金额，该月全额扣除
            WHEN CumulativeTotal <= @payment THEN MonthlyTotal
            -- 支付金额覆盖到当前月的部分债务，计算需扣除的金额
            WHEN CumulativeTotal - MonthlyTotal < @payment THEN @payment - (CumulativeTotal - MonthlyTotal)
            -- 未被支付覆盖的月份，分配金额为0
            ELSE 0
        END AS AllocatedPayment
    FROM CumulativeDebts
)
-- 计算每笔债务的剩余金额
SELECT 
    d_date,
    N_Amount,
    CASE
        WHEN AllocatedPayment = 0 THEN N_Amount_Rest
        ELSE N_Amount_Rest - (N_Amount_Rest / MonthlyTotal) * AllocatedPayment
    END AS N_Amount_Rest
FROM PaymentAllocation
ORDER BY MonthOrder;

逻辑说明 #

1. MonthlyDebts：按月份分组统计当月总债务，同时给每个月份按时间先后分配序号，确保从最早月份开始处理
2. CumulativeDebts：计算累计债务总和，用来判断支付金额能覆盖到第几个月份
3. PaymentAllocation：根据累计债务和支付金额的关系，确定每个月份的扣减金额：
   • 已完全覆盖的月份，全额扣减
   • 部分覆盖的月份，扣减支付金额剩余的部分
   • 未覆盖的月份，不扣减
4. 最后根据分配的扣减金额，按比例计算每笔债务的剩余金额，未覆盖的月份保持原剩余债务不变

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