r - 可以使用 data.table 完成 SQL 非等连接任务(下面的示例)(更快和/或更整洁)吗？

Question

我有一个包含 3 列的数据表:pid(作业)、starttime(开始时间)和 fintime(结束时间)，如下所示:

require(data.table)

dt <- data.table(pid=sample(1:100,100), starttime = sample(1:100,100)/100)[,fintime:=starttime + round(runif(100)/4,2)] 

我需要确定所有可能的两个工作，这些工作可以按顺序完成，但要确认工作之间的可接受“差距”。我可以使用 SQL 在 0.05 到 0.4 个单位(时间)之间执行此操作，如下所示:

require(sqldf)
res <- sqldf("select a.pid as first, b.pid as second , a.starttime as startime,  b.fintime as fintime 
    from  dt a, dt b 
    where  a.fintime < b.starttime - 0.05
       and a.fintime > b.starttime - 0.4
  ")

我如何使用 data.table 做到这一点？(我希望在数据量大且约束更多的情况下比 sqldf 有性能改进)

Answer 1

所以这里有一个 data.table 方法，速度大约快 20 倍，但是有一些注意事项(在最后描述)。

require(data.table)
set.seed(1)  # for reproducible example
n <- 100     # simple example
dt <- data.table(pid=sample(1:n,n), 
                 starttime = sample(1:n,n)/n,2)[,fintime:=starttime + round(runif(n)/4,2)]
# sqldf approach
require(sqldf)
f.sql <- function(dt) {
  sqldf("create index idx on dt(starttime,fintime)")
  res <- sqldf("select a.pid as first, b.pid as second , a.starttime as starttime,  b.fintime as fintime 
    from  dt a, dt b 
    where b.starttime >= a.fintime + 0.05
      and b.starttime <= a.fintime + 0.4
  ")  
}
res.sql <- f.sql(dt)

# data.table approach with foverlaps(...): need >= 1.9.4 for this!!
packageVersion("data.table")
# [1] ‘1.9.4’
f.DT <- function(dt) {
  lookup <- dt[,list(second=pid, fintime, a=starttime,b=starttime)]
  setkey(lookup,a,b)
  DT      <- dt[,list(first=pid, starttime, a=fintime+0.05,b=fintime+0.4)]
  J.olaps <- foverlaps(DT,lookup,type="any",nomatch=0) 
  J.olaps[,list(first,second,starttime,fintime)]
}
res.DT <- f.DT(dt)

所以这在最新版本的 data.table (1.9.4) 中使用了 foverlaps(...) 函数。假设您有两个 data.tables，x 和 y。每个都有一对构成范围的列。 foverlaps(...) 查找 x 和 y 中 x 范围重叠的所有记录组合code> 和 y 中的范围。在这里我们设置它，以便 x 具有由 fintime+0.04 和 fintime+0.5 和 y 定义的范围两端的范围由 starttime 定义。所以现在 foverlaps(...) 查找开始时间比 fintime 多 0.04 到 0.5 之间的任何记录组合。

现在注意事项:

首先，这仅在您愿意将约束放宽到封闭间隔时才有效(据我所知)(例如，b.starttime >= a.fintime + 0.05 , 与严格 > 相比)。

其次，data.table 方法查找在 sql 方法中找到的所有记录加上一些额外的记录。您可以使用以下代码看到这一点:

indx  <- data.table(first=res.sql$first,second=res.sql$second,key=c("first","second"))
setkey(res.DT,first,second)
extra <-  res.DT[!indx,]

额外的记录看起来是合法的，所以问题是:为什么 sqldf(...) 找不到它们？我无法回答。

第三，这适用于您的示例，但可能不容易通过“更多约束”进行扩展。

最后，这是一个与您的实际数据更相似的数据集的“基准”:

set.seed(1)
n <- 1e4   # more realistic example
dt <- data.table(pid=sample(1:n,n), 
                 starttime = sample(1:n,n)/n)[,fintime:=starttime + round(runif(n)/4,2)]
system.time(res.sql <- f.sql(dt))
#    user  system elapsed 
#   45.25    0.53   45.80 
system.time(res.DT  <- f.DT(dt))
#    user  system elapsed 
#    2.09    0.86    2.94