如何在SQL中避免负收入值并将其分摊至后续月份？

要实现将负收入置为0，并使用后续正收入抵消负金额的需求，可以通过递归CTE按时间顺序逐步计算调整后的收入和待抵消余额，逻辑清晰且能准确匹配预期结果。

步骤说明 #

1. 排序数据：先将收入数据按日期升序排列，确保从最早到最晚的顺序处理，保证负金额用后续的正收入抵消。
2. 递归计算：
   • 锚点成员处理最早的日期，将负收入转为0并记录待抵消金额；正收入直接保留，待抵消金额为0。
   • 递归成员依次处理后续日期，根据上一步的待抵消余额，用当前正收入抵消余额，计算调整后的收入和更新后的待抵消余额；若当前为负收入则直接置为0并新增待抵消金额。

完整SQL代码 #

WITH revenue_table AS (
    SELECT '2018-09-01' AS date, 1200 AS revenue UNION ALL
    SELECT '2018-08-01' AS date, 400 AS revenue UNION ALL
    SELECT '2018-07-01' AS date, -1000 AS revenue UNION ALL
    SELECT '2018-06-01' AS date, 800 AS revenue UNION ALL
    SELECT '2018-05-01' AS date, 600 AS revenue UNION ALL
    SELECT '2018-04-01' AS date, 200 AS revenue UNION ALL
    SELECT '2018-03-01' AS date, -200 AS revenue UNION ALL
    SELECT '2018-02-01' AS date, 400 AS revenue UNION ALL
    SELECT '2018-01-01' AS date, 200 AS revenue
),
ordered_data AS (
    SELECT 
        date,
        revenue,
        ROW_NUMBER() OVER (ORDER BY date ASC) AS rn
    FROM revenue_table
),
recursive_adjust AS (
    -- 处理最早的日期（锚点）
    SELECT 
        date,
        CASE WHEN revenue < 0 THEN 0 ELSE revenue END AS adjusted_revenue,
        CASE WHEN revenue < 0 THEN ABS(revenue) ELSE 0 END AS pending_offset
    FROM ordered_data
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归处理后续日期
    SELECT 
        od.date,
        CASE
            WHEN ra.pending_offset > 0 THEN
                CASE
                    WHEN od.revenue <= ra.pending_offset THEN 0
                    ELSE od.revenue - ra.pending_offset
                END
            ELSE
                CASE WHEN od.revenue < 0 THEN 0 ELSE od.revenue END
        END AS adjusted_revenue,
        CASE
            WHEN ra.pending_offset > 0 THEN
                CASE
                    WHEN od.revenue <= ra.pending_offset THEN ra.pending_offset - od.revenue
                    ELSE 0
                END
            ELSE
                CASE WHEN od.revenue < 0 THEN ABS(od.revenue) ELSE 0 END
        END AS pending_offset
    FROM recursive_adjust ra
    JOIN ordered_data od ON od.rn = ra.rn + 1
)
SELECT 
    date,
    adjusted_revenue AS 收入
FROM recursive_adjust
ORDER BY date DESC; -- 按原始示例的日期降序输出

输出结果 #

运行上述代码后，将得到与预期一致的结果：

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