如何用BigQuery SQL实现满足时间条件的行级内连接等效操作？

你的需求是保留table1所有行，同时加入table2中timestamp落在table1任意行timestamp与endtime区间内的记录，最终按行合并而非列连接。可以用以下方式实现：

核心思路 #

1. 先筛选出table2中符合时间区间条件的记录；
2. 用UNION ALL将table1全量记录和筛选后的table2记录合并，确保字段对齐。

完整示例代码 #

WITH table_a AS (
  -- 模拟table1，补全table2有的字段为NULL
  SELECT 'x' AS event, 1 AS timestamp, 5 AS endtime, 'a' AS field1, NULL AS field2
  UNION ALL SELECT 'x', 100, 200, 'b', NULL
),
table_b AS (
  -- 模拟table2，补全table1有的字段为NULL
  SELECT 'y' AS event, 2 AS timestamp, NULL AS endtime, NULL AS field1, 'm' AS field2
  UNION ALL SELECT 'y', 25, NULL, NULL, 'n'
  UNION ALL SELECT 'y', 150, NULL, NULL, 'o'
),
-- 筛选table2中落在table1时间区间内的记录，去重避免重复匹配
filtered_b AS (
  SELECT DISTINCT b.*
  FROM table_a a
  JOIN table_b b 
    ON b.timestamp BETWEEN a.timestamp AND a.endtime
)
-- 合并两行数据集
SELECT * FROM table_a
UNION ALL
SELECT * FROM filtered_b
ORDER BY timestamp; -- 可选：按时间排序，匹配示例输出的顺序

关键语法说明 #

• BETWEEN：快速判断时间是否在区间内，等价于b.timestamp >= a.timestamp AND b.timestamp <= a.endtime，根据实际需求选择写法；
• UNION ALL：实现行级合并，保留所有符合条件的行（如果不需要重复行，可在筛选时用DISTINCT或替换为UNION）；
• DISTINCT：防止table2同一条记录匹配多个table1区间时重复出现在结果中。

字段对齐注意事项 #

两张表存在部分共同字段，合并时必须保证字段的数量、顺序、数据类型完全一致，缺失的字段用NULL填充即可。

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