BigQuery如何计算库存减需求滚动和直至差值为正后重新开始滚动求和

该需求属于带状态传递的逐行库存扣减场景，原生窗口函数无法直接实现带重置条件的滚动求和，可通过递归CTE实现对应逻辑，代码如下：

-- 按需调整递归深度，默认限制为1000，单个SKU记录数超过1000时打开下方配置
-- SET max_recursion_depth = 10000;

WITH sorted_data AS (
  -- 按SKU分组、日期升序排序，为每个SKU的记录生成连续行号
  SELECT 
    SKU,
    Date,
    Total_Inventory,
    demand,
    ROW_NUMBER() OVER (PARTITION BY SKU ORDER BY Date) AS rn
  FROM `你的项目.你的数据集.你的表名`
),
recursive_calculate AS (
  -- 锚点：计算每个SKU第一条记录的期末库存
  SELECT 
    SKU,
    Date,
    Total_Inventory,
    demand,
    rn,
    GREATEST(Total_Inventory - demand, 0) AS final_inventory
  FROM sorted_data
  WHERE rn = 1

  UNION ALL

  -- 递归：逐行继承上一期期末库存，结合当期入库计算新的期末库存
  SELECT 
    s.SKU,
    s.Date,
    s.Total_Inventory,
    s.demand,
    s.rn,
    GREATEST(r.final_inventory + s.Total_Inventory - s.demand, 0) AS final_inventory
  FROM recursive_calculate r
  JOIN sorted_data s 
    ON r.SKU = s.SKU 
    AND s.rn = r.rn + 1
)
SELECT SKU, Date, Total_Inventory, demand, final_inventory
FROM recursive_calculate
ORDER BY SKU, Date

逻辑说明 #

1. 先对每个SKU的记录按日期排序生成行号，保证计算顺序和业务发生顺序一致
2. 递归过程中每一行的可用库存 = 上一行剩余的final_inventory + 当日新入库的Total_Inventory
3. 用当日可用库存减去当日demand，结果取与0的最大值，自动实现库存扣到0就不再产生负数的要求
4. 新入库的库存会自动叠加到剩余库存中继续参与后续扣减，不需要额外生成分区字段，也不会出现跨批次继承失败的问题

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