r - 在 R 中加速 sqldf

Question

我在 R 中有一个程序，我已经运行了大约一天，它只完成了大约 10%。缓慢的主要原因是必须制作数千个 sqldf(SELECT ...)使用 R 包调用长度约为 100 万的数据集 sqldf .我的选择语句目前采用以下形式:

sqldf(SELECT V1, V2, FROM mytable WHERE cast(start as real) <= sometime and cast(realized as real) > sometime)

sometime只是一些代表 unix 时间戳的整数，start和 realized是 mytable 的列也充满了 unix 时间戳条目。然而，我另外知道的是 |realized - start| < 172800总是，这是一个相当小的时期，因为数据集跨越一年多。我的想法是，我应该能够利用这个事实告诉 R 只从时间 +- 172800 检查数据帧。在每个电话中。

是包裹sqldf用在这里不合适？我应该使用 data.frame 的传统 [,] 遍历吗？ ？有没有一种简单的方法可以结合这个事实来加速程序？我的直觉是分解数据框，对向量进行排序，然后构建自己遍历和选择适当条目的自定义函数，但如果这是最好的方法，我正在寻求一些肯定。

Answer 1

首先，慢的部分可能是 cast(...)，所以与其在每个查询中对每条记录执行两次，不如离开 start 和 realized 作为时间戳，并更改查询以适应它。

其次，data.table 选项仍然快了大约 100 倍(但是请参阅最后关于使用 sqldf 进行索引的部分) .

library(sqldf)
library(data.table)

N <- 1e6
# sqldf option
set.seed(1)
df <- data.frame(start=as.character(as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T)),
                 realized=as.character(as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T)),
                 V1=rnorm(N), V2=rpois(N,4))

sometime <- "2000-01-05 00:00:00"
query <- "SELECT V1, V2 FROM df WHERE start <= datetime('%s') and realized > datetime('%s')"
query <- sprintf(query,sometime,sometime)          
system.time(result.sqldf <- sqldf(query))
#    user  system elapsed 
#   12.17    0.03   12.23 

# data.table option
set.seed(1)
DT <- data.table(start=as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T),
                 realized=as.POSIXct("2000-01-01")+sample(0:1e6,N,replace=T),
                 V1=rnorm(N), V2=rpois(N,4))
setkey(DT,start,realized)
system.time(result.dt <- DT[start<=as.POSIXct(sometime) & realized > as.POSIXct(sometime),list(V1,V2)])
#    user  system elapsed 
#    0.15    0.00    0.15 

请注意，这两个结果集的排序方式不同。

编辑 基于@G.Grothendieck(sqldf 包的作者)的以下评论。

这变成了一个非常好的包比较......

# code from G. Grothendieck comment
sqldf()      # opens connection
sqldf("create index ix on df(start, realized)")
query <- fn$identity("SELECT V1, V2 FROM main.df WHERE start <= '$sometime' and realized > '$sometime'")
system.time(result.sqldf <- sqldf(query))
sqldf()      # closes connection
#    user  system elapsed 
#    1.28    0.00    1.28 

因此，在这种情况下，创建索引的速度 sqldf 大约提高了 10 倍。索引创建很慢，但你只需要做一次。 data.table 中的“键”创建(对表进行物理排序)速度非常快，但在这种情况下并没有提高性能(大约只有 2 倍)。

使用 system.time() 进行基准测试有点冒险(1 个数据点)，因此最好使用 microbenchmark(...)。请注意，要使其正常工作，我们必须运行上面的代码并保持连接打开(例如，删除最后一次调用 sqldf()。)

f.dt    <- function() result.dt <- DT[start<=as.POSIXct(sometime) & realized > as.POSIXct(sometime),list(V1,V2)]
f.sqldf <- function() result.sqldf <- sqldf(query)
library(microbenchmark)
microbenchmark(f.dt(),f.sqldf())
# Unit: milliseconds
#       expr      min        lq    median       uq       max neval
#     f.dt() 110.9715  184.0889  200.0634  265.648  833.4041   100
#  f.sqldf() 916.8246 1232.6155 1271.6862 1318.049 1951.5074   100

所以我们可以看到，在这种情况下，使用键的data.table比使用索引的sqldf快大约6倍。实际时间将取决于结果集的大小，因此您可能需要比较这两个选项。