如何在BigQuery中实现MSSQL含关联子查询的查询逻辑？

原MSSQL查询逻辑 #

需要实现的查询逻辑如下：当COL1为偶数时返回COL2；当COL1为奇数时，从ANOTHER_TABLE中找出与当前行COL2匹配、且COL9小于当前行COL3的最新（COL9最大）记录的COL99值：

SELECT 
  COL1,
  COL2, COL3,
  CASE
    WHEN ( COL1 % 2 ) = 0 THEN COL2
    ELSE (SELECT TOP 1 COL99 FROM ANOTHER_TABLE AS AT WHERE AT.COL8 = T.COL2 AND AT.COL9 < T.COL3 ORDER BY AT.COL9 DESC)
  END AS COL4

FROM TABLE AS T

初次尝试的问题 #

直接将MSSQL的TOP 1替换为BigQuery的LIMIT 1后，触发以下错误：

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

原因是BigQuery不支持这种每行执行的关联子查询，需要转换为JOIN形式。

二次尝试的问题 #

改用LEFT JOIN结合窗口函数时，由于AT.COL9 < T.COL3的条件，返回行数超出预期，且无法直接通过ROW_NUMBER() OVER(PARTITION BY COL8)筛选出符合条件的最新记录：

SELECT 
  COL1,
  COL2, COL3,
  CASE
    WHEN ( COL1 % 2 ) = 0 THEN COL2
    ELSE AT.COL99
  END AS COL4

FROM PROJECT.DATASET.TABLE AS T
LEFT JOIN (
  SELECT * FROM (
    SELECT 
       COL99,
       COL8,
       COL9,
       ROW_NUMBER() OVER (PARITION BY COL8 ORDER BY COL9 DESC) AS rn
  ) AS TMP
  /*WHERE TMP.rn = 1*/
) AS AT
ON AT.COL8 = T.COL2
AND AT.COL9 < T.COL3

示例数据 #

TABLE AS T #

ANOTHER TABLE AS AT #

预期输出 #

正确解决方案 #

使用LEFT JOIN LATERAL（推荐，与原逻辑完全一致） #

BigQuery支持LATERAL JOIN，可以直接模拟原MSSQL的关联子查询逻辑，既简洁又符合需求：

SELECT
  T.COL1,
  T.COL2,
  T.COL3,
  CASE
    WHEN T.COL1 % 2 = 0 THEN T.COL2
    ELSE AT.COL99
  END AS COL4
FROM PROJECT.DATASET.TABLE AS T
LEFT JOIN LATERAL (
  SELECT COL99
  FROM PROJECT.DATASET.ANOTHER_TABLE AS AT
  WHERE AT.COL8 = T.COL2
    AND AT.COL9 < T.COL3
  ORDER BY AT.COL9 DESC
  LIMIT 1
) AS AT ON TRUE

窗口函数替代方案 #

如果不使用LATERAL JOIN，可以通过关联后再用窗口函数筛选符合条件的最新记录：

WITH joined_data AS (
  SELECT
    T.COL1,
    T.COL2,
    T.COL3,
    AT.COL99,
    ROW_NUMBER() OVER (
      PARTITION BY T.COL1, T.COL2, T.COL3
      ORDER BY AT.COL9 DESC
    ) AS rn
  FROM PROJECT.DATASET.TABLE AS T
  LEFT JOIN PROJECT.DATASET.ANOTHER_TABLE AS AT
    ON AT.COL8 = T.COL2
    AND AT.COL9 < T.COL3
)
SELECT
  COL1,
  COL2,
  COL3,
  CASE
    WHEN COL1 % 2 = 0 THEN COL2
    ELSE COL99
  END AS COL4
FROM joined_data
WHERE rn = 1

说明 #

• LEFT JOIN LATERAL会为TABLE中的每一行执行一次子查询，返回符合条件的单条记录，完全匹配原MSSQL的TOP 1逻辑。
• 窗口函数方案通过先关联所有符合条件的记录，再为每个TABLE的行筛选出COL9最大的记录，最终得到预期结果。

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