英文:

BigQuery UDF: DECLARE/SET variable inside Standard SQL UDF body

问题

Here is the translated content:

注意：这可能被视为与[如何在BigQuery UDF体内声明变量？](https://stackoverflow.com/questions/63147752/how-to-declare-variable-inside-bigquery-udf-body)重复，但我对提供的答案不满意 - 没有一个答案说明如何在UDF内部声明/设置变量，语言文档[[1]](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language)、[[2]](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions)也没有讨论这个问题。

给定以下JS UDF：

```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 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
""";

我正在尝试生成等效的标准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;
));

但我得到了语法错误：意外的标识符"result"，位置在[8:13]。
我尝试过在UDF内部和外部使用DECLARE，并在语句周围使用BEGIN..END。

您有什么办法可以修复语法错误吗？

<details>
<summary>英文:</summary>

NOTE: This may be considered a duplicate of [How to declare variable inside BigQuery UDF body?](https://stackoverflow.com/questions/63147752/how-to-declare-variable-inside-bigquery-udf-body), but I am not satisfied with the answers provided - None answer how to DECLARE/SET variables within the UDF, and the language documentation [[1]](https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language), [[2]](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions) does not discuss this either.

Given the following 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
""";

I am trying to produce the equivalent 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;
));

but I get `Syntax error: Unexpected identifier "result" at [8:13]`.
I tried both with the DECLARE inside and outside of the UDF, and with a BEGIN..END around the statements.

Any idea how I can fix the syntax error?

</details>


# 答案1
**得分**: 1

以下是您要翻译的内容：

"不确定我是否理解了您在UDFs中的逻辑，但似乎您可以像下面这样以SQL风格的UDF来实现它。

- 下面的UDF已通过您定义的所有测试用例

```sql
CREATE TEMP FUNCTION earliest_full_payment(
  payments ARRAY&lt;STRUCT&lt;event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE&gt;&gt;,
  td_charges_amount FLOAT64
) AS (((
  SELECT CASE
           WHEN IFNULL(p.amount, 0) &gt;= 0 AND IFNULL(p.td_amount, 0) &gt;= td_charges_amount THEN p
           WHEN IFNULL(p.amount, 0) &lt; 0 AND IFNULL(p.td_amount, 0) &lt; td_charges_amount THEN null
         END
   FROM UNNEST(payments) p WITH offset
  ORDER BY offset DESC LIMIT 1
))));
```"

<details>
<summary>英文:</summary>

Not sure I understood your logic in UDFs, but seems you can implement it with an UDF in SQL-lish way like below.

- Below UDF has passed all TCs you defined

```sql
CREATE TEMP FUNCTION earliest_full_payment(
  payments ARRAY&lt;STRUCT&lt;event_key STRING, amount FLOAT64, td_amount FLOAT64, date DATE&gt;&gt;,
  td_charges_amount FLOAT64
) AS ((
  SELECT CASE
           WHEN IFNULL(p.amount, 0) &gt;= 0 AND IFNULL(p.td_amount, 0) &gt;= td_charges_amount THEN p
           WHEN IFNULL(p.amount, 0) &lt; 0 AND IFNULL(p.td_amount, 0) &lt; td_charges_amount THEN null
         END
   FROM UNNEST(payments) p WITH offset
  ORDER BY offset DESC LIMIT 1
));