将SQL Server含动态偏移量的LAG函数查询迁移至BigQuery遇阻

直接用以下SQL即可实现你要的效果，完全匹配原SQL Server查询的逻辑：

WITH cpi_with_month_num AS (
  SELECT
    *,
    YEAR * 12 + MONTH AS month_num,
    ((MONTH + 8) % 6) + 4 AS offset_months
  FROM SYSTEM_CPI
)
SELECT
  main.YEAR,
  main.MONTH,
  main.CPI,
  LAG(main.CPI, 1) OVER (ORDER BY main.YEAR, main.MONTH) AS CPI_1,
  LAG(main.CPI, 2) OVER (ORDER BY main.YEAR, main.MONTH) AS CPI_2,
  LAG(main.CPI, 3) OVER (ORDER BY main.YEAR, main.MONTH) AS CPI_3,
  target.CPI AS CPI_6M_LAG4
FROM cpi_with_month_num main
LEFT JOIN cpi_with_month_num target
  ON main.month_num - main.offset_months = target.month_num
ORDER BY main.YEAR, main.MONTH;

为什么要这么改？ #

BigQuery的LAG函数不支持用列作为偏移量参数（只能用常量），但我们可以通过以下步骤绕开这个限制：

1. 把YEAR和MONTH转换成连续的月份编号（比如2020年5月转为2020*12+5=24245），这样就能用数值计算精准定位目标月份。
2. 用CTE提前算出每个行需要的偏移量offset_months，逻辑和你原SQL里的((MONTH+8)%6)+4完全一致。
3. 通过自连接关联到目标月份的CPI数据，替代原SQL中动态偏移的LAG行为，返回结果和原查询完全一致（没有对应数据时返回NULL，和LAG的默认行为匹配）。

额外说明 #

• 基础的CPI_1、CPI_2、CPI_3可以直接用BigQuery的LAG函数，因为它们的偏移量是常量，BigQuery完全支持。
• 如果你的SYSTEM_CPI表数据量很大，可以给month_num字段添加索引，提升自连接的查询性能。

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