R中如何基于多条件合并两个data.table：匹配日期落在时间区间的记录

首先需要将两个表的日期字段统一转换为Date类型避免字符串比较错误，再用data.table自带的非等值左连接即可完成按时间范围匹配的需求，完整可运行代码如下：

library(data.table)

# 示例数据构造
dt1 <- data.table (ID = 1:10,
                  exit = c("31/12/2010", "01/01/2021", "30/09/2010", "31/12/2015", "30/09/2010","31/10/2018", "01/02/2016", "01/05/2015", "01/09/2013", "01/01/2016"))
dt2 <- data.table (ID = c(1,2,2,2,3,5,6,6,7,8,8,9,10),
                   valid_from = c("01/01/2010", "01/01/2012", "01/01/2013", "01/12/2017", "01/05/2010", "01/04/2010", "01/05/2014", "01/11/2016", "01/01/2016", "15/04/2013", "01/01/2015", "15/02/2010", "01/04/2012"),
                   valid_until = c("01/01/2021", "31/12/2012", "30/11/2017", "01/01/2021", "01/01/2021", "01/01/2021", "31/10/2016", "01/01/2021", "01/01/2021", "31/12/2014", "01/05/2015", "01/01/2013", "01/01/2021"),
                   text1 = c("a", "a", "b", "c", "b", "b", "c", "a", "a", "b", "a", "c", "a"),
                   text2 = c("I", "I", "II", "I", "III", "I", "II", "III", "I", "II", "II", "I", "III" ))

# 1. 转换日期列格式，原数据为日/月/年，指定format参数为"%d/%m/%Y"
dt1[, exit := as.Date(exit, format = "%d/%m/%Y")]
dt2[, `:=`(
  valid_from = as.Date(valid_from, format = "%d/%m/%Y"),
  valid_until = as.Date(valid_until, format = "%d/%m/%Y")
)]

# 2. 非等值左连接：匹配ID相同，且退出日期落在有效时间段内的记录
result <- dt2[dt1, on = .(ID, valid_from <= exit, valid_until >= exit)]

# 3. 整理输出字段，删除多余列，得到最终结果
result <- result[, .(ID, exit = valid_from, text1, text2)]

逻辑说明 #

• 连接参数on = .(ID, valid_from <= exit, valid_until >= exit)的含义是：先匹配ID相同的记录，再校验dt1.exit落在dt2.valid_from和dt2.valid_until的闭区间内
• 采用dt2[dt1]的写法是左连接规则，会完整保留dt1的所有行，dt2中没有匹配到的属性字段会自动填充NA，和你需要的ID4返回NA的要求一致
• 如果你的实际业务中dt2同一ID存在重叠的有效时间区间，匹配会返回多条记录，可在连接时增加mult = "first"或mult = "last"参数，指定取匹配到的第一条还是最后一条记录

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