BigQuery左连接查询中如何对嵌套表所有字段执行Group By操作

方案1：预聚合子表（最优方案，无需分组） #

你原来的逐轮连接后分组的写法可以直接优化为提前聚合所有子表，完全规避主表/中间表的分组需求，同时性能更优，且天然保留Table1中ID相同但其他字段不同的独立行：

WITH
-- 预聚合所有需要嵌套的子表，提前按ID生成嵌套数组
agg_table2 AS (
  SELECT ID, ARRAY_AGG((SELECT AS STRUCT t.* EXCEPT(ID))) AS Table2
  FROM Table2 t
  GROUP BY ID
),
agg_table3 AS (
  SELECT ID, ARRAY_AGG((SELECT AS STRUCT t.* EXCEPT(ID))) AS Table3
  FROM Table3 t
  GROUP BY ID
),
-- 以此类推生成agg_table4到agg_table10
agg_table10 AS (
  SELECT ID, ARRAY_AGG((SELECT AS STRUCT t.* EXCEPT(ID))) AS Table10
  FROM Table10 t
  GROUP BY ID
)

-- 直接与主表左连接即可，主表所有行都会完整保留
SELECT 
  t1.*,
  IFNULL(t2.Table2, []) AS Table2,
  IFNULL(t3.Table3, []) AS Table3,
  -- 依次补全到Table10
  IFNULL(t10.Table10, []) AS Table10
FROM Table1 t1
LEFT JOIN agg_table2 t2 USING(ID)
LEFT JOIN agg_table3 t3 USING(ID)
-- 依次连接到agg_table10
LEFT JOIN agg_table10 t10 USING(ID)

方案2：沿用逐轮嵌套写法的修复方案 #

如果你的业务逻辑必须保留逐轮生成R表的结构，可以通过序列化整行作为分组键，等效实现按所有字段分组的效果：

R1 AS(
  SELECT ANY_VALUE(t1).*, ARRAY_AGG(( SELECT AS STRUCT Table2.* EXCEPT(ID))) AS Table2
  FROM Table1 t1 LEFT JOIN Table2 USING(ID)
  -- 序列化整行作为分组键，等效按Table1所有字段分组
  GROUP BY TO_JSON_STRING(t1)
),
R2 AS(
  SELECT ANY_VALUE(r1).*, ARRAY_AGG(( SELECT AS STRUCT Table3.* EXCEPT(ID))) AS Table3
  FROM R1 r1 LEFT JOIN Table3 USING(ID)
  -- 中间R表也直接序列化整行分组
  GROUP BY TO_JSON_STRING(r1)
),
-- 以此类推到R9
SELECT ANY_VALUE(r9).*, ARRAY_AGG(( SELECT AS STRUCT Table10.* EXCEPT(ID))) AS Table10
FROM R9 r9 LEFT JOIN Table10 USING(ID)
GROUP BY TO_JSON_STRING(r9)

原理说明 #

TO_JSON_STRING函数会将整行的所有字段值序列化为唯一字符串，内容完全相同的行会生成相同的字符串，以此作为分组键即可实现按整行所有字段分组的需求，兼容包含嵌套字段的场景。

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