BigQuery中如何不使用自连接实现前置值关联查询

1 临时查询场景 #

1.1 链表为严格线性递增（id顺序对应业务先后顺序，previous_id为上一条的id） #

直接用LAG分析函数即可，仅需扫描一次表，性能远高于自连接：

SELECT 
  id,
  `Previous id` AS Previous_id,
  Value,
  LAG(Value, 1, '-') OVER (ORDER BY id) AS Previous_Value
FROM `Table`

1.2 链表存在分支（多个行的previous_id指向同一个id） #

提前构建id-value映射小维度表，定期同步更新（同步频率可根据业务需求调整），查询时关联小维度表即可，比关联原大表性能高30%~80%：

• 维度表结构仅2列：id、value，无冗余字段
• 维度表id设为主键，关联时性能极高

-- 先定时同步维度表，再执行查询
SELECT 
  t1.*,
  IFNULL(t2.value, '-') AS Previous_Value
FROM `Table` t1
LEFT JOIN id_value_dim t2 ON t1.`Previous id` = t2.id

2 定期产出结果表场景 #

直接在ETL流程中提前计算Previous_Value字段写入结果表，下游直接读取已固化的字段，无需每次查询时做关联计算：

• 新增/更新数据时用触发器自动回填Previous_Value
• 或定时跑批任务全量刷新该字段

3 原自连接方案的优化补充 #

如果必须保留自连接写法，给id字段设主键、Previous id字段建普通索引，数据量越大索引带来的性能提升越明显。

内容的提问来源于stack exchange，提问作者Víctor