data.table `on`参数不等连接逻辑及相关技术问题咨询

一、不等自连接的基础疑问 #

原测试代码 #

library(data.table)
dt <- data.table(ind=1:5, x=letters[1:5])
dtcopy <- copy(dt)
dtcopy[,indminus3 := ind - 3]
setnames(dtcopy, names(dtcopy), paste0(names(dtcopy), "copy"))
dtcopy[,indcopy2 := indcopy]
dt[dtcopy, on = c("ind<=indcopy", "ind>indminus3copy")]

执行结果 #

ind x ind.1 xcopy indcopy2
 1:   1 a    -2     a        1
 2:   2 a    -1     b        2
 3:   2 b    -1     b        2
 4:   3 a     0     c        3
 5:   3 b     0     c        3
 6:   3 c     0     c        3
 7:   4 b     1     d        4
 8:   4 c     1     d        4
 9:   4 d     1     d        4
10:   5 c     2     e        5
11:   5 d     2     e        5
12:   5 e     2     e        5

疑问解答 #

1. ind.1的来源与含义
   ind.1是data.table在不等连接中自动生成的列，对应连接条件中最后一个针对ind列的右表（dtcopy）列值——也就是indminus3copy。当对同一列设置多个比较条件时，data.table会将最后一个条件的右表列值以.1后缀命名添加到结果中，用来标识该条件的边界值。

2. indcopy列缺失的原因
   indcopy没有出现在结果中，是因为它被用作了连接条件的右表列（ind<=indcopy）。在data.table的不等连接语法中，用作连接条件的右表列不会自动保留到结果集里，除非你显式在j参数中引用它，或者像indcopy2那样复制一份不参与连接条件的列。你的假设是正确的：如果indcopy不参与连接条件，或者用复制后的列做连接，它就会像indcopy2一样出现在结果里。

3. ind与x不匹配的原因
   你搞反了data.table连接操作的核心逻辑：dt[dtcopy, on=...]是将dtcopy作为查询表（i表），dt作为被查询表（x表）。更关键的是，不等连接中col<=icol的语法会将结果中的col列替换为icol（i表的列值），而非保留x表的col值。

比如结果中ind==2且x=="a"的行，实际是：dtcopy中indcopy=2的行，匹配dt中满足ind<=2且ind>-1的行（包括ind=1的x=a），此时ind列被替换为dtcopy的indcopy=2，而x列保留dt中匹配行的x值，所以出现了原dt中没有的组合。

二、滚动中位数实现的可行性与语法疑问 #

原实现代码 #

library(data.table)
dt <- data.table(time = as.POSIXct("2020-06-01") + c(13, 20, 23, 26, 30, 38, 42, 50),
                 x = c(0, .1, 1, 10, 100, 1000, 10000, 100000))
dtcopy <- copy(dt)
dtcopy[,timeminus10 := time - 10]
setnames(dtcopy, names(dtcopy), paste0(names(dtcopy), "copy"))
dtcopy[,timecopy2 := timecopy]
dt[dtcopy, on = c("time<=timecopy", "time>=timeminus10copy")][,median(x),by=timecopy2]$V1

可行性分析 #

这种基于不等自连接的滚动中位数实现方法完全可行，且性能远优于frollapply。原因是data.table的连接操作基于底层优化的索引机制，而frollapply是逐行调用函数，开销极大，对于1万条以上的数据，速度差距会非常明显。

语法疑问解答 #

1. 为什么dt[dtcopy, on=...][,median(x),by=time]能得到正确结果？
   连接后的time列已经被替换为dtcopy的timecopy值（即每个窗口的当前时间），按这个time分组计算中位数，正好对应每个时间窗口内的所有匹配行的中位数，因此结果正确。

2. 为什么dt[dtcopy, on=...,median(x),by=time]得到错误结果？
   data.table的语法规则是x[i, j, on]，其中j和by的作用域是i表（dtcopy），而非连接后的结果集。此时x指的是dtcopy的xcopy，而time列在dtcopy中不存在（只有timecopy），因此会得到错误结果甚至报错。必须先完成连接得到完整结果集，再链式调用[]进行分组计算。

3. 为什么链式写法正常，而合并写法报错？
   和第二个疑问原因一致：合并写法中j（median(x)）和by（timecopy2）的作用域是dtcopy，此时无法访问连接后才会出现的dt的x列；而链式写法先完成连接，得到包含dt$x和dtcopy$timecopy2的结果集，此时分组计算自然可以正常运行。

代码优化建议 #

无需复制表并重命名，直接用自连接即可实现，代码更简洁：

dt[dt, on = c(time <= time, time >= time - 10), allow.cartesian=TRUE][,median(x),by=time]

添加allow.cartesian=TRUE是因为自连接可能产生大量行，data.table默认会阻止这种情况，需要显式允许。

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