如何基于时间区间连接两个数据表？避免结果出现重复行

SQL实现 #

场景1：无重叠时间区间 #

如果Table1的时间区间连续且无重叠，直接用区间条件内连接即可，注意避免端点重复匹配：

SELECT 
    t2.event_id,
    t2.timestamp,
    t1.id AS interval_id,
    t1.start_time,
    t1.end_time,
    t1.value1,
    t2.value2
FROM Table2 t2
INNER JOIN Table1 t1 
    ON t2.timestamp >= t1.start_time 
    AND t2.timestamp < t1.end_time;

用>=和<替代BETWEEN，可以防止时间点刚好等于区间end_time时，同时匹配当前区间和下一个区间（若下一个区间start_time等于当前end_time），避免产生重复记录。

场景2：存在重叠时间区间 #

如果Table1有重叠区间，需要指定匹配优先级，用窗口函数过滤出唯一匹配项：

WITH joined_data AS (
    SELECT 
        t2.event_id,
        t2.timestamp,
        t1.id AS interval_id,
        t1.start_time,
        t1.end_time,
        t1.value1,
        t2.value2,
        -- 按需求设置排序规则，比如优先匹配最新启动的区间
        ROW_NUMBER() OVER (PARTITION BY t2.event_id ORDER BY t1.start_time DESC) AS rn
    FROM Table2 t2
    INNER JOIN Table1 t1 
        ON t2.timestamp >= t1.start_time 
        AND t2.timestamp < t1.end_time
)
SELECT event_id, timestamp, interval_id, start_time, end_time, value1, value2
FROM joined_data
WHERE rn = 1;

PARTITION BY t2.event_id会给每个Table2记录的所有匹配结果编号，取rn=1即可得到唯一匹配项，排序规则可根据业务调整（比如按区间结束时间、区间长度等）。

Python Pandas实现（解决merge_asof重复问题） #

merge_asof要求左右表必须按时间列排序，且默认匹配最近的前向时间点，需结合筛选条件确保区间匹配：

import pandas as pd

# 第一步：对两张表按时间列排序
table1_sorted = table1.sort_values("start_time").reset_index(drop=True)
table2_sorted = table2.sort_values("timestamp").reset_index(drop=True)

# 第二步：用merge_asof匹配最大的start_time <= timestamp的区间
merged = pd.merge_asof(
    table2_sorted,
    table1_sorted,
    left_on="timestamp",
    right_on="start_time",
    direction="backward"
)

# 第三步：筛选出timestamp <= end_time的有效匹配（排除start_time符合但end_time早于时间点的情况）
final_result = merged[merged["timestamp"] <= merged["end_time"]]

如果存在重叠区间，需额外增加去重步骤：

# 给每个Table2记录的匹配结果按优先级排名
final_result["rank"] = final_result.groupby("event_id")["start_time"].rank(ascending=False, method="first")
# 取排名第一的唯一匹配项
final_result = final_result[final_result["rank"] == 1].drop("rank", axis=1)

常见问题排查 #

• 之前INNER JOIN出现重复：大概率是Table1存在重叠区间，导致单个Table2时间点匹配到多个区间；
• merge_asof出现重复：未提前对两张表按时间列排序，或者未添加end_time筛选条件，导致匹配到不符合区间要求的记录。

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