如何在BigQuery中实现SQL Server OUTER APPLY的等效查询

原MS SQL查询的核心逻辑是：为tableA的每一行，匹配tableB中日期早于当前行日期的最新一条记录，即使没有匹配记录也保留tableA的行。以下是两种在BigQuery中实现该逻辑的方法：

方法一：使用LEFT LATERAL JOIN（最贴近原语法逻辑） #

BigQuery支持LEFT LATERAL JOIN，功能等价于MS SQL的OUTER APPLY，结合LIMIT 1替代TOP 1即可实现相同效果：

SELECT *
FROM tableA
LEFT LATERAL JOIN (
  SELECT *
  FROM tableB
  WHERE tableB.dateTimeColumn < tableA.dateTimeColumn
  ORDER BY tableB.dateTimeColumn DESC
  LIMIT 1
) AS b

该写法会为tableA的每一行单独执行子查询，筛选出符合条件的tableB最新记录；无匹配时tableB的所有列返回NULL，完全保留tableA的行数据。

方法二：使用窗口函数+LEFT JOIN（适合大数据量场景） #

如果处理的数据集较大，窗口函数的方式可能更高效：

WITH ranked_b AS (
  SELECT 
    a.*,
    b.*,
    ROW_NUMBER() OVER (
      PARTITION BY a.id -- 替换为tableA的唯一主键或唯一标识列
      ORDER BY b.dateTimeColumn DESC
    ) AS rn
  FROM tableA a
  LEFT JOIN tableB b 
    ON b.dateTimeColumn < a.dateTimeColumn
)
SELECT * EXCEPT(rn)
FROM ranked_b
WHERE rn = 1 OR rn IS NULL

说明： #

1. 先通过LEFT JOIN关联所有符合日期条件的tableA和tableB记录；
2. 用ROW_NUMBER()按tableA的唯一标识分区，对每个分区内的tableB记录按日期倒序排名；
3. 最后筛选排名为1的记录（即每个tableA行对应的最新tableB记录），同时保留rn为NULL的行（无匹配的tableA行）。

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