Google BigQuery使用WITH子句进行表关联时出现随机结果问题问询

问题原因 #

• 第一是代码存在笔误：你第三次左关联的表错误写为prepExport2，未和前两次关联的pre_final表（你代码中别名table）保持一致，同时关联条件写为c.pom_cnt = 3而非预期的c.rating = 3，会直接导致关联逻辑错误，返回不符合预期的随机结果。
• 第二是rating字段生成逻辑不稳定：如果修正笔误后仍然出现随机结果，说明你在WITH子句中生成rating排名时，使用的排序键（如订单生成时间、订单金额等）存在重复值，BigQuery对重复排序键的行不会固定返回顺序，每次执行时重复排序键对应的rating值会随机分配，最终关联出来的结果就会随机变化。
• 第三是SELECT子句字段来源不明确：如果你在查询返回字段时没有明确指定字段所属的表别名，例如直接写product而不是a.product AS product_1, b.product AS product_2, c.product AS product_3，在多个关联表存在同名字段的情况下，BigQuery会随机取第一个匹配的字段值，也会导致结果随机。

更简洁的替代实现方案 #

不需要多次自关联，直接使用BigQuery的PIVOT函数即可实现行转列需求，逻辑更清晰也更不容易出错，示例代码如下：

WITH pre_final AS (
  -- 此处替换为你原有生成pre_final表的逻辑
)
SELECT * 
FROM pre_final
PIVOT (
  -- 因为email+rating唯一，此处用MAX/MIN等聚合函数都可以
  MAX(product) AS top_product, MAX(order_id) AS top_order_id
  FOR rating IN (1 AS first, 2 AS second, 3 AS third)
)

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