data.table中带between条件的左连接实现及重复数据问题求助

需求说明 #

以id为关联键，添加date介于start和end之间的条件，将Y左连接至X，同时保留X的所有行。

示例数据 #

library(data.table)

X <- data.table( 
    id = 1:5
  , start = 1:5
  , end   = 5:9
  , x = LETTERS[1:5]
)

Y <- data.table(
    id = 3:5
  , date = c(1,5,7)
  , y = letters[3:5]
)

X的内容：

id start end x
1:  1     1   5 A
2:  2     2   6 B
3:  3     3   7 C
4:  4     4   8 D
5:  5     5   9 E

Y的内容：

id date y
1:  3    1 c
2:  4    5 d
3:  5    7 e

问题重现 #

尝试用以下代码连接时出现错误：

Y[X,on=.(id=id, between(date,start, end))]

报错信息：

Error in colnamesInt(x, names(on), check_dups = FALSE) : 
  argument specifying columns specify non existing column(s): cols[2]='between(date, start, end)'

更新说明：实际数据中，该范围条件会导致X与Y存在多对多关联，尝试的代码出现笛卡尔积相关错误，即使设置allow.cartesian=TRUE也得到不符合预期的笛卡尔积结果，但用sqldf的SQL语句可得到正确结果。

解决方案 #

data.table的on=参数不支持直接传入between()表达式，需用以下两种方式实现：

方法1：先关联再筛选范围 #

先通过id做左连接保留X所有行，再筛选符合范围条件的记录（包括无匹配的X行）：

result <- X[Y, on = .(id), allow.cartesian = TRUE][is.na(y) | between(date, start, end)]

方法2：使用非等连接（推荐） #

这是data.table处理范围类连接的标准方式，直接在on=中定义范围条件：

result <- X[Y, on = .(id, start <= date, end >= date), allow.cartesian = TRUE][, 
  .(id, start = i.start, end = i.end, x, date, y)
]

注：非等连接会临时修改start和end列的值，因此需要通过i.start和i.end取回X原有的start、end值，再重新整理列顺序。

预期结果 #

两种方法均可得到以下正确结果：

id start end x date    y
1:  1     1   5 A   NA <NA>
2:  2     2   6 B   NA <NA>
3:  3     3   7 C   NA <NA>
4:  4     4   8 D    5    d
5:  5     5   9 E    7    e

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