在BigQuery UDF中使用WITH RECURSIVE报错，寻求解决方法

错误原因 #

BigQuery明确限制WITH RECURSIVE只能用于顶层语句（如SELECT、CREATE TABLE AS SELECT、CREATE VIEW等），不能嵌套在UDF的子查询或表达式内部，这就是你遇到报错的直接原因。

解决方案 #

方案1：改用JavaScript UDF #

利用JavaScript的循环逻辑实现迭代计算，完全匹配你的递归需求：

CREATE OR REPLACE FUNCTION accounting.monthly_sum(arr ARRAY<STRUCT<aq NUMERIC, ap NUMERIC, m INT64>>)
RETURNS NUMERIC
LANGUAGE js AS """
  // 先按月份m排序，确保计算顺序正确
  const sortedItems = arr.sort((a, b) => a.m - b.m);
  let accumulatedAR = 0;
  for (const item of sortedItems) {
    accumulatedAR = Math.min(item.aq + accumulatedAR, item.ap);
  }
  return accumulatedAR;
""";

方案2：纯SQL模拟迭代（无需JS） #

用窗口函数LAG()获取上一步的计算结果，替代递归CTE：

CREATE OR REPLACE FUNCTION accounting.monthly_sum(arr ARRAY<STRUCT<aq NUMERIC, ap NUMERIC, m INT64>>)
RETURNS NUMERIC
AS (
  (
    SELECT ar
    FROM (
      SELECT
        item.m,
        LEAST(item.aq + COALESCE(LAG(ar) OVER(ORDER BY item.m), 0), item.ap) AS ar
      FROM UNNEST(arr) item
    )
    ORDER BY m DESC
    LIMIT 1
  )
);

逻辑说明 #

两种方案都实现了和你原递归CTE一致的逻辑：

• 确保数组按月份m递增排序（JS方案主动排序，SQL方案通过窗口函数的ORDER BY保证顺序）
• 从初始值0开始，依次计算每个月的ar值：min(当月aq + 上月ar, 当月ap)
• 返回最后一个月的ar结果

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