如何在BigQuery中基于重叠区间连接两张表？Postgres语句转译求助

原PostgreSQL语句 #

PostgreSQL中通过tstzrange和&&操作符实现基于时间区间重叠的表连接：

-- postgres
SELECT *
FROM table_a
INNER JOIN table_b
ON tstzrange(table_a.start, table_a.end) && tstzrange(table_b.start, table_b.end)

问题尝试写法 #

你拆分的四种区间重叠场景逻辑冗余，且因区间开闭性判断错误，导致遗漏部分有效时间窗口：

INNER JOIN table_b
ON 
-- case 1
a.start <= b.start AND b.end <= a.end
OR
-- case 2
a.start <= b.start AND b.start <= a.end
OR 
-- case 3
b.start <= a.start AND a.end <= b.end
OR
-- case 4
b.start <= a.start AND a.start <= b.end

BigQuery正确实现方式 #

PostgreSQL的tstzrange默认是左闭右开区间（[start, end)），&&操作符用于判断两个区间存在非空交集。对应BigQuery的简化判断逻辑如下，既能覆盖所有重叠场景，又与PostgreSQL的行为保持一致：

-- BigQuery
SELECT *
FROM table_a a
INNER JOIN table_b b
ON a.start < b.end 
AND b.start < a.end

逻辑说明 #

• 核心判断逻辑：只要A区间的起始早于B区间的结束，且B区间的起始早于A区间的结束，即可判定两个区间存在重叠。
• 用<比较结束时间，避免将A.end = B.start这类相邻无交集的区间误判为重叠，严格匹配PostgreSQL的&&操作符行为。

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