BigQuery：临时场景下的库存余额计算及余额列新增需求

核心逻辑 #

库存余额是每日库存变动的累计值，需先计算每日净库存变化，再按日期排序做累计求和，若有初始库存需额外叠加。

具体步骤 #

• 第一步：计算每日净库存变动
  将入库、出库转换为统一变动值：入库记为正，出库记为负，公式示例：daily_change = quantity_in - quantity_out（字段名需根据实际表结构调整）。
• 第二步：用窗口函数计算累计余额
  借助BigQuery的SUM() OVER()窗口函数，按交易日期升序排序，累计每日净变动量。若有期初库存，直接在累计结果上加上该初始值即可。

示例SQL代码 #

假设表名为inventory_transactions，字段包含transaction_date（交易日期）、quantity_in（入库数量）、quantity_out（出库数量），SQL示例如下：

SELECT
  transaction_date,
  quantity_in,
  quantity_out,
  -- 当日净库存变动
  quantity_in - quantity_out AS daily_change,
  -- 累计库存余额（默认初始库存为0，有期初值则改为 + 期初值）
  SUM(quantity_in - quantity_out) OVER (ORDER BY transaction_date ASC) AS inventory_balance
FROM
  `your-project.your-dataset.inventory_transactions`
ORDER BY
  transaction_date;

扩展场景处理 #

如果需要按商品维度分组计算库存（多商品库存管理），只需在窗口函数中增加分区条件，示例：

SUM(quantity_in - quantity_out) OVER (PARTITION BY product_id ORDER BY transaction_date ASC) AS inventory_balance

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