如何用非等值日期连接为R data.table添加查找列？

需求：给主表dt1新增一列LookupDt，取值为查找表dtLookup中大于等于当前行日期的最小日期。

示例数据 #

library(data.table)

# 主表dt1
dt1 <- data.table(Dt = as.Date(c("2023-01-01", "2023-01-02", "2023-01-14", "2023-01-15")))

# 查找表dtLookup
dtLookup <- data.table(Dt = as.Date(c("2023-01-07", "2023-01-14", "2023-01-21")))

实现方法 #

可以通过data.table的非等值连接结合mult="first"参数直接实现，步骤如下：

1. 先确保查找表按日期升序排序（避免因数据无序导致匹配错误）
2. 用非等值连接匹配每个日期对应的最小符合条件值

# 排序查找表（关键：保证匹配的是最小的>=当前日期的值）
setorder(dtLookup, Dt)

# 非等值连接并新增LookupDt列
dt1[, LookupDt := dtLookup[.SD, on = .(Dt >= Dt), x.Dt, mult = "first"]]

# 查看结果
dt1

运行结果：

Dt   LookupDt
1: 2023-01-01 2023-01-07
2: 2023-01-02 2023-01-07
3: 2023-01-14 2023-01-14
4: 2023-01-15 2023-01-21

代码解释 #

• on = .(Dt >= Dt)：定义非等值连接规则，匹配dtLookup中Dt大于等于dt1当前行Dt的记录
• x.Dt：指定返回dtLookup表中的Dt列作为匹配结果
• mult = "first"：当存在多个符合条件的记录时，仅取第一个（因lookup表已升序排序，第一个就是最小的符合条件日期）

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