BigQuery中QTD数据转日值的SQL实现需求

需求说明 #

我们需要将每日接收的QTD（季度累计）数据转换为周期增量值（日/月维度均可）。value列无固定规律：可能因退货归零、无采购而数值不变，也可能出现缺失值。核心要求是禁止跨季度计算差值，每个季度的第一个周期值直接保留，后续周期仅与同季度内的前一个周期值计算增量。

原始数据示例 #

预期输出 #

跨季度场景示例（关键验证点） #

输入数据

预期输出

BigQuery实现方案 #

以下SQL通过窗口函数实现同季度内的增量计算，同时处理空值和跨季度问题：

WITH processed_data AS (
  SELECT
    SKU,
    value,
    Date,
    -- 提取年度和季度，用于分组隔离不同季度的数据
    EXTRACT(YEAR FROM Date) AS record_year,
    EXTRACT(QUARTER FROM Date) AS record_quarter,
    -- 仅在同SKU、同年度季度内，按日期排序取前一条的QTD值
    LAG(value) OVER (
      PARTITION BY SKU, record_year, record_quarter
      ORDER BY Date
    ) AS prev_qtd_value
  FROM
    `your-project.your-dataset.target-table` -- 替换为你的表路径
)
SELECT
  SKU,
  -- 计算增量：首条记录直接取原值（空值则为0），后续记录计算差值
  CASE
    WHEN prev_qtd_value IS NULL THEN COALESCE(value, 0)
    ELSE COALESCE(value, prev_qtd_value) - prev_qtd_value
  END AS value,
  Date
FROM
  processed_data
ORDER BY
  SKU, Date;

关键逻辑说明 #

1. 季度分组隔离：通过PARTITION BY SKU, record_year, record_quarter确保仅在同SKU、同年度同季度内计算前值，彻底避免跨季度差值。
2. 前值获取：LAG(value)窗口函数按日期排序，获取同组内上一条记录的QTD值。
3. 空值处理：COALESCE函数将空值替换为前一个周期的QTD值，最终增量为0，符合缺失值视为无变化的需求。
4. 增量计算：同季度第一条记录直接保留原始QTD值，后续记录用当前值减去前值得到周期增量。

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