BigQuery中基于关联子查询的LEFT JOIN实现求助

需求与数据结构 #

需要对表T1和T2执行LEFT JOIN，匹配规则为：

• T1.code = T2.code
• T1.t1_date <= T2.valid_date
• 为每个T1记录匹配符合条件的最小valid_date对应的SomeVal，无匹配项则SomeVal为null

表结构 #

T1表：

id      code       t1_date
101     xyz1       2010-10-23
102     xyz1       2023-10-01
103     abc2       2023-09-01

T2表：

code         valid_date      SomeVal      
xyz1         2010-12-31        xxx
xyz1         2999-12-31        yyy 

预期输出 #

id      code       t1_date       SomeVal
101     xyz1       2010-10-23    xxx 
102     xyz1       2023-10-01    yyy
103     abc2       2023-09-01    null

尝试的SQL（未完成） #

SELECT T1.*, T2.SomeVal FROM T1 
LEFT JOIN (SELECT code,valid_date, ROW_NUMBER() over (PARTITION BY code ORDER BY 
           valid_date) as row_num from T2)
ON T1.code = T2.code
AND T1.t1_date <= T2.valid_date 

解决方案 #

方法1：使用QUALIFY子句（BigQuery原生支持） #

这是最简洁的写法，直接在关联后筛选符合条件的最小匹配项：

SELECT 
  T1.id,
  T1.code,
  T1.t1_date,
  T2.SomeVal
FROM T1
LEFT JOIN T2
  ON T1.code = T2.code
  AND T1.t1_date <= T2.valid_date
QUALIFY ROW_NUMBER() OVER (PARTITION BY T1.id ORDER BY T2.valid_date ASC) = 1
ORDER BY T1.id;

逻辑说明：

1. LEFT JOIN 确保没有匹配到T2的T1记录（如id=103）被保留，SomeVal 为null
2. 对每个T1记录（按T1.id分区），将关联到的符合条件的T2记录按valid_date升序排序
3. QUALIFY 筛选出排序后第一条记录（即最小valid_date对应的SomeVal）

方法2：调整子查询逻辑（兼容窗口函数写法） #

如果需要用子查询实现，可以先关联再筛选：

WITH joined_data AS (
  SELECT 
    T1.*,
    T2.SomeVal,
    T2.valid_date,
    ROW_NUMBER() OVER (PARTITION BY T1.id ORDER BY T2.valid_date ASC) AS row_num
  FROM T1
  LEFT JOIN T2
    ON T1.code = T2.code
    AND T1.t1_date <= T2.valid_date
)
SELECT 
  id,
  code,
  t1_date,
  SomeVal
FROM joined_data
WHERE row_num = 1 OR row_num IS NULL
ORDER BY id;

逻辑说明：

1. 先通过LEFT JOIN获取所有符合条件的关联记录
2. 为每个T1记录的关联结果分配行号，按valid_date升序排序
3. 筛选出每个T1记录的第一条结果（row_num=1），或无匹配的记录（row_num IS NULL）

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