如何在BigQuery中查询表名需通过另一查询获取的多表并做聚合

可行性说明 #

该需求可以实现。你之前尝试的写法不生效的原因是标准SQL不支持直接将拼接得到的字符串作为表名代入查询逻辑，需要使用BigQuery提供的动态SQL语法EXECUTE IMMEDIATE来实现动态表名查询。

实现方案 #

以下是可以直接使用的SQL代码，执行后会返回每个dataset对应的some_table表的x字段聚合结果：

DECLARE query_str STRING;

-- 动态拼接所有表的查询逻辑，用UNION ALL关联
SET query_str = (
  SELECT STRING_AGG(
    FORMAT("SELECT '%s' AS dataset_id, SUM(x) AS sum_x FROM `some_project.%s.some_table`", dataset_id, dataset_id),
    " UNION ALL "
  )
  FROM `some_project.metadata.metadata`
);

-- 执行生成的动态查询
EXECUTE IMMEDIATE query_str;

逻辑说明 #

• 首先声明字符串类型变量query_str，用于存储最终要执行的完整SQL语句
• 利用STRING_AGG函数遍历metadata表的所有dataset_id，为每个id生成对应的单表聚合查询，再通过UNION ALL把所有查询拼接为整体
• FORMAT函数用于标准化拼接逻辑，避免字符串拼接出错，同时将dataset_id作为结果列返回，方便对应每个表的聚合结果
• 最后通过EXECUTE IMMEDIATE执行拼接完成的SQL语句，得到最终结果

注意事项 #

• 需确保所有some_project.{dataset_id}.some_table表都真实存在，且都包含x字段，否则执行会报错
• 如果不需要区分每个表的聚合结果，只需要所有表的x字段总和，可以使用以下简化写法：

DECLARE query_str STRING;

SET query_str = (
  SELECT "SELECT SUM(x) AS total_sum FROM (" || 
    STRING_AGG(
      FORMAT("SELECT x FROM `some_project.%s.some_table`", dataset_id),
      " UNION ALL "
    ) || ")"
  FROM `some_project.metadata.metadata`
);

EXECUTE IMMEDIATE query_str;

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