BigQuery按条件UNNEST嵌套字段及CROSS JOIN最优实现方法

现有月度采购表包含两个嵌套重复字段items和payment，表结构如下：

user      STRING
items     REPEATED RECORD
     class      STRING 
     spent      INT 
payment   REPEATED RECORD 
     method  STRING  

需求为：

• 筛选items中class为fruit、drinks的记录，将对应spent值拆分为独立列
• 对用户和支付方式做常规CROSS JOIN关联
  原多CTE写法存在多次扫描源表、多余JOIN的问题，执行效率低。

不需要拆分为多个CTE反复扫描表、做自连接，单次扫描即可完成计算，参考代码如下：

SELECT
  user,
  pay.method AS payment,
  -- 直接从items数组中提取对应分类的消费金额
  (SELECT spent FROM UNNEST(items) WHERE class = 'fruit') AS spent_fruit,
  (SELECT spent FROM UNNEST(items) WHERE class = 'drinks') AS spent_drinks
FROM `your_table`
-- 仅需要展开支付方式数组即可
CROSS JOIN UNNEST(payment) AS pay

• 性能提升明显：仅对源表做1次全表扫描，不需要多次UNNEST items数组再做JOIN，避免了不必要的IO和shuffle开销，数据量级越大优势越显著
• 写法简洁易维护：没有冗余的CTE逻辑，所有计算逻辑集中在单段查询中，后续如果要新增其他分类的消费列，只需要加一行对应的子查询即可
• 结果完全匹配：输出和原写法的预期结果完全一致，不会出现笛卡尔积错误或者数据遗漏

补充说明：如果单个用户存在多条同class的items记录（比如多次购买fruit类商品），可以将子查询改为聚合形式避免报错，示例：

(SELECT SUM(spent) FROM UNNEST(items) WHERE class = 'fruit') AS spent_fruit

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