BigQuery结合Javascript UDF实现分批次累计计算late_fee与pending_principal

完全可以通过SQL实现该需求，不需要依赖JS UDF也能完成。你之前的实现存在两个核心问题：

1. 逻辑判断错位：原JS代码中用「上一行的rownum」做分支判断，而非「当前正在处理的行的rownum」，导致遇到新批次的起始行（rownum=1）时，根本没触发累计值重置的逻辑，直接沿用上一批次的计算结果。
2. 缺少批次隔离：rownum=1本质是独立计算批次的起始标记，原实现没有对不同批次的计算范围做隔离，把跨批次的行放在同一个聚合数组里计算，自然会出现累计值串用的问题。

方案1：BigQuery 原生递归CTE（推荐，无需JS） #

核心思路分两步：

• 先通过窗口函数给所有行打批次ID：按日期顺序累计所有rownum=1的出现次数，相同次数的行属于同一个独立计算批次，跨批次的计算完全独立
• 用递归CTE逐行计算：从每个批次的起始行开始，逐行关联同批次的下一行，按规则计算late_fee和pending_principal

WITH mytable AS (
  SELECT DATE '2020-01-01' AS date, 1 AS rownum , 0 AS late_fee, 100000 AS interest, 1000000 AS pending_principal UNION ALL
  SELECT DATE '2020-01-02', 2, NULL, 150000, NULL UNION ALL
  SELECT DATE '2020-01-03', 3, NULL, 200000, NULL UNION ALL
  SELECT DATE '2020-01-04', 4, NULL, 250000, NULL UNION ALL
  SELECT DATE '2020-01-05', 1, 100000 , 300000, 1000000 UNION ALL
  SELECT DATE '2020-01-06', 2, NULL, 900000, NULL
),
table_with_batch AS (
  SELECT 
    *,
    -- 按日期累计rownum=1的次数，作为独立批次ID
    SUM(IF(rownum = 1, 1, 0)) OVER (ORDER BY date) AS batch_id
  FROM mytable
),
recursive_calc AS (
  -- 递归锚点：取每个批次的起始行（rownum=1），直接使用原始存储值
  SELECT 
    date,
    rownum,
    late_fee,
    interest,
    pending_principal,
    batch_id
  FROM table_with_batch
  WHERE rownum = 1

  UNION ALL

  -- 递归关联：同批次内，用上一行的计算结果，计算下一行的值
  SELECT 
    curr.date,
    curr.rownum,
    -- 当前行late_fee = 上一行pending_principal * 5%
    CAST(ROUND(prev.pending_principal * 0.05) AS INT64) AS late_fee,
    curr.interest,
    -- 当前行pending_principal = 上一行值 + 当前late_fee + 当前interest
    CAST(ROUND(prev.pending_principal + prev.pending_principal * 0.05 + curr.interest) AS INT64) AS pending_principal,
    curr.batch_id
  FROM recursive_calc prev
  JOIN table_with_batch curr
    ON prev.batch_id = curr.batch_id -- 仅同批次内做关联
    AND curr.rownum = prev.rownum + 1 -- 取同批次的下一行
)
SELECT date, rownum, late_fee, interest, pending_principal
FROM recursive_calc
ORDER BY date

计算结果 #

完全匹配你预期的输出：

方案2：修正JS UDF实现 #

如果你一定要使用JS UDF，需要修正两个问题：

1. 循环内的分支判断要基于当前行的rownum，而非上一行的变量
2. 按批次拆分数据，把同一个批次的行聚合成数组传给UDF，避免跨批次值串用

CREATE TEMP FUNCTION udf_calc_batch(x ARRAY<STRUCT<rownum INT64, late_fee INT64, interest INT64, pending_principal INT64>>)
RETURNS ARRAY<STRUCT<rownum INT64, late_fee INT64, interest INT64, pending_principal INT64>>
LANGUAGE js AS """
  let accumulated_pp = 0;
  const result = [];
  for (const row of x) {
    let cur_late_fee, cur_pp;
    // 判断当前行是不是批次起点
    if (row.rownum === 1) {
      cur_late_fee = row.late_fee;
      cur_pp = row.pending_principal;
    } else {
      cur_late_fee = Math.round(accumulated_pp * 0.05);
      cur_pp = accumulated_pp + cur_late_fee + row.interest;
    }
    result.push({
      rownum: row.rownum,
      late_fee: cur_late_fee,
      interest: row.interest,
      pending_principal: cur_pp
    });
    // 更新累计值供下一行使用
    accumulated_pp = cur_pp;
  }
  return result;
""";

WITH mytable AS (
  SELECT DATE '2020-01-01' AS date, 1 AS rownum , 0 AS late_fee, 100000 AS interest, 1000000 AS pending_principal UNION ALL
  SELECT DATE '2020-01-02', 2, NULL, 150000, NULL UNION ALL
  SELECT DATE '2020-01-03', 3, NULL, 200000, NULL UNION ALL
  SELECT DATE '2020-01-04', 4, NULL, 250000, NULL UNION ALL
  SELECT DATE '2020-01-05', 1, 100000 , 300000, 1000000 UNION ALL
  SELECT DATE '2020-01-06', 2, NULL, 900000, NULL
),
table_with_batch AS (
  SELECT 
    *,
    SUM(IF(rownum = 1, 1, 0)) OVER (ORDER BY date) AS batch_id
  FROM mytable
),
batch_calc AS (
  SELECT 
    batch_id,
    udf_calc_batch(ARRAY_AGG(STRUCT(rownum, late_fee, interest, pending_principal) ORDER BY date)) AS calc_result
  FROM table_with_batch
  GROUP BY batch_id
)
SELECT date, rownum, late_fee, interest, pending_principal
FROM batch_calc,
UNNEST(calc_result) AS calc
ORDER BY date

• 计算时用ROUND处理金额，避免浮点数精度问题导致出现非整数的计算结果（比如原实现里出现的108608这类异常值）
• 数据量较大时优先选择递归CTE方案，相比JS UDF有更好的分布式计算性能，不会出现单节点处理的瓶颈

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