如何将含TOP 1的子查询转换为适配BigQuery的Inner Join？

原查询的核心需求是为每个包裹记录匹配其状态为Deleted的最新生命周期记录的create_dt，直接用Inner Join会过滤掉没有对应Deleted记录的包裹，同时如果一个包裹有多条Deleted状态的生命周期记录，会返回重复行。在BigQuery中可以用窗口函数ROW_NUMBER()来实现需求，同时保留原查询的逻辑（即使没有Deleted记录也返回包裹信息，对应字段为NULL）：

转换后的SQL #

SELECT 
  pak.order_id_int, 
  pak.order_number_vc,
  life.create_dt AS package_archive_dt
FROM recipient_package_tbl pak
LEFT JOIN (
  SELECT 
    recipient_package_id_fk_int,
    create_dt,
    ROW_NUMBER() OVER (PARTITION BY recipient_package_id_fk_int ORDER BY lifecycle_id_int DESC) AS rn
  FROM lifecycle_tbl
  WHERE status_vc = 'Deleted'
) life ON pak.recipient_package_id_int = life.recipient_package_id_fk_int AND life.rn = 1

逻辑说明 #

• 子查询中先对lifecycle_tbl按recipient_package_id_fk_int分组，通过ROW_NUMBER()给每个分组内的记录按lifecycle_id_int降序编号，最新的记录会被标记为rn=1。
• 使用LEFT JOIN替代INNER JOIN，确保即使包裹没有对应的Deleted状态生命周期记录，也能保留包裹的基础信息，此时package_archive_dt会显示为NULL，和原查询逻辑一致。
• 最后通过life.rn = 1筛选出每个包裹对应的最新Deleted记录。

内容的提问来源于stack exchange，提问作者Daniel Muñoz López