You need to enable JavaScript to run this app. Python调用BigQuery执行EXECUTE IMMEDIATE合并转置表报错求解
优惠活动
大模型
产品
解决方案
定价
更多

Python调用BigQuery执行EXECUTE IMMEDIATE合并转置表报错求解

问题排查及修复方案

错误1:Scalar subquery produced more than one element

原因:EXECUTE IMMEDIATE要求输入参数为单个标量字符串,但你生成动态SQL的外层SELECT语句返回了多行(每行对应一个brand_month值),没有聚合为单行结果,因此触发报错。

错误2:Unrecognized name: brand_month

原因:最终执行的SQL中,内层子查询尝试查询不存在的brand_month字段,你没有给「PRODUCT拼接月份」的计算字段起别名brand_month,反而错误将brand_month作为原表字段引用,而你的原表只有ID、Date、PRODUCT、P、R五个字段,不存在brand_month,因此识别失败。

具体修复方案

  1. 修正内层子查询的字段定义
    删除多余的as Month和错误引用的brand_month字段,给拼接后的计算字段起别名brand_month,修正后的内层查询如下:
SELECT 
  ID,
  CONCAT(PRODUCT, '_', EXTRACT(month FROM PARSE_DATETIME('%d/%m/%Y %H:%M', Date))) AS brand_month,
  P,
  R
FROM `project.test_string_date`
WHERE ID IN (
  SELECT DISTINCT CAST(ID as INT) as ID
  FROM `project.blue_test.data1`
)
  1. 修正动态SQL生成逻辑
    在外层生成动态SQL的查询中,确保返回结果为单行单个字符串,可以在外层SELECT后增加STRING_AGG聚合,或者直接在末尾加LIMIT 1,保证EXECUTE IMMEDIATE接收的是合法标量输入。
  2. 修正Python代码中的拼接错误
    你原query0的拼接逻辑存在语法错误,brand_column + " || '_' || as brand_month"缺少年份/月份的拼接字段,需要替换为正确的日期提取+拼接逻辑,和你最终打印的SQL中的日期解析逻辑对齐。

修正后最终执行SQL参考

SELECT * FROM (
  SELECT 
    ID,
    CONCAT(PRODUCT, '_', EXTRACT(month FROM PARSE_DATETIME('%d/%m/%Y %H:%M', Date))) AS brand_month,
    P,
    R
  FROM `project.test_string_date`
  WHERE ID IN (
    SELECT DISTINCT CAST(ID as INT) as ID
    FROM `project.blue_test.data1`
  )
)
PIVOT (
  sum(P) P, 
  sum(R) R 
  FOR brand_month IN ("a_1","a_10","a_2","a_3","a_4","a_5","a_6","a_7","a_8","a_9","b_1","b_10","b_2","b_3","b_4","b_5","b_6","b_7","b_8","b_9","c_1","c_10","c_2","c_3","c_4","c_5","c_6","c_7","c_8","c_9","d_1","d_10","d_2","d_3","d_4","d_5","d_6","d_7","d_8","d_9")
)
ORDER BY ID

内容的提问来源于stack exchange,提问作者VMEscoli

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.16 11:54:03