SQL实现超额付款向下结转至后续月份（Amazon Redshift场景）

在Amazon Redshift（DataGrip）中，有如下业务表结构及数据：

原始业务表 #

计算需求 #

需要每月计算实际达成金额（Achieved），规则如下：

• Achieved不能超过当月Target
• 若当月Amount_Collected超出Target，超额部分结转至后续月份抵扣目标
• 超额耗尽后，未达成目标的月份无需追溯，直接记Achieved为0

最终需要得到包含Achieved和Overpayment（结转的超额金额）的结果表：

目标结果表 #

注意事项 #

普通的SUM() OVER(ORDER BY MONTH ROWS UNBOUNDED PRECEDING)无法实现，因为需要仅向前结转超额，不追溯未达成目标的月份，需通过滚动计算Overpayment来实现。

在Redshift中，可以使用递归CTE来处理这种带状态的滚动计算，因为需要保留每个月的超额结转值。以下是具体SQL：

WITH ranked_data AS (
    -- 给每个合同的记录按到期日排序，生成行号
    SELECT
        Contract_ID,
        Starting_Month,
        Contract_Duration_In_Months,
        Collection_Due_Date,
        Target,
        Amount_Collected,
        ROW_NUMBER() OVER (PARTITION BY Contract_ID ORDER BY Collection_Due_Date) AS rn
    FROM your_table_name -- 替换为实际表名
),
recursive_calculation AS (
    -- 递归起始：处理每个合同的第一条记录
    SELECT
        Contract_ID,
        Starting_Month,
        Contract_Duration_In_Months,
        Collection_Due_Date,
        Target,
        Amount_Collected,
        LEAST(Target, Amount_Collected) AS Achieved,
        GREATEST(Amount_Collected - Target, 0) AS Overpayment,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归迭代：处理后续每条记录
    SELECT
        rd.Contract_ID,
        rd.Starting_Month,
        rd.Contract_Duration_In_Months,
        rd.Collection_Due_Date,
        rd.Target,
        rd.Amount_Collected,
        -- 优先用之前的超额抵扣，再用当月收款，不超过目标
        CASE
            WHEN rc.Overpayment >= rd.Target THEN rd.Target
            ELSE LEAST(rc.Overpayment + rd.Amount_Collected, rd.Target)
        END AS Achieved,
        -- 计算剩余结转的超额金额
        CASE
            WHEN rc.Overpayment >= rd.Target THEN rc.Overpayment - rd.Target
            ELSE GREATEST(rc.Overpayment + rd.Amount_Collected - rd.Target, 0)
        END AS Overpayment,
        rd.rn
    FROM ranked_data rd
    JOIN recursive_calculation rc ON rd.Contract_ID = rc.Contract_ID AND rd.rn = rc.rn + 1
)
-- 输出最终结果
SELECT
    Contract_ID,
    Starting_Month,
    Contract_Duration_In_Months,
    Collection_Due_Date,
    Target,
    Amount_Collected,
    Achieved,
    Overpayment
FROM recursive_calculation
ORDER BY Contract_ID, Collection_Due_Date;

逻辑说明 #

1. ranked_data CTE：给每个合同下的记录按到期日排序生成行号，确保递归按时间顺序处理。
2. recursive_calculation CTE：
   • 起始部分处理每个合同的第一条记录，直接计算初始的达成金额和超额结转值。
   • 迭代部分关联上一条记录的超额结转值，计算当月达成金额：如果上期超额足够覆盖当月目标，就用超额抵扣；否则用上期超额加当月收款，不超过目标。同时更新剩余的超额结转值，不足则记0。
3. 最后按合同和到期日排序输出结果，得到目标表。

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