使用窗口函数的Running Total UDF报错：Failed to coerce output value

错误原因分析 #

你遇到的类型转换错误，核心是自定义JS函数的参数定义与传入值不匹配：

• 函数定义为接收单个FLOAT64类型值，但调用时通过array_agg(number) over (...)传入的是当前行及之前所有数值组成的数组（比如第二行传入[8,11.5]，第三行传入[8,11.5,-20]）。
• 函数逻辑里直接把数组和累计值相加，导致数组无法转换为FLOAT64，触发报错。

解决方案 #

方案一：修正JS UDF（推荐） #

利用BigQuery JS UDF在窗口分区内的状态保留特性，直接逐行传入单个数值，维护每个ID的累计状态：

CREATE TEMP FUNCTION conditional_running_sum(val FLOAT64)
RETURNS FLOAT64
LANGUAGE js AS r"""
    // 初始化当前分区的累计值
    if (!('current_sum' in this)) {
        this.current_sum = 0;
    }
    // 计算条件累计和：若累加后小于0则重置为0
    this.current_sum = Math.max(this.current_sum + val, 0);
    return this.current_sum;
""";

SELECT 
    Date,
    ID,
    Number,
    conditional_running_sum(Number) OVER (PARTITION BY ID ORDER BY Date) AS run_sum
FROM tbl;

方案二：纯SQL递归CTE实现 #

如果不想使用JS UDF，可以用递归CTE逐行计算条件累计和：

WITH ranked_data AS (
    -- 给每个ID内的行按日期排序
    SELECT 
        Date,
        ID,
        Number,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS row_num
    FROM tbl
),
recursive_sum AS (
    -- 初始化每个ID的第一行
    SELECT 
        Date,
        ID,
        Number,
        row_num,
        GREATEST(Number, 0) AS run_sum
    FROM ranked_data
    WHERE row_num = 1
    UNION ALL
    -- 递归计算后续行的累计和
    SELECT 
        rd.Date,
        rd.ID,
        rd.Number,
        rd.row_num,
        GREATEST(rs.run_sum + rd.Number, 0) AS run_sum
    FROM ranked_data rd
    JOIN recursive_sum rs ON rd.ID = rs.ID AND rd.row_num = rs.row_num + 1
)
SELECT 
    Date,
    ID,
    Number,
    run_sum
FROM recursive_sum
ORDER BY ID, Date;

两种方案运行后，run_sum列都会与你提供的期望结果(Desired)完全匹配。

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