使用data.table区间连接时如何保留df2的date1/date2原始值？

我有两个数据框：

df1
group date
1 A 2023-01-10
2 A 2023-01-15
3 B 2023-01-09
4 B 2023-01-12
df2
group date1 date2 value
1 A 2023-01-09 2023-01-11 2
2 B 2023-01-11 2023-01-14 3

想要用data.table实现：基于df1的date落在df2的date1和date2区间内的条件，将df2连接到df1上。但运行以下代码后，date1和date2被替换成了date的值，无法保留df2中的原始值：

library(data.table)
setDT(df1)
setDT(df2)
df2[df1, 
    .(group, date, date1, date2, value),
    on = .(group, date1 <= date, date2 >= date)]

输出结果：

#>    group       date      date1      date2 value
#> 1:     A 2023-01-10 2023-01-10 2023-01-10     2
#> 2:     A 2023-01-15 2023-01-15 2023-01-15    NA
#> 3:     B 2023-01-09 2023-01-09 2023-01-09    NA
#> 4:     B 2023-01-12 2023-01-12 2023-01-12     3

预期输出应该保留df2中date1和date2的原始值：

#>    group       date      date1      date2 value
#> 1:     A 2023-01-10 2023-01-09 2023-01-11     2
#> 2:     A 2023-01-15         NA         NA    NA
#> 3:     B 2023-01-09         NA         NA    NA
#> 4:     B 2023-01-12 2023-01-11 2023-01-14     3

dput数据：

df1 <- structure(list(group = c("A", "A", "B", "B"), date = structure(c(1673305200, 
1673737200, 1673218800, 1673478000), class = c("POSIXct", "POSIXt"
), tzone = "")), class = "data.frame", row.names = c(NA, -4L))

df2 <- structure(list(group = c("A", "B"), date1 = structure(c(1673218800, 
1673391600), class = c("POSIXct", "POSIXt"), tzone = ""), date2 = structure(c(1673391600, 
1673650800), class = c("POSIXct", "POSIXt"), tzone = ""), value = c(2, 
3)), class = "data.frame", row.names = c(NA, -2L))

出现该问题的核心原因是：在on参数中使用date1 <= date和date2 >= date时，data.table会将date1、date2的列值替换为连接用的date值。要保留df2的原始字段，可通过以下两种方法解决：

方法1：明确引用df2的原始列（x.前缀） #

在j参数中指定列时，用x.date1和x.date2明确指向df2（连接操作中的x表）的原始列，避免被连接值覆盖：

library(data.table)
setDT(df1)
setDT(df2)

df2[df1, 
    .(group, date, date1 = x.date1, date2 = x.date2, value),
    on = .(group, date1 <= date, date2 >= date)]

方法2：反向连接（以df1为基础表） #

反过来以df1作为主表，通过on条件匹配df2，直接选取df2的列即可保留原始值：

library(data.table)
setDT(df1)
setDT(df2)

df1[df2, 
    .(group, date, date1, date2, value),
    on = .(group, date >= date1, date <= date2),
    allow.cartesian = TRUE]

注意：如果存在单个date匹配多个区间的场景，需设置allow.cartesian = TRUE（可根据实际数据情况调整）。

两种方法均能得到符合预期的输出结果。

内容的提问来源于Stack Exchange，提问作者Quinten