如何将PostgreSQL的CROSS JOIN LATERAL语句改写为BigQuery兼容语句？

原语句核心逻辑是：针对data表中每一行（作为left_table），找出同source_ip且会话时间与当前行有重叠的所有会话ID，用逗号拼接成字符串。BigQuery不支持CROSS JOIN LATERAL，可以用以下两种方式改写：

方案一：关联子查询（最贴近原语句逻辑） #

直接把原LATERAL子查询放到SELECT列表中，BigQuery会自动对每一行执行该子查询，返回对应结果：

SELECT 
  left_table.*,
  (
    SELECT STRING_AGG(right_table.session_id, ',')
    FROM data right_table 
    WHERE right_table.source_ip = left_table.source_ip 
      AND (
        (right_table.session_start_time >= left_table.session_start_time 
         AND right_table.session_start_time <= left_table.session_end_time)
        OR 
        (right_table.session_end_time >= left_table.session_start_time 
         AND right_table.session_end_time <= left_table.session_end_time)
      )
  ) AS session_ids
FROM data left_table

注：原子查询中的GROUP BY right_table.source_ip可以直接去掉——因为WHERE条件已经限定了source_ip和当前行一致，分组后只有一组，不影响结果，去掉后执行效率更高。

方案二：自连接+聚合函数 #

先通过自连接匹配所有符合条件的行，再按原表的所有列分组聚合：

SELECT 
  left_table.*,
  STRING_AGG(right_table.session_id, ',') AS session_ids
FROM data left_table
LEFT JOIN data right_table
  ON right_table.source_ip = left_table.source_ip 
  AND (
    (right_table.session_start_time >= left_table.session_start_time 
     AND right_table.session_start_time <= left_table.session_end_time)
    OR 
    (right_table.session_end_time >= left_table.session_start_time 
     AND right_table.session_end_time <= left_table.session_end_time)
  )
GROUP BY left_table.source_ip, left_table.session_start_time, left_table.session_end_time -- 这里要列出left_table中所有出现在SELECT里的非聚合列

注：GROUP BY子句必须包含left_table中所有未被聚合的列，确保每一行原数据都能正确分组。

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