如何按ID和日期合并数据集（限定1周范围并匹配最早记录）及是否用Left Join？

关于Left Join的疑问 #

是的，若要优先保留Set A的所有记录（无论Set B中是否存在匹配项），必须使用Left Join（左连接）。这样即使Set B中没有符合条件的记录，Set A的行也会被完整保留，对应Set B的字段会显示为NULL。

实现代码 #

以下提供两种常用工具的实现方案：

1. SQL实现 #

先对Set B做预处理，筛选出每个ID下处于Set A对应日期1周范围内的最早记录，再与Set A做左连接：

-- 预处理Set B：筛选符合日期范围的记录，并标记每个ID下的最早条目
WITH filtered_b AS (
    SELECT 
        b.ID,
        b.Date AS b_date,
        ROW_NUMBER() OVER (PARTITION BY b.ID ORDER BY b.Date) AS rn
    FROM 
        SetB b
    JOIN 
        SetA a ON b.ID = a.ID
    WHERE 
        -- 日期范围：Set B日期在Set A日期之后的0-7天内（需根据数据库调整日期函数）
        DATEDIFF(day, a.Date, b.Date) BETWEEN 0 AND 7
)
-- 左连接保留所有Set A记录，仅匹配Set B中最早的符合条件的条目
SELECT 
    a.ID,
    a.Date AS a_date,
    fb.b_date AS b_date
FROM 
    SetA a
LEFT JOIN 
    filtered_b fb ON a.ID = fb.ID AND fb.rn = 1;

注：不同数据库的日期计算函数有差异，比如PostgreSQL可替换为b.Date <= a.Date + INTERVAL '7 days' AND b.Date >= a.Date，需根据实际环境调整。

2. Python Pandas实现 #

先转换日期格式，筛选符合范围的记录后取最早条目，再与Set A左连接：

import pandas as pd

# 示例数据
set_a = pd.DataFrame({
    'ID': [1, 2],
    'Date': ['10-21-2021', '03-03-2020']
})
set_b = pd.DataFrame({
    'ID': [1, 2, 1],
    'Date': ['10-22-2021', '03-04-2020', '10-23-2021']
})

# 转换日期为datetime类型
set_a['Date'] = pd.to_datetime(set_a['Date'], format='%m-%d-%Y')
set_b['Date'] = pd.to_datetime(set_b['Date'], format='%m-%d-%Y')

# 匹配ID并筛选日期范围在0-7天内的记录
merged_temp = pd.merge(set_a, set_b, on='ID', suffixes=('_a', '_b'))
merged_temp['date_diff'] = (merged_temp['Date_b'] - merged_temp['Date_a']).dt.days
filtered_temp = merged_temp[(merged_temp['date_diff'] >= 0) & (merged_temp['date_diff'] <=7)]

# 按ID和Set A日期分组，取Set B中最早的日期记录
filtered_b = filtered_temp.sort_values('Date_b').groupby(['ID', 'Date_a']).first().reset_index()

# 左连接保留所有Set A记录
final_result = pd.merge(set_a, filtered_b[['ID', 'Date_a', 'Date_b']], 
                        left_on=['ID', 'Date'], right_on=['ID', 'Date_a'], 
                        how='left').drop(columns='Date_a')

print(final_result)

运行结果：

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