Redshift条件累计求和：基于累计结果启停的SQL实现需求

嘿，我明白你要解决的问题了——就是要跟踪负数缺口的填补过程：只有当出现负数时才启动累计，用后面的正数一点点填补这个缺口，直到累计和转正就停止，之后的正数就正常显示，不用再参与之前的累计。你之前写的SQL用正负值分区，肯定搞不定这种跨正负的缺口填补场景，因为转正的那个正数其实是属于同一个累计周期的，不能被分到正数组里。

下面是专门针对Redshift的解决方案，用窗口函数一步步标记累计周期并计算正确的累计值：

WITH running_gap AS (
    -- 第一步：计算未填补的负数缺口（正数填补后剩余的缺口，最小为0）
    SELECT
        date,
        partner,
        invoice,
        GREATEST(
            0,
            LAG(running_gap, 1, 0) OVER (PARTITION BY partner ORDER BY date) - invoice
        ) AS running_gap,
        -- 标记新的累计周期起点：当前是负数，且之前没有未填补的缺口
        CASE
            WHEN LAG(running_gap, 1, 0) OVER (PARTITION BY partner ORDER BY date) = 0 
                 AND invoice < 0 THEN 1
            ELSE 0
        END AS new_gap_start
    FROM your_table_name -- 替换成你的实际表名
),
gap_groups AS (
    -- 第二步：给每个累计周期分配唯一组ID
    SELECT
        *,
        SUM(new_gap_start) OVER (PARTITION BY partner ORDER BY date) AS gap_group_id
    FROM running_gap
),
gap_groups_with_end AS (
    -- 第三步：标记每个累计周期的结束点（缺口被完全填补的行）
    SELECT
        *,
        CASE
            WHEN running_gap = 0 
                 AND LAG(running_gap, 1, 0) OVER (PARTITION BY partner ORDER BY date) > 0 THEN 1
            ELSE 0
        END AS gap_end
    FROM gap_groups
),
final_groups AS (
    -- 第四步：修正组ID，周期结束后重置为0，避免后续行误归到旧周期
    SELECT
        date,
        partner,
        invoice,
        CASE
            WHEN SUM(gap_end) OVER (PARTITION BY partner ORDER BY date) >= gap_group_id THEN 0
            ELSE gap_group_id
        END AS final_gap_group_id
    FROM gap_groups_with_end
),
group_cumulative AS (
    -- 第五步：在每个累计周期内计算从起点到当前行的累计和
    SELECT
        *,
        SUM(invoice) OVER (PARTITION BY partner, final_gap_group_id ORDER BY date) AS cumulative_invoice
    FROM final_groups
)
-- 最终结果：累计周期内显示缺口填补的累计值，周期外显示原发票值
SELECT
    date,
    partner,
    invoice,
    CASE
        WHEN final_gap_group_id > 0 THEN cumulative_invoice
        ELSE invoice
    END AS cumulative_invoice
FROM group_cumulative
ORDER BY partner, date;

逻辑说明： #

1. running_gap：计算每个时间点剩余的负数缺口（比如-250会产生250的缺口，后续+100后缺口变为150），同时标记新累计周期的起点（首次出现负数且之前无未填补缺口）。
2. gap_groups：通过累计求和，给每个新启动的累计周期分配唯一的组ID。
3. gap_groups_with_end：标记周期结束点——当剩余缺口从正数变为0时，说明缺口已被完全填补，该行就是当前周期的最后一行。
4. final_groups：修正组ID，确保周期结束后的行不再属于任何累计组（组ID重置为0）。
5. group_cumulative：在每个累计组内计算从周期起点到当前行的累计和。

这个逻辑完全匹配你的示例场景：

• 2022-06-01的-250启动第一个周期，累计到2022-09-01的+60时总和转正（-250+100+100+60=10），周期内的累计值正确显示。
• 2022-10-01的+200不在累计周期内，直接显示原值。
• 2022-11-01的-300启动新周期，累计到2023-01-01的+200时总和转正（-300+150+200=50），符合预期。

内容的提问来源于stack exchange，提问作者C. Vale