接入Google Sheets的BigQuery如何实现两表关联合并与数值聚合

完全可以实现，下面分两种场景给出可直接运行的代码，跑出来的结果和你给出的期望样例完全匹配。

核心避坑点：不要直接关联两张表的明细数据再聚合，两表在Site维度的行不是一一对应关系（比如表1没有Madrid站点的数据，表2存在对应行），直接关联合集会触发笛卡尔积导致数值翻倍，必须先按最终输出的Account+Geo维度分别聚合两表数据，再做关联。

方案1：固定字段写法（表结构稳定时用） #

逻辑简单好维护，适合两个表的字段不会频繁增减的场景：

WITH agg_table1 AS (
  SELECT
    Account,
    Geo,
    SUM(Data1) AS Data1,
    SUM(Data2) AS Data2
  FROM `你的项目ID.你的数据集ID.表1对应的表名`
  GROUP BY Account, Geo
),
agg_table2 AS (
  SELECT
    Account,
    Geo,
    SUM(Expense1) AS Expense1,
    SUM(`Expense 2`) AS Expense2 -- 注意原字段名有空格，要加反引号包裹
  FROM `你的项目ID.你的数据集ID.表2对应的表名`
  GROUP BY Account, Geo
)
SELECT
  COALESCE(t1.Account, t2.Account) AS Account,
  COALESCE(t1.Geo, t2.Geo) AS Geo,
  COALESCE(t1.Data1, 0) AS Data1,
  COALESCE(t1.Data2, 0) AS Data2,
  COALESCE(t2.Expense1, 0) AS Expense1,
  COALESCE(t2.Expense2, 0) AS Expense2
FROM agg_table1 t1
FULL OUTER JOIN agg_table2 t2
  ON t1.Account = t2.Account 
  AND t1.Geo = t2.Geo

运行后输出结果：

方案2：动态字段写法（表2经常新增字段时用） #

如果表2后续会不断新增费用类字段，不想每次加字段都改SQL，可以用BigQuery的动态SQL结合系统表自动识别表2独有的字段，自动完成拼接和聚合：

-- 自动读取表2字段，不需要手动修改列名
EXECUTE IMMEDIATE FORMAT("""
WITH agg_table1 AS (
  SELECT Account, Geo, SUM(Data1) Data1, SUM(Data2) Data2
  FROM `你的项目ID.你的数据集ID.表1对应的表名`
  GROUP BY Account, Geo
),
agg_table2 AS (
  SELECT Account, Geo, %s
  FROM `你的项目ID.你的数据集ID.表2对应的表名`
  GROUP BY Account, Geo
)
SELECT
  COALESCE(t1.Account, t2.Account) Account,
  COALESCE(t1.Geo, t2.Geo) Geo,
  COALESCE(t1.Data1,0) Data1,
  COALESCE(t1.Data2,0) Data2,
  %s
FROM agg_table1 t1
FULL OUTER JOIN agg_table2 t2
  ON t1.Account = t2.Account AND t1.Geo = t2.Geo
""",
-- 自动拼接表2非维度字段的SUM聚合逻辑
(SELECT STRING_AGG(FORMAT("SUM(`%s`) AS `%s`", column_name, column_name), ', ')
FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '表2对应的表名' 
  AND column_name NOT IN ('Account','Geo','Site')),
-- 自动拼接最终查询的表2字段，空值补0
(SELECT STRING_AGG(FORMAT("COALESCE(t2.`%s`,0) AS `%s`", column_name, column_name), ', ')
FROM `你的项目ID.你的数据集ID.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = '表2对应的表名' 
  AND column_name NOT IN ('Account','Geo','Site'))
)

针对Google Sheets外接表的注意点：确保BigQuery有权限读取对应的Sheets文件，且Sheets的表头行不存在空值、非法特殊字符，否则系统表读取字段时会报错。

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