BigQuery如何实现带非负规则的动态库存累计计算

常规的无界窗口累计求和是静态计算，不会根据上一行的库存结果动态调整累计起点——一旦累计值跌成负数，后续所有计算都会带着负偏移累加，完全无法满足“库存为负则重置为0，后续变动从0开始累计”的动态规则。
另外需要特别提示：当前示例代码里的窗口函数没有指定ORDER BY子句，实际执行时行的返回顺序是不可控的，正式生产使用时必须给每笔库存变动绑定可排序的唯一标识（比如变动时间戳、自增序号），保证累计的先后逻辑正确。

方案1：高性能差值窗口法（优先推荐，适配大数据量场景） #

这个方法不需要逐行递归，仅通过两次窗口计算就能动态重置累计值，BigQuery的窗口计算是分布式执行的，千万级数据量下的性能远高于逐行递归的CTE写法，非常适合生产环境。
逻辑核心是自动定位累计过程中出现过的最低原始累计值，把累计起点从“数据集第一行”平移到“让库存跌为0的断点位置”。
计算步骤拆解：

• 给所有变动值按时间顺序打序号
• 计算每一行的原始无重置累计值（就是之前用普通窗口算出的不带重置规则的累计值）
• 逐行计算「从第一行到当前行，所有原始累计值里的最小值」
• 实际库存值 = max( 当前行原始累计值 - 该位置之前的原始累计最小值, 0 )
  对应的可直接运行的BigQuery代码：

with base_stock as (
  -- 给变动值按顺序打序号，正式使用时替换为实际的时间排序逻辑
  select 
    variation,
    row_number() over() as sort_id
  from (
    SELECT -7.34 variation UNION ALL
    SELECT -1.81 UNION ALL
    SELECT -0.51 UNION ALL
    SELECT 17.19 UNION ALL
    SELECT -1.63 UNION ALL
    SELECT 2.82 UNION ALL
    SELECT -1.00 UNION ALL
    SELECT 0.56 UNION ALL
    SELECT -17.92 UNION ALL
    SELECT 0.34 UNION ALL
    SELECT 1.02 UNION ALL
    SELECT 0.39 UNION ALL
    SELECT 0.25 UNION ALL
    SELECT 0.65 UNION ALL
    SELECT 0.09 UNION ALL
    SELECT -0.32 UNION ALL
    SELECT -0.40 
  )
),
cum_calc as (
  select
    sort_id,
    variation,
    -- 原始无重置累计值
    sum(variation) over(order by sort_id rows unbounded preceding) as raw_cum
  from base_stock
)
select
  sort_id,
  variation,
  raw_cum,
  -- 逐行取之前所有原始累计的最小值
  min(raw_cum) over(order by sort_id rows unbounded preceding) as min_cum,
  -- 计算最终带重置规则的实际库存
  max(raw_cum - min(raw_cum) over(order by sort_id rows unbounded preceding), 0) as real_stock
from cum_calc

方案2：递归CTE逐行计算（逻辑最直观，适合小数据量场景） #

递归的计算逻辑完全贴合业务规则，每一步只依赖上一行的计算结果，可读性更强：

• 锚点：第一行的库存值 = max(第一行变动值, 0)
• 递归步：当前行库存值 = max(上一行已计算的库存值 + 当前行变动值, 0)
  对应的可运行代码：

with recursive base_stock as (
  select 
    variation,
    row_number() over() as sort_id
  from (
    SELECT -7.34 variation UNION ALL
    SELECT -1.81 UNION ALL
    SELECT -0.51 UNION ALL
    SELECT 17.19 UNION ALL
    SELECT -1.63 UNION ALL
    SELECT 2.82 UNION ALL
    SELECT -1.00 UNION ALL
    SELECT 0.56 UNION ALL
    SELECT -17.92 UNION ALL
    SELECT 0.34 UNION ALL
    SELECT 1.02 UNION ALL
    SELECT 0.39 UNION ALL
    SELECT 0.25 UNION ALL
    SELECT 0.65 UNION ALL
    SELECT 0.09 UNION ALL
    SELECT -0.32 UNION ALL
    SELECT -0.40 
  )
),
stock_calc as (
  -- 递归锚点：第一行的库存
  select
    sort_id,
    variation,
    max(variation, 0) as real_stock
  from base_stock
  where sort_id = 1

  union all

  -- 递归步：逐行累计
  select
    b.sort_id,
    b.variation,
    max(s.real_stock + b.variation, 0) as real_stock
  from stock_calc s
  join base_stock b on s.sort_id = b.sort_id - 1
)
select * from stock_calc

两种方案的计算结果完全一致，核心节点的库存值完全符合业务规则：

• 前3行累计出现负变动，库存全部置0
• 第4行入账17.19时，库存从0开始累计为17.19
• 累计到第9行的-17.92变动时，基于上一行17.94的库存值计算后剩0.02，不会出现之前用普通窗口算出的-9.64的错误结果
  内容的提问来源于stack exchange，提问作者cleobatista