基于前一行值的条件累计求和问题 - Postgres Redshift

Redshift中普通窗口函数无法处理这种依赖上一行计算结果的逻辑，必须用递归CTE来实现。以下是完整的可执行代码：

WITH ranked_transactions AS (
    -- 给每个用户的交易按顺序生成行号（替换transaction_date为实际排序字段，比如交易ID）
    SELECT
        userid,
        transaction_type,
        amount,
        transaction_date,
        ROW_NUMBER() OVER (PARTITION BY userid ORDER BY transaction_date) AS rn
    FROM your_table_name -- 替换为你的表名
),
recursive_cumulative AS (
    -- 锚点：初始化每个用户的第一行累计值
    SELECT
        userid,
        transaction_type,
        amount,
        transaction_date,
        rn,
        CASE
            WHEN transaction_type = 'Sell' THEN amount
            ELSE 0 -- 第一行无前置累计，Buy或其他类型初始为0
        END AS cumulative_sum_sell
    FROM ranked_transactions
    WHERE rn = 1

    UNION ALL

    -- 递归计算：逐行依赖上一行的累计结果
    SELECT
        rt.userid,
        rt.transaction_type,
        rt.amount,
        rt.transaction_date,
        rt.rn,
        CASE
            WHEN rt.transaction_type = 'Sell' THEN rc.cumulative_sum_sell + rt.amount
            WHEN rt.transaction_type = 'Buy' THEN
                CASE
                    WHEN rc.cumulative_sum_sell >= rt.amount THEN rc.cumulative_sum_sell - rt.amount
                    ELSE rc.cumulative_sum_sell -- 不满足条件则保持原值
                END
            ELSE rc.cumulative_sum_sell -- 其他交易类型不改变累计值
        END AS cumulative_sum_sell
    FROM ranked_transactions rt
    JOIN recursive_cumulative rc
        ON rt.userid = rc.userid
        AND rt.rn = rc.rn + 1
)
-- 输出最终结果
SELECT
    userid,
    transaction_type,
    amount,
    transaction_date,
    cumulative_sum_sell
FROM recursive_cumulative
ORDER BY userid, rn;

关键逻辑说明 #

1. ranked_transactions：给每个用户的交易按时间（或业务顺序）生成行号，确保递归时严格按交易顺序处理。
2. 递归锚点：处理每个用户的第一笔交易，初始化累计值——Sell直接取amount，其他类型初始为0（可根据业务调整）。
3. 递归计算：通过rt.rn = rc.rn + 1关联上一行的累计结果，按规则更新当前行的累计值：
   • Sell：累计值 += 当前amount
   • Buy：仅当累计值≥当前amount时，累计值 -= 当前amount
   • 其他类型：完全继承上一行的累计值

注意事项 #

• 确保排序字段（如transaction_date）能唯一确定用户交易的顺序，避免递归逻辑出错。
• 若数据量极大，递归CTE可能存在性能瓶颈，可考虑优化排序字段的索引或分批处理。

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