BigQuery多数据集循环查询报FORMAT参数过多错误咨询

报错的核心是两层FORMAT的格式占位符冲突：

• 你用外层FORMAT()函数拼接动态SQL时，函数会扫描传入的模板字符串，把所有%开头的合法格式符（比如%s/%d/%f）识别为待替换的占位符，按顺序匹配你传入的参数。
• 你的模板里写了内层FORMAT_DATE("%Y/%m/%d", Date)的逻辑，其中的%d会被外层FORMAT()识别为整数类型的占位符，统计后认为只需要传入1个参数匹配这个位置即可。
• 你额外传入了data_set.id作为参数，同时模板里没有为数据集ID预留对应的占位符，最终参数数量超过函数识别到的占位符数量，触发报错。
• 你测试传2个data_set.id时提示需要1个得到3个，是因为外层始终只识别到%d这1个占位符，传入N个参数就会提示需要1个得到N+1。

这是BigQuery循环语法的固定取值逻辑：

• 你定义的FOR data_set IN (SELECT * FROM UNNEST(data_set_list) as id)是数组遍历循环，会依次取出data_set_list数组里的每一个元素，每轮循环生成一个名为data_set的行对象。
• id是你给UNNEST(data_set_list)拆分数组后生成的列取的别名，data_set.id就对应当前循环轮次取出的数组元素值：第一轮循环值为12345678，第二轮循环值为45678912。
• 你当前的脚本里没有把这个值用到动态SQL的模板中，等于循环两次查询同一张写死的表，会向目标表插入重复数据，完全没实现跨数据集复用查询逻辑的目的。

需要做两处修改：

1. 把模板里内层FORMAT_DATE用到的所有%转义为%%，让外层FORMAT()把这些内容识别为普通字符，处理后输出单个%供内层FORMAT_DATE使用，不会误判为占位符。
2. 在模板里查询源表的位置添加%s占位符，用来接收循环传入的数据集ID，实现跨数据集查询。

DECLARE data_set_list ARRAY<STRING> DEFAULT ['12345678', '45678912'];

CREATE OR REPLACE TABLE `xxx.TEST.Test_file`  
(date DATE, data_source STRING,promo_identifier STRING,promo_description STRING,deviceCategory STRING, channelGrouping STRING, sourcemedium STRING,landing_page STRING,site STRING, region STRING,
sessions INT64, total_events INT64, orders INT64, duration INT64, revenue FLOAT64);

FOR data_set IN (SELECT * FROM UNNEST(data_set_list) as id) DO
 EXECUTE IMMEDIATE FORMAT(
  """
 INSERT INTO `xxx.TEST.Test_file` 
SELECT FORMAT_DATE("%%Y/%%m/%%d", Date) as date,data_source,
promo_identifier, promo_description, deviceCategory, channelGrouping, sourcemedium, landing_page,
site, region,
SUM(sessions) AS Sessions, SUM(total_events) AS Total_events, SUM(orders) AS Orders, SUM(duration) AS Duration, SUM(revenue) AS Revenue
from `xxx.%s.masterdata`
group by 1,2,3,4,5,6,7,8,9,10
  """
 , data_set.id);
END FOR;

注意：如果你的源表项目、表名规则和示例里的xxx.{数据集ID}.masterdata不一致，需要自行调整占位符所在的表名位置。

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