BigQuery使用JOIN与UNNEST提取嵌套字段时消除重复行
BigQuery 嵌套重复字段关联重复问题优化方案
问题根因
当前写法同时对源表的两个独立REPEATED数组(Address、Work)执行了UNNEST交叉连接,两个数组无关联匹配条件,会直接生成笛卡尔积:
当Title为A的行内,Address数组有2条记录、Work数组有2条记录时,交叉连接后会生成2×2=4条中间行,后续ARRAY_AGG聚合时,会把所有重复的地址、工作条目都合并进最终数组,直接造成冗余重复数据。
优化方案
核心逻辑是两个数组的拆解操作完全隔离,不做交叉匹配,从根源上避免笛卡尔积生成,可根据实际场景选择对应写法:
场景1:仅需保留原行的两个数组结构(无跨行合并需求)
直接在SELECT子句中对单个数组做独立的拆解+重构,完全不涉及跨数组关联:
SELECT Title, -- 单独拆解Address数组,聚合为目标结构的独立子查询,不与Work关联 ARRAY( SELECT AS STRUCT a.Field1, a.Field2 FROM UNNEST(Address) a ) AS Address, -- 单独拆解Work数组,聚合为目标结构的独立子查询,不与Address关联 ARRAY( SELECT AS STRUCT w.Field1, w.Field2 FROM UNNEST(Work) w ) AS Work FROM source_table
如果不需要对原数组字段做清洗、裁剪,可直接保留原嵌套字段,无需额外拆解:
SELECT Title, Address, Work FROM source_table
场景2:需要按Title聚合多行源表数据为单行目标数据
拆分为两个独立的聚合子查询,分别处理Address和Work数组,避免在同一条关联链路中同时拆解两个数组:
SELECT t.Title, addr.Address, work.Work FROM (SELECT DISTINCT Title FROM source_table) t -- 独立聚合同Title下所有的Address条目 LEFT JOIN ( SELECT Title, ARRAY_AGG(STRUCT(Field1, Field2)) AS Address FROM source_table, UNNEST(Address) AS a GROUP BY Title ) addr ON t.Title = addr.Title -- 独立聚合同Title下所有的Work条目 LEFT JOIN ( SELECT Title, ARRAY_AGG(STRUCT(Field1, Field2)) AS Work FROM source_table, UNNEST(Work) AS w GROUP BY Title ) work ON t.Title = work.Title
可选优化:聚合去重
如果聚合过程中发现有重复条目,可在ARRAY_AGG后追加DISTINCT关键字,直接对重复的STRUCT结构去重:
ARRAY_AGG(DISTINCT STRUCT(Field1, Field2))
内容的提问来源于stack exchange,提问作者moirK
相关产品推荐
相关产品推荐

