如何在R data.table非等值连接中关联无匹配日期至最近区间

解决方案思路 #

1. 先执行常规非等值连接，保留原始日期和区间信息，标记出无匹配的行。
2. 为区间表添加区间中点字段，用于计算目标日期到区间的距离。
3. 针对无匹配的行，按id分组，找到同id下与目标日期距离最近的区间，提取对应权重值。
4. 将补全的值合并回初始连接结果。

完整代码实现 #

library(data.table)

# 示例数据
# 含单个日期的data.table
id <-  c("xxx","xxx","xxx","yyy","yyy","yyy","zzz","zzz","zzz")
date <- as.Date(c("2019-02-01","2020-05-01","2021-02-01","2020-01-01","2020-07-01","2020-08-01","2021-03-01","2022-10-01","2023-11-16"))
single_date_dt <- data.table(id,date)

# 含日期区间的data.table
id <- c("xxx","xxx","yyy","zzz","zzz")
weight_kg <- c(10,13,20,45,65)
start_date <- as.Date(c("2019-01-01","2021-01-01","2020-01-01","2021-01-01","2023-01-01"))
end_date <- as.Date(c("2019-12-31","2021-12-31","2021-01-01","2021-12-31","2023-12-31"))
range_date_dt <- data.table(id,weight_kg,start_date,end_date) 

# 1. 初始非等值连接，保留原始日期和区间信息
dates_joined <- range_date_dt[single_date_dt, 
                              on = .(id, start_date <= date, end_date >= date),
                              .(id, date = i.date, weight_kg, original_start = x.start_date, original_end = x.end_date),
                              nomatch = NA]

# 2. 为区间表添加区间中点
range_date_dt[, mid_date := start_date + (end_date - start_date)/2]

# 3. 筛选无匹配的行，补全最近区间的权重
na_rows <- dates_joined[is.na(weight_kg)]
filled_rows <- na_rows[, 
                       range_date_dt[.SD, on = .(id), 
                                     .(date = i.date, weight_kg, distance = abs(mid_date - i.date)),
                                     by = .EACHI][which.min(distance)], 
                       by = .(id, date)]

# 4. 将补全值合并回结果表
dates_joined[filled_rows, on = .(id, date), weight_kg := i.weight_kg]

# 查看最终结果
print(dates_joined)

结果验证 #

执行后，dates_joined中的NA值会被正确替换：

• 2020-05-01（xxx）关联到权重10（最近区间2019-01-01至2019-12-31）
• 2022-10-01（zzz）关联到权重65（最近区间2023-01-01至2023-12-31）

性能说明 #

• 仅针对无匹配的5000行进行补全计算，不会对百万级已匹配行造成性能损耗。
• 使用data.table的by=.EACHI和which.min实现高效分组计算，处理速度快。

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