在BigQuery中如何左关联表B至表A对应分组最大日期的行？

• 首先标记出表A中每个ID、Name分组下Date_A为最大值的行
• 左关联表B时仅让上述标记的行参与关联，其余行关联表B的字段自动返回NULL，完全匹配需求要求

WITH a_with_max_flag AS (
  SELECT 
    *,
    -- 按ID+Name分组，Date_A倒序排序，排名为1即为当前组最大日期行
    -- 若同组存在多个相同的最大Date_A且需要全部关联表B，将ROW_NUMBER替换为RANK即可
    ROW_NUMBER() OVER(PARTITION BY ID, Name ORDER BY Date_A DESC) AS date_rank
  FROM `你的项目ID.你的数据集名.表A`
)
SELECT 
  a.ID,
  a.Name,
  a.Date_A,
  a.field_x,
  a.field_y,
  a.field_z,
  b.Date_B,
  b.field_A,
  b.field_B
FROM a_with_max_flag a
LEFT JOIN `你的项目ID.你的数据集名.表B` b
  ON a.ID = b.ID 
  AND a.Name = b.Name
  AND a.date_rank = 1 -- 仅最大日期行参与关联

SELECT 
  a.*,
  b.Date_B,
  b.field_A,
  b.field_B
FROM `你的项目ID.你的数据集名.表A` a
LEFT JOIN `你的项目ID.你的数据集名.表B` b
  ON a.ID = b.ID 
  AND a.Name = b.Name
  AND a.Date_A = (SELECT MAX(Date_A) FROM `你的项目ID.你的数据集名.表A` WHERE ID = a.ID AND Name = a.Name)

• 推荐使用窗口函数的方案，BigQuery对窗口函数有专门的性能优化，数据量大时执行效率远高于相关子查询方案
• 关联条件中直接增加date_rank = 1的过滤，而不是关联后再用WHERE过滤，可以减少参与关联的数据量，进一步提升执行效率

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