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,因此识别失败。
具体修复方案
- 修正内层子查询的字段定义
删除多余的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` )
- 修正动态SQL生成逻辑
在外层生成动态SQL的查询中,确保返回结果为单行单个字符串,可以在外层SELECT后增加STRING_AGG聚合,或者直接在末尾加LIMIT 1,保证EXECUTE IMMEDIATE接收的是合法标量输入。 - 修正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
相关产品推荐
相关产品推荐

