BigQuery分区SUM函数内添加条件实现库存预测值不为负的方法

原有逻辑问题 #

你当前使用的普通滚动SUM窗口函数仅能实现全量历史值累加，无法满足「库存低于0时重置为0再继续后续计算」的需求：这类逐行状态依赖的计算需要动态修正每一步的计算基数，普通聚合窗口函数无法实现中途的数值重置。你之前尝试的CASE WHEN写法仅能修正最终输出结果，无法改变后续累加的计算基数，因此达不到预期效果。 #

推荐实现方案（全引擎兼容） #

使用递归CTE逐行迭代计算，每一步的库存都以上一步修正后的值（保底为0）为基数累加当日变动，示例代码如下：

WITH sorted_stock AS (
    SELECT 
        Productcode,
        date,
        stockToday,
        stockmutation,
        -- 按分区字段分组、日期升序排序，为每行分配连续行号
        ROW_NUMBER() OVER (PARTITION BY globalid ORDER BY date ASC) AS rn
    FROM 你的业务表名
),
recursive_stock_calc AS (
    -- 锚点成员：计算每个分区第一行的初始库存
    SELECT 
        globalid,
        Productcode,
        date,
        rn,
        GREATEST(stockToday + stockmutation, 0) AS StockFC
    FROM sorted_stock
    WHERE rn = 1
    UNION ALL
    -- 递归成员：逐行计算后续日期库存，自动保底为0
    SELECT 
        s.globalid,
        s.Productcode,
        s.date,
        s.rn,
        GREATEST(r.StockFC + s.stockmutation, 0) AS StockFC
    FROM recursive_stock_calc r
    INNER JOIN sorted_stock s
        ON r.globalid = s.globalid
        AND s.rn = r.rn + 1
)
SELECT Productcode, date, StockFC
FROM recursive_stock_calc
ORDER BY Productcode, date;

说明 #

• 代码中PARTITION BY globalid和你原有逻辑保持一致，若实际分区规则有变化可自行调整
• GREATEST(x, 0)是各SQL引擎通用的取最大值函数，等同于CASE WHEN判断：CASE WHEN x < 0 THEN 0 ELSE x END
• 若使用Spark SQL、Flink SQL等大数据引擎，也可以用内置的aggregate高阶函数实现，性能优于递归CTE，适合数据量较大的场景

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