BigQuery中如何用子查询WHERE子句引用外部列匹配最新行

数据老陈

2026-8-18

在BigQuery中实现PostgreSQL LATERAL JOIN的等价逻辑

BigQuery不支持PostgreSQL的LATERAL JOIN，但可以通过以下两种方式实现你需要的「为主查询每行筛选子表最新匹配行」的逻辑：

方法1：使用`QUALIFY`子句（推荐，简洁高效）

利用窗口函数对table_b预分组排序，筛选出每个id下的最新符合条件记录，再与主表关联：

SELECT
  a.*,
  b.score,
  CONCAT('AB', b.id) AS b_id
FROM
  `your-project.dataset.table_a` AS a
LEFT JOIN (
  SELECT
    id,
    score,
    date,
    -- 按id分组，日期倒序排名，仅保留最新的一条
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
  FROM
    `your-project.dataset.table_b`
) AS b
ON b.id = a.company_id
AND b.date < a.date
AND b.rn = 1
WHERE
  a.id LIKE 'AB%'
ORDER BY
  a.createdAt DESC

方法2：用`ARRAY_AGG` + `UNNEST`模拟LATERAL行为

这种方式和你PostgreSQL的写法逻辑更贴近，对主表的每一行，动态筛选子表符合条件的记录并取最新一条：

SELECT
  a.*,
  b.score,
  CONCAT('AB', b.id) AS b_id
FROM
  `your-project.dataset.table_a` AS a
LEFT JOIN UNNEST(
  -- 针对当前主表行，筛选table_b中匹配id且日期更早的记录，取最新一条
  ARRAY(
    SELECT AS STRUCT score, id
    FROM `your-project.dataset.table_b` AS b
    WHERE b.id = a.company_id
      AND b.date < a.date
    ORDER BY b.date DESC
    LIMIT 1
  )
) AS b
WHERE
  a.id LIKE 'AB%'
ORDER BY
  a.createdAt DESC