BigQuery实现每日库存扣减需求叠加新增库存的滚动计算

实现逻辑 #

该需求是逐行依赖上一行计算结果的递归计算场景，普通累加窗口函数无法直接实现「结果为负强制取0」的规则，因此采用递归CTE实现，步骤如下：

1. 对每个sku的所有记录按日期升序排序，生成行号标记计算顺序
2. 先计算每个sku首行（最早日期）的solution值
3. 逐行迭代计算后续每一行的solution，每次计算后做负数截断处理

完整SQL代码 #

WITH RECURSIVE base_data AS (
  -- 基础数据排序：给每个sku下的记录按日期升序打行号
  SELECT 
    date,
    sku,
    inventory_added,
    demand,
    ROW_NUMBER() OVER(PARTITION BY sku ORDER BY date ASC) AS row_num
  FROM 你的表名 -- 替换为实际表名
),
calculate_solution AS (
  -- 锚点：计算每个sku首行的solution
  SELECT 
    date,
    sku,
    inventory_added,
    demand,
    row_num,
    GREATEST(inventory_added - demand, 0) AS solution
  FROM base_data
  WHERE row_num = 1

  UNION ALL

  -- 递归部分：逐行计算后续日期的solution
  SELECT 
    b.date,
    b.sku,
    b.inventory_added,
    b.demand,
    b.row_num,
    GREATEST(c.solution + b.inventory_added - b.demand, 0) AS solution
  FROM calculate_solution c
  JOIN base_data b 
    ON c.sku = b.sku 
    AND b.row_num = c.row_num + 1
)
-- 输出最终结果，按sku和日期排序
SELECT date, sku, inventory_added, demand, solution
FROM calculate_solution
ORDER BY sku, date ASC;

代码说明 #

• 用GREATEST(计算结果, 0)直接实现负数取0的逻辑，无需额外写IF判断
• PARTITION BY sku保证不同sku的计算完全独立，不会互相干扰
• 递归逻辑只在同sku下逐行迭代，性能有保障，单sku维度下的日数据量不超过10万行时可稳定运行

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