如何修复BigQuery标准SQL UDF中DECLARE/SET变量的语法错误？

注：此问题曾被判定为与某Stack Overflow问题重复，但现有答案未解决UDF内使用DECLARE/SET变量的问题，官方文档也未提及相关内容。

原JS UDF代码 #

CREATE TEMP FUNCTION earliest_full_payment(
    payments ARRAY<STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>>,
    td_charges_amount FLOAT64
)
RETURNS STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>
LANGUAGE js
AS
r"""
    let result = null;
    for(const payment of payments) {
        const payment_amount = (payment.amount || 0)
        const td_payment_amount = (payment.td_amount || 0)
        if (payment_amount < 0) {
            if (td_payment_amount < td_charges_amount) {
                result = null;
            }
        } else {
            if (td_payment_amount >= td_charges_amount) {
                result = payment
            }
        }
    }
return result
""";

尝试的Standard SQL UDF代码 #

CREATE TEMP FUNCTION earliest_full_payment(
    payments ARRAY<STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>>,
    td_charges_amount FLOAT64
)
RETURNS STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>
AS
((
    DECLARE result STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>;
    SET result = NULL;
    FOR payment IN UNNEST(payments) DO
        DECLARE payment_amount FLOAT64;
        DECLARE td_payment_amount FLOAT64;
        SET payment_amount = IFNULL(payment.amount, 0);
        SET td_payment_amount = IFNULL(payment.td_amount, 0);
        IF payment_amount < 0 THEN
            IF td_payment_amount < td_charges_amount THEN
              SET result = NULL;
            END IF;
        ELSE
            IF td_payment_amount >= td_charges_amount THEN
                SET result = payment
            END IF;
        END IF;
    END FOR;
    RETURN result;
));

报错信息 #

Syntax error: Unexpected identifier "result" at [8:13]

尝试过在UDF内外使用DECLARE，也用BEGIN..END包裹语句，但仍报错，需修复该语法错误。

修复方案 #

错误原因 #

1. Standard SQL UDF使用过程逻辑时，不能用外层双重括号((...))，必须用BEGIN ... END包裹整个过程代码。
2. BigQuery过程语言要求块内的变量声明必须放在所有可执行语句之前，不能在FOR循环内部DECLARE变量。
3. 缺少显式的LANGUAGE sql声明（虽默认是SQL，但显式声明更规范）。
4. 代码中SET result = payment末尾缺少分号，违反SQL语法规范。

正确代码 #

CREATE TEMP FUNCTION earliest_full_payment(
    payments ARRAY<STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>>,
    td_charges_amount FLOAT64
)
RETURNS STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>
LANGUAGE sql
AS
BEGIN
    -- 所有变量声明必须放在块的最顶部
    DECLARE result STRUCT<event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE>;
    DECLARE payment_amount FLOAT64;
    DECLARE td_payment_amount FLOAT64;
    
    SET result = NULL;
    
    FOR payment IN UNNEST(payments) DO
        SET payment_amount = IFNULL(payment.amount, 0);
        SET td_payment_amount = IFNULL(payment.td_amount, 0);
        
        IF payment_amount < 0 THEN
            IF td_payment_amount < td_charges_amount THEN
                SET result = NULL;
            END IF;
        ELSE
            IF td_payment_amount >= td_charges_amount THEN
                SET result = payment;
            END IF;
        END IF;
    END FOR;
    
    RETURN result;
END;

内容的提问来源于Stack Exchange，提问作者Philippe Hebert