BigQuery无递归CTE场景下逐行递归乘法累加计算方案问询

核心思路 #

利用BigQuery的窗口函数按日期排序累积所有历史参数到数组，再通过自定义JS函数遍历数组完成迭代计算，完全避开递归CTE限制和EXP数值溢出问题。

完整实现代码 #

-- 自定义临时JS迭代计算函数
CREATE TEMP FUNCTION js_calculate(init_a FLOAT64, params ARRAY<STRUCT<b FLOAT64, c FLOAT64>>)
RETURNS FLOAT64
LANGUAGE js AS """
  let res = init_a;
  for(let i=0; i<params.length; i++){
    res = res * (1 - params[i].b) + params[i].c;
  }
  return res;
""";

WITH sample_data AS (
  -- 你的样例表数据
  SELECT DATE("2021-09-01") AS date, 50 AS a, 0.5 AS b, 2 AS c UNION ALL
  SELECT DATE("2021-09-02") AS date, NULL AS a, 0.6 AS b, 1 AS c UNION ALL
  SELECT DATE("2021-09-03") AS date, NULL AS a, 0.4 AS b, 3 AS c
),
ordered_data AS (
  SELECT 
    *,
    -- 提取首行初始a值
    FIRST_VALUE(a) OVER(ORDER BY date ASC) AS init_a,
    ROW_NUMBER() OVER(ORDER BY date ASC) AS rn
  FROM sample_data
),
agg_params AS (
  SELECT 
    *,
    -- 累积从首行到当前行的所有b、c参数
    ARRAY_AGG(STRUCT(b, c)) OVER(ORDER BY rn ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS params_list
  FROM ordered_data
)
SELECT 
  date,
  a,
  b,
  c,
  js_calculate(init_a, params_list) AS calculation
FROM agg_params
ORDER BY date ASC

输出结果 #

和你预期完全一致：

方案说明 #

• 性能层面：即使数据到2021年末也就百余行，数组遍历开销可忽略，无性能压力
• 数值范围：依托JS数值精度，完全覆盖大c值场景，不会出现EXP方案的溢出问题
• 扩展适配：如果存在多组独立序列需要分别计算，只需在所有窗口函数中增加PARTITION BY 分组字段即可，核心逻辑无需调整

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