如何基于时间范围关联两个数据集？避免重复行的实现方案

问题背景 #

需要将Table2的Timestamp匹配到Table1的start_time与end_time时间范围内完成关联，得到指定格式的结果。尝试使用Python的pandas.merge_asof时出现右表数据重复的问题，inner join也未达到预期效果，希望通过SQL等方式实现无重复的时间范围关联。

Table1数据 #

start_time         end_time          ID  Ident
01/01/2022 17:56   01/01/2022 17:59  1   1A
01/01/2022 18:36   01/01/2022 18:40  2   1C
01/01/2022 19:48   01/01/2022 19:50  1   2D
01/01/2022 20:12   01/01/2022 20:14  2   4F
01/01/2022 21:47   01/01/2022 21:50  3   7R
01/01/2022 22:56   01/01/2022 22:59  5   2E
01/01/2022 23:57   01/01/2022 23:59  6   3E

Table2数据 #

Timestamp         rate
01/01/2022 17:57  5
01/01/2022 19:49  5  
01/01/2022 20:14  5
01/01/2022 21:47  5
01/01/2022 23:58  5

期望结果 #

start_time         end_time          ID Timestamp         rate
01/01/2022 17:56   01/01/2022 17:59  1  01/01/2022 17:57  5
01/01/2022 18:36   01/01/2022 18:40  2  null              null
01/01/2022 19:48   01/01/2022 19:50  1  01/01/2022 19:49  5
01/01/2022 20:12   01/01/2022 20:14  2  01/01/2022 20:14  5
01/01/2022 21:47   01/01/2022 21:50  3  01/01/2022 21:47  5
01/01/2022 22:56   01/01/2022 22:59  5  null              null
01/01/2022 23:57   01/01/2022 23:59  6  01/01/2022 23:58  5

SQL解决方案 #

基础LEFT JOIN实现（单匹配场景） #

如果Table2中每个时间区间最多对应一条记录，直接用LEFT JOIN加时间范围条件即可，保留Table1所有行并匹配符合条件的Table2数据：

SELECT 
    t1.start_time,
    t1.end_time,
    t1.ID,
    t2.Timestamp,
    t2.rate
FROM Table1 t1
LEFT JOIN Table2 t2 
    ON t2.Timestamp BETWEEN t1.start_time AND t1.end_time;

注意：需确保所有时间字段为datetime类型，避免字符串比较导致的逻辑错误。

多匹配场景去重（取唯一结果） #

如果同一时间区间内有多个Table2记录，可通过LATERAL JOIN或窗口函数确保每个Table1行只匹配一条结果（以取最新时间为例）：

PostgreSQL/SQL Server写法（LATERAL JOIN）

SELECT 
    t1.start_time,
    t1.end_time,
    t1.ID,
    t2.Timestamp,
    t2.rate
FROM Table1 t1
LEFT JOIN LATERAL (
    SELECT Timestamp, rate
    FROM Table2
    WHERE Timestamp BETWEEN t1.start_time AND t1.end_time
    ORDER BY Timestamp DESC -- 按时间倒序，取最新的一条
    LIMIT 1
) t2 ON true;

通用窗口函数写法

WITH ranked_table2 AS (
    SELECT 
        Timestamp,
        rate,
        -- 给每个匹配Table1区间的记录分组排序
        ROW_NUMBER() OVER (
            PARTITION BY (SELECT 1 WHERE Timestamp BETWEEN t1.start_time AND t1.end_time) 
            ORDER BY Timestamp DESC
        ) AS rn
    FROM Table2
)
SELECT 
    t1.start_time,
    t1.end_time,
    t1.ID,
    rt2.Timestamp,
    rt2.rate
FROM Table1 t1
LEFT JOIN ranked_table2 rt2 
    ON rt2.Timestamp BETWEEN t1.start_time AND t1.end_time
    AND rt2.rn = 1;

Pandas解决方案（修正重复问题） #

merge_asof适用于最近时间匹配而非区间匹配，改用以下方式实现精准区间关联：

import pandas as pd

# 读取并转换时间数据
table1 = pd.DataFrame({
    'start_time': pd.to_datetime(['01/01/2022 17:56', '01/01/2022 18:36', '01/01/2022 19:48', '01/01/2022 20:12', '01/01/2022 21:47', '01/01/2022 22:56', '01/01/2022 23:57']),
    'end_time': pd.to_datetime(['01/01/2022 17:59', '01/01/2022 18:40', '01/01/2022 19:50', '01/01/2022 20:14', '01/01/2022 21:50', '01/01/2022 22:59', '01/01/2022 23:59']),
    'ID': [1,2,1,2,3,5,6],
    'Ident': ['1A','1C','2D','4F','7R','2E','3E']
})

table2 = pd.DataFrame({
    'Timestamp': pd.to_datetime(['01/01/2022 17:57', '01/01/2022 19:49', '01/01/2022 20:14', '01/01/2022 21:47', '01/01/2022 23:58']),
    'rate': [5,5,5,5,5]
})

# 定义区间匹配函数
def match_time_range(row):
    mask = (table2['Timestamp'] >= row['start_time']) & (table2['Timestamp'] <= row['end_time'])
    matches = table2[mask]
    if not matches.empty:
        return matches.iloc[0]  # 取第一个匹配结果，可按需改为排序后取值
    return pd.Series([pd.NaT, None], index=['Timestamp', 'rate'])

# 执行匹配并整理结果
result = table1.join(table1.apply(match_time_range, axis=1))
result = result[['start_time', 'end_time', 'ID', 'Timestamp', 'rate']]

# 格式化时间显示（可选）
result['start_time'] = result['start_time'].dt.strftime('%m/%d/%Y %H:%M')
result['end_time'] = result['end_time'].dt.strftime('%m/%d/%Y %H:%M')
result['Timestamp'] = result['Timestamp'].dt.strftime('%m/%d/%Y %H:%M').where(result['Timestamp'].notna(), 'null')
result['rate'] = result['rate'].astype(str).where(result['rate'].notna(), 'null')

print(result)

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