google-bigquery - 如何更新（乘以 5 而不是简单地替换）BigQuery 中的嵌套收入字段？

Question

我正在尝试在 BigQuery 中使用 DML 来更新嵌套的收入字段。挑战在于，我不想简单地替换收入的价值，而是将其与特定因素相乘。

只是替换我发现：

UPDATE `project.dataset.table`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    (SELECT AS STRUCT transaction.* REPLACE ( 1 AS transactionRevenue)) AS transaction
  )
  FROM UNNEST(hits) as transactionRevenue
)
WHERE true

但我想有类似的东西：

UPDATE `project.dataset.table`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE (
    (SELECT AS STRUCT transaction.* REPLACE ( (transactionRevenue*5) AS transactionRevenue)) AS transaction
  )
  FROM UNNEST(hits) as transactionRevenue
)
WHERE true

这种方法行不通。错误消息：对于参数类型：STRUCT、INT64 的运算符 * 没有匹配的签名。支持的签名：INT64 * INT64；FLOAT64 * FLOAT64; 数字 * 数字 [4:48]

标签： google-bigquerydml

Answer 1

下面应该工作

UPDATE `project.dataset.table`
SET hits = ARRAY(
  SELECT AS STRUCT * REPLACE(
    (SELECT AS STRUCT * REPLACE(5 * transactionRevenue AS transactionRevenue) 
      FROM UNNEST([transaction])
    ) AS transaction
  ) 
  FROM t.hits
)
WHERE true