如何实现触发硬限制时保留上一行累计值的Running Total计算

需求说明 #

需要实现的计算逻辑：计算销售额累计值时，若累计值超过指定的Hard Limit（硬限制值），则返回上一行的累计结果不再累加；后续所有行只要累计突破硬限制，均遵循此规则。对应Excel公式为 I9=IF(I8+F9 > $H$2, I8, I8+F9)。

字段定义：

• SaleAmount：单条记录的销售额
• HardLimit：硬限制阈值（可为固定值或每行独立值）
• 期望输出：符合规则的累计结果

解决方案 #

由于该逻辑依赖前一行的计算结果（而非原始销售额的单纯累计），需使用**递归CTE（Common Table Expression）**逐行处理数据。前提是数据必须有明确的排序依据（如交易日期、自增ID等），保证处理顺序与Excel一致。

固定硬限制场景示例 #

假设你的表名为sales_data，包含排序用的唯一标识id和销售额SaleAmount，硬限制固定为5000000，实现代码如下：

WITH recursive sales_ordered AS (
    -- 给数据添加行号，确保处理顺序正确
    SELECT 
        SaleAmount,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM sales_data
),
running_total AS (
    -- 初始化第一行的累计值
    SELECT 
        row_num,
        SaleAmount,
        CASE 
            WHEN SaleAmount > 5000000 THEN SaleAmount  -- 若第一行就超限制，可根据需求调整规则
            ELSE SaleAmount 
        END AS desired_total
    FROM sales_ordered
    WHERE row_num = 1
    
    UNION ALL
    
    -- 递归处理后续行
    SELECT 
        s.row_num,
        s.SaleAmount,
        CASE 
            WHEN r.desired_total + s.SaleAmount > 5000000 
            THEN r.desired_total  -- 超过硬限制，沿用前一行累计值
            ELSE r.desired_total + s.SaleAmount  -- 未超过，继续累加
        END AS desired_total
    FROM sales_ordered s
    JOIN running_total r ON s.row_num = r.row_num + 1
)
SELECT 
    SaleAmount,
    desired_total AS 期望输出结果
FROM running_total
ORDER BY row_num;

动态硬限制场景适配 #

若HardLimit是每条记录的独立字段（表中包含HardLimit列），只需修改递归中的判断条件，将固定值替换为字段名：

CASE 
    WHEN r.desired_total + s.SaleAmount > s.HardLimit 
    THEN r.desired_total
    ELSE r.desired_total + s.SaleAmount
END AS desired_total

代码说明 #

1. sales_ordered CTE：为数据添加行号，确保处理顺序与业务逻辑一致，可根据实际需求替换排序字段（如transaction_date）。
2. 递归初始化：处理第一行数据，初始化累计值，可根据业务需求调整第一行超限制时的返回规则。
3. 递归迭代：逐行计算累计值，每次基于前一行的结果判断是否继续累加，严格遵循硬限制规则。

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