如何在BigQuery SQL中自引用计算列实现递推计算

由于new_cad的计算依赖之前所有行计算出的new_cad累计值，而非原始数据的固定累计，普通窗口函数无法满足需求，必须使用递归查询来逐行处理并维护累计状态。

以下是针对你的需求编写的BigQuery SQL代码：

WITH RECURSIVE cte AS (
  -- 初始行：取row_number=1的记录，初始化累计值
  SELECT
    row_number,
    usd,
    cad,
    cad AS new_cad,
    cad AS cad_sum,
    usd AS usd_sum
  FROM your_table
  WHERE row_number = 1
  
  UNION ALL
  
  -- 递归处理后续行
  SELECT
    t.row_number,
    t.usd,
    t.cad,
    -- 根据cad是否为null计算new_cad
    CASE
      WHEN t.cad IS NOT NULL THEN t.cad
      ELSE (cte.cad_sum / cte.usd_sum) * t.usd
    END AS new_cad,
    -- 更新累计cad总和：当前new_cad加上之前的累计
    CASE
      WHEN t.cad IS NOT NULL THEN cte.cad_sum + t.cad
      ELSE cte.cad_sum + (cte.cad_sum / cte.usd_sum) * t.usd
    END AS cad_sum,
    -- 更新累计usd总和：当前usd加上之前的累计
    cte.usd_sum + t.usd AS usd_sum
  FROM cte
  JOIN your_table t ON t.row_number = cte.row_number + 1
)

-- 最终输出所需列
SELECT row_number, usd, cad, new_cad
FROM cte
ORDER BY row_number;

代码说明： #

1. 初始CTE：选取row_number=1的记录（已知该行cad非空），直接将cad赋值给new_cad，同时初始化累计cad_sum和usd_sum为该行的cad和usd值。
2. 递归逻辑：通过关联下一行数据逐行处理：
   • 若当前行cad非空，new_cad直接取cad，并将cad累加到cad_sum；
   • 若当前行cad为空，用之前的累计cad_sum除以累计usd_sum得到汇率，再乘以当前usd得到new_cad，并将该值累加到cad_sum；
   • 累计usd_sum始终是之前的总和加上当前行usd。
3. 输出结果：从递归结果中提取目标列并按row_number排序。

验证结果： #

运行上述代码后，输出将与你期望的结果完全一致：

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