求简化Oracle中最后有效事务索引（LAST_IDX）的计算SQL方案

你遇到的核心问题是：常规的累计求和方案无法正确处理-事务的「抵消最近一次+」逻辑——-不是简单的数值减1，而是回退到最近一次+执行前的状态，后续的+需要基于当前回退后的状态继续递增（从你的样例结果来看，SEQ=8的+直接跳到3，说明+的计数是全局累计的，-仅临时回退状态不影响后续+的递增）。

递归CTE可以逐行处理每个事务，维护当前索引状态和撤销历史，完美匹配你的事务规则，代码逻辑清晰且易维护：

WITH ordered_trans AS (
    -- 先给每个ID的事务按SEQ排序，生成行号
    SELECT 
        id, 
        seq, 
        code,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq) AS row_num
    FROM tab
),
transaction_state AS (
    -- 锚点成员：处理每个ID的第一条事务
    SELECT 
        id, 
        seq, 
        code,
        CASE code
            WHEN '+' THEN 1
            WHEN '=' THEN NULL
            ELSE NULL -- 符合一致性规则的话，不会出现无前置+的-
        END AS last_idx,
        -- 用字符串维护撤销栈，存储每次+操作前的索引值（NULL用'NULL'表示）
        CASE code WHEN '+' THEN 'NULL' ELSE '' END AS undo_history
    FROM ordered_trans
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归成员：逐行处理后续事务，更新状态和撤销栈
    SELECT 
        ot.id, 
        ot.seq, 
        ot.code,
        CASE
            WHEN ot.code = '+' THEN ts.last_idx + 1
            WHEN ot.code = '=' THEN ts.last_idx
            WHEN ot.code = '-' THEN
                -- 从撤销栈取出最后一个值，转换为对应索引（处理NULL）
                CASE SUBSTR(ts.undo_history, INSTR(ts.undo_history, ',', -1) + 1)
                    WHEN 'NULL' THEN NULL
                    ELSE TO_NUMBER(SUBSTR(ts.undo_history, INSTR(ts.undo_history, ',', -1) + 1))
                END
        END AS last_idx,
        CASE
            WHEN ot.code = '+' THEN
                -- 当前+操作前的索引加入撤销栈
                ts.undo_history || ',' || COALESCE(TO_CHAR(ts.last_idx), 'NULL')
            WHEN ot.code = '-' THEN
                -- 移除撤销栈的最后一个记录（抵消最近一次+）
                SUBSTR(ts.undo_history, 1, INSTR(ts.undo_history, ',', -1) - 1)
            ELSE
                -- =操作不改变撤销栈
                ts.undo_history
        END AS undo_history
    FROM transaction_state ts
    JOIN ordered_trans ot 
        ON ts.id = ot.id AND ts.row_num + 1 = ot.row_num
)
-- 输出结果，按ID和SEQ排序
SELECT id, seq, code, last_idx
FROM transaction_state
ORDER BY id, seq;

方案优势 #

1. 逻辑直观：完全贴合你的事务规则，每一步操作都对应明确的状态更新，比嵌套窗口函数更易读和调试。
2. 扩展性强：如果后续规则调整（比如允许嵌套抵消），仅需修改撤销栈的处理逻辑即可。
3. 规则兼容性：若数据存在违反一致性规则的-（如无前置+或连续-），可在递归分支中添加校验逻辑抛出错误。

如果你更倾向于用窗口函数而非递归CTE，可以通过标记每个+和对应-的关联关系，计算有效+的贡献：

WITH trans_with_markers AS (
    SELECT 
        id,
        seq,
        code,
        -- 给每个+分配唯一编号
        CASE WHEN code = '+' THEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq) END AS plus_id,
        -- 找到每个-对应的最近未被抵消的+编号
        CASE WHEN code = '-' THEN 
            MAX(CASE WHEN code = '+' THEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY seq) END) 
            OVER (PARTITION BY id ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
        END AS minus_plus_id
    FROM tab
),
plus_cancel_status AS (
    SELECT 
        id,
        plus_id,
        -- 标记每个+是否被抵消
        COUNT(CASE WHEN minus_plus_id = plus_id THEN 1 END) OVER (PARTITION BY id) AS is_canceled
    FROM trans_with_markers
)
SELECT 
    t.id,
    t.seq,
    t.code,
    -- 计算当前未被抵消的+的数量，即为last_idx
    (SELECT COUNT(*) FROM plus_cancel_status p WHERE p.id = t.id AND p.plus_id <= t.plus_id AND p.is_canceled = 0) AS last_idx
FROM trans_with_markers t
ORDER BY id, seq;

注意：该方案在数据量较大时，性能可能不如递归CTE。

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