如何修复BigQuery中动态SQL的变量声明及语法错误？

错误原因解析 #

• 第一个报错variable declarations are allowed only at the start of a block or script：
  除了变量声明必须放在脚本最开头，原代码中SET column_list语句末尾缺少分号，BigQuery对语句结束符有严格要求。
• 第二个报错No matching signature for aggregate function STRING_AGG：
  原代码中STRING_AGG('SUM(', column, ') AS ', column)传递了4个参数，不符合BigQuery中STRING_AGG的语法规则——该函数仅支持1个（仅聚合表达式）或2个（聚合表达式+分隔符）参数。

修复后的完整代码 #

DECLARE column_list ARRAY<STRING>;
DECLARE query STRING;

SET column_list = [
  'prod_ab_amt',
  'prod_bf_amt',
  'prod_gm_amt',
  'catg_cg_amt',
  'catg_dh_amt',
  'catg_xy_amt',
  'catg_mk_amt',
  'brand_hk_amt',
  'brand_hp_amt',
  'brand_vm_amt'
];

SET query = (
  SELECT CONCAT(
    'SELECT user_id, EXTRACT(YEAR FROM purchase_date) AS purch_yr, ',
    'CASE WHEN EXTRACT(MONTH FROM purchase_date) < 7 THEN 1 ELSE 2 END AS purch_half_period, ',
    STRING_AGG(CONCAT('SUM(', column, ') AS ', column), ', '),
    ' FROM trans',
    ' GROUP BY user_id, purch_yr, purch_half_period'
  )
  FROM UNNEST(column_list) AS column
);

EXECUTE IMMEDIATE query;

核心修复说明 #

1. 给SET column_list语句补充末尾分号，满足BigQuery语法规范；
2. 调整STRING_AGG的用法：先通过CONCAT生成单个SUM(列名) AS 列名格式的字符串，再用STRING_AGG(..., ', ')将所有聚合列语句用逗号分隔拼接，匹配函数支持的签名；
3. 保持变量声明在脚本最开头，符合BigQuery脚本执行规则。

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