Big Query新增百分比字段：基于周期金额计算环比

要实现你需要的百分比字段，核心是用窗口函数来获取相邻周期的Amount值，WHILE和CASE并不适用于这种行与行之间的关联计算——WHILE是脚本层面的循环控制，CASE是条件分支判断，都无法直接获取其他行的数值。

实现步骤 #

1. 确定周期排序字段：必须有一个能定义周期顺序的字段（比如period_id、period_date），否则无法区分“上一周期”和“下一周期”。
2. 使用窗口函数获取相邻值：用LAG()或LEAD()函数获取目标周期的Amount，再代入公式计算。

示例SQL #

假设你的表有period_id（数值越大，周期越晚）、Amount及其他字段，以下是匹配你示例逻辑的写法：

SELECT
  *,
  -- 计算百分比，SAFE_DIVIDE避免除数为0时报错
  SAFE_DIVIDE(Amount - LAG(Amount) OVER (ORDER BY period_id ASC), LAG(Amount) OVER (ORDER BY period_id ASC)) AS period_percentage,
  -- 可选：转为百分比格式（保留2位小数）
  FORMAT_PERCENT(SAFE_DIVIDE(Amount - LAG(Amount) OVER (ORDER BY period_id ASC), LAG(Amount) OVER (ORDER BY period_id ASC)), 2) AS period_percentage_formatted
FROM
  `你的项目ID.你的数据集ID.Data1`

如果你的“上一周期”指更早的周期、“下一周期”指更晚的周期，可调整为：

SELECT
  *,
  SAFE_DIVIDE(LAG(Amount) OVER (ORDER BY period_id ASC) - LEAD(Amount) OVER (ORDER BY period_id ASC), LEAD(Amount) OVER (ORDER BY period_id ASC)) AS period_percentage
FROM
  `你的项目ID.你的数据集ID.Data1`

关键说明 #

• LAG(Amount) OVER (ORDER BY period_id ASC)：获取当前行的上一行（更早周期）的Amount值
• LEAD(Amount) OVER (ORDER BY period_id ASC)：获取当前行的下一行（更晚周期）的Amount值
• SAFE_DIVIDE(a, b)：BigQuery内置安全除法函数，当除数b为0时返回NULL，避免查询失败
• 若需要按业务维度分组计算（比如按区域、产品线），可添加PARTITION BY，例如：LAG(Amount) OVER (PARTITION BY region ORDER BY period_id ASC)

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