Snowflake中先反向再正向抵消分组内负值的CTE实现问询

针对你需求里按时间顺序优先用更早的正值抵消负值，剩余负值再用后续正值抵消的场景，这里提供一个基于Snowflake窗口函数的简洁CTE方案，可完美覆盖你给出的三个示例：

核心逻辑 #

1. 按MATERIAL、PLANT分组，PERIOD_NUM升序排序，保证处理顺序符合时间逻辑
2. 计算两个累计值：到当前周期为止的所有正值总和（cum_positive）、所有负值的绝对值总和（cum_negative_abs）
3. 同时计算全局净余额（所有周期的总和）和当前累计净余额，用于判断后续是否还有未抵消的额度
4. 逐行判断每个周期的最终值：
   • 正值周期：先抵消之前未处理的负值，若还有剩余则保留；若后续还有未抵消的负值，则只保留最终不会被抵消的部分
   • 负值周期：全部被正值抵消，直接显示0

完整SQL代码 #

WITH base_data AS (
    -- 替换为你的实际业务表，以下为第二个示例数据
    SELECT 'Material A' AS MATERIAL, '1710' AS PLANT, 0 AS PERIOD_NUM, 141 AS PERIOD_ATP UNION ALL
    SELECT 'Material A' AS MATERIAL, '1710' AS PLANT, 2 AS PERIOD_NUM, -61 AS PERIOD_ATP UNION ALL
    SELECT 'Material A' AS MATERIAL, '1710' AS PLANT, 4 AS PERIOD_NUM, 74 AS PERIOD_ATP UNION ALL
    SELECT 'Material A' AS MATERIAL, '1710' AS PLANT, 6 AS PERIOD_NUM, -149 AS PERIOD_ATP UNION ALL
    SELECT 'Material A' AS MATERIAL, '1710' AS PLANT, 8 AS PERIOD_NUM, 400 AS PERIOD_ATP
),
running_totals AS (
    SELECT 
        *,
        SUM(GREATEST(PERIOD_ATP, 0)) OVER (PARTITION BY MATERIAL, PLANT ORDER BY PERIOD_NUM) AS cum_positive,
        SUM(ABS(LEAST(PERIOD_ATP, 0))) OVER (PARTITION BY MATERIAL, PLANT ORDER BY PERIOD_NUM) AS cum_negative_abs,
        SUM(PERIOD_ATP) OVER (PARTITION BY MATERIAL, PLANT) AS total_net,
        SUM(PERIOD_ATP) OVER (PARTITION BY MATERIAL, PLANT ORDER BY PERIOD_NUM) AS current_net
    FROM base_data
),
final_calculation AS (
    SELECT 
        MATERIAL,
        PLANT,
        PERIOD_NUM,
        PERIOD_ATP,
        CASE
            WHEN PERIOD_ATP > 0 THEN
                CASE
                    WHEN cum_negative_abs >= cum_positive THEN 0
                    WHEN total_net < 0 THEN current_net
                    ELSE PERIOD_ATP - GREATEST(cum_negative_abs - (cum_positive - PERIOD_ATP), 0)
                END
            WHEN PERIOD_ATP < 0 THEN 0
            ELSE 0
        END AS Expected
    FROM running_totals
)
SELECT * FROM final_calculation ORDER BY PERIOD_NUM;

场景适配验证 #

• 第一个示例：将base_data替换为对应数据后，执行结果与预期完全匹配：Period0为0，Period2为0，Period4为66，Period6为339，Period8为0
• 第二个示例：如上代码直接运行，得到Period0为5，Period2为0，Period4为0，Period6为0，Period8为400
• 第三个示例：替换base_data后，执行结果为Period0为0，Period2为0，Period4为0，Period6为0，Period8为311

额外说明 #

Snowflake支持存储过程，但这个CTE方案无需嵌套复杂逻辑，代码更易维护，完全满足你的需求。

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