R语言中按日期范围匹配两个非等长数据框的方案咨询

针对50万+行的批量匹配场景，推荐使用data.table的非等值连接实现，运算效率远高于常规逐行匹配或笛卡尔积方案，普通配置设备可在几秒内完成匹配运算。

步骤1：安装加载依赖包 #

使用data.table做高效连接，lubridate处理日期偏移避免月末日期计算错误：

# 首次运行需先安装
install.packages(c("data.table", "lubridate"))
# 加载包
library(data.table)
library(lubridate)

步骤2：数据预处理 #

# 生成示例数据（使用你的原始数据可跳过这部分）
id = rep("A",10)
date = seq(as.Date("2000/1/1"), by = "day", length.out = 10)
product = c("E1","E1","E2","E3","E3","E4","E1","E1","E5","E5")
man = c("PS","PS","PS","DDR","DDR","FFR","PS","PS","BA","BA")
weight = c(1505,300,259,231,140,150,300,112,203,2200) 
data1 = data.frame(id,date,product,man,weight)

id2 = rep("A",10)
date2 = sample(seq(as.Date("2000/1/10"), by = "day", length.out = 10),10)
product2 = c("E3","E3","E1","E1","E1","E1","E5","E4","E5","E2")
man2 = c("DDR","DDR","PS","PS","PS","PS","BA","FFR","BA","PS")
weight2 = c(259,295,260,240,135,145,250,110,2000,210) 
data2 = data.frame(id2,date2,product2,man2,weight2)

# 统一核验表列名与申报表对应
setnames(data2, old = c("id2","date2","product2","man2","weight2"), new = c("id","date2","product","man","weight2"))
# 转换为data.table格式提升运算效率
setDT(data1)
setDT(data2)
# 生成申报日期的1个月区间上限，%m+%可避免1月31日加1个月报错的问题
data1[, date_end := date %m+% months(1)]

步骤3：执行多条件非等值连接 #

同时满足三个匹配规则：id完全一致、产品编码和生产商编码完全一致、核验日期落在申报日期的1个月范围内：

res = data2[data1, on = .(id, product, man, date2 >= date, date2 <= date_end), nomatch = NA]

参数说明：nomatch = NA表示未匹配到的记录保留NA，符合业务需求。

步骤4：整理输出结果 #

按要求生成指定字段：

final_res = res[, .(
  id = id,
  日期范围 = paste(date, "至", date_end),
  产品编码 = product,
  生产商编码 = man,
  申报重量 = i.weight,
  核验重量 = weight2,
  重量求和结果 = i.weight + weight2
)]

# 查看结果
head(final_res)

可选优化 #

如果一条申报记录匹配到多条核验记录，需要按申报维度聚合核验总重量，可新增聚合步骤：

agg_res = final_res[, .(
  核验总重量 = sum(核验重量, na.rm = T),
  重量求和结果 = sum(重量求和结果, na.rm = T)
), by = .(id, 日期范围, 产品编码, 生产商编码, 申报重量)]

内容的提问来源于stack exchange，提问作者Homer Jay Simpson