如何按ID分组对最近时间戳执行rolljoin滚动连接？

嗨，我来帮你搞定这个按ID匹配最近时间的问题～

首先，你之前的代码核心问题是滚动连接的主表搞反了：你用了DT1[DT2, roll=Inf]，这是把DT2作为主表去匹配DT1的数据，而你实际需要的是以DT1为基础，去匹配DT2中对应ID的最近时间记录。

先给你简化一下数据初始化的代码，用lubridate的ymd_hms可以更直接地把字符串转成时间类型：

library(data.table)
library(lubridate)

# 初始化DT1
DT1 <- data.table(
  id = c(7,7,7,3,3,3),
  start_time = ymd_hms(c("2017-11-01 08:37:35","2017-11-01 09:07:44","2017-11-01 09:46:16","2017-11-01 10:32:29","2017-11-01 10:59:25","2017-11-01 13:24:12")),
  cube = c(628,625,469,711,376,628)
)

# 初始化DT2
DT2 <- data.table(
  id = c(7,7,7,3,3,3),
  res_time = ymd_hms(c("2017-11-01 08:35:30","2017-11-01 09:07:48","2017-11-01 09:46:32","2017-11-01 10:31:29","2017-11-01 10:57:25","2017-11-01 13:22:10")),
  res_cube = c(309,625,469,712,375,630)
)

接下来分两种常见需求给你解决方案：

方案1：匹配不晚于start_time的最近res_time（即找start_time之前的最近记录） #

这应该是你原本想实现的效果，正确的滚动连接写法是把DT2作为被连接表，DT1作为主表：

# 设置键：先按id分组，再按时间排序
setkey(DT2, id, res_time)
setkey(DT1, id, start_time)

# 滚动连接：为DT1每行匹配同ID下res_time <= start_time的最近记录
ans1 <- DT2[DT1, roll=Inf]
# 调整列顺序让结果更清晰
ans1 <- ans1[, .(id, start_time, cube, res_time, res_cube)]

运行后你会得到符合预期的结果：

id          start_time cube           res_time res_cube
1:  7 2017-11-01 08:37:35  628 2017-11-01 08:35:30      309
2:  7 2017-11-01 09:07:44  625 2017-11-01 08:35:30      309
3:  7 2017-11-01 09:46:16  469 2017-11-01 09:07:48      625
4:  3 2017-11-01 10:32:29  711 2017-11-01 10:31:29      712
5:  3 2017-11-01 10:59:25  376 2017-11-01 10:57:25      375
6:  3 2017-11-01 13:24:12  628 2017-11-01 13:22:10      630

方案2：匹配与start_time时间差最小的res_time（不管时间先后） #

如果你需要的是绝对时间最接近的记录（哪怕res_time比start_time晚），可以用分组计算时间差的方法：

# 先按ID做笛卡尔积，得到同ID下DT1和DT2的所有组合
# 然后计算时间差的绝对值，取每组中时间差最小的记录
ans2 <- DT1[DT2, on="id", allow.cartesian=TRUE][
  , time_diff := abs(difftime(start_time, res_time, units="secs"))
][
  , .SD[which.min(time_diff)], by=.(id, start_time, cube)
][
  , .(id, start_time, cube, res_time, res_cube)
]

这个方案的结果会把DT1中id=7的第二行（start_time=09:07:44）匹配到DT2中时间仅差4秒的09:07:48记录，结果如下：

id          start_time cube           res_time res_cube
1:  7 2017-11-01 08:37:35  628 2017-11-01 08:35:30      309
2:  7 2017-11-01 09:07:44  625 2017-11-01 09:07:48      625
3:  7 2017-11-01 09:46:16  469 2017-11-01 09:46:32      469
4:  3 2017-11-01 10:32:29  711 2017-11-01 10:31:29      712
5:  3 2017-11-01 10:59:25  376 2017-11-01 10:57:25      375
6:  3 2017-11-01 13:24:12  628 2017-11-01 13:22:10      630

你可以根据实际需求选择对应的方案～

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