使用R向量查询sqlite数据库的有效方法

Question

我在R中有一个值的向量，并希望从sqlite数据库中获得响应值。我使用以下代码。

values = c()

for (a in keys)
{
    result <- dbGetQuery(con," SELECT content FROM aacontent WHERE Id=?",a)
    values = c(values,results)
}

不幸的是，这段代码非常慢。有没有更有效的方法来做到这一点？

谢谢,

约翰尼斯

Answer 1

如果内容不是很大，则将其全部读入R，并使用类似于R的match函数、sqldf函数或data.table函数

如果一个内容太大，而键很小，那么将键写入sqlite表并执行连接查询。在其中一个或两个上创建索引可能会对您有所帮助。

Answer 2

这些当然都是预置的工具，用于在R中执行这样的SQL查询任务(因为您使用的是SQLite，所以我一定要查看sqldf)，但根据我的经验，我最终只是编写了许多用于构建查询的小辅助包装函数。

例如，在您的例子中，您的问题实际上不是R部分，而是您希望将keys中的所有值合并到一个查询中。所以你可能想要一个更像这样的查询：

SELECT content FROM aacontent WHERE Id IN (val1,val2,...)

然后，技巧是在R中使用paste来构建IN子句。我倾向于在dbGetQuery上使用一个简单的包装器函数，该函数使用...参数和paste将不同片段的查询拼接在一起。如下所示：

myQuery <- function(con,...){
    arg <- list(...)
    res <- dbGetQuery(con,paste(arg,collapse = ""))
    res
}

因此，在使用IN子句时，可以更容易地将内容缝合在一起：

myQuery(con,"SELECT content FROM aacontent WHERE Id IN (",
             paste(keys,collapse = ","),"))

注意，如果keys中的值是字符，这就有点困难了，因为那时您需要对paste做更多的工作来使每个元素两边都有单引号，但这不是很多的工作。

如果数据库相当小，这个建议就更有意义；如果你要处理更大的数据，Spacedman的建议可能更值得研究。