使用变量中存储的条件选择数据

Question

假设我在服务器上有这个表:

library(dplyr)
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
iris$id = 1:nrow(iris)
dbWriteTable(con, "iris", iris)

我想从这个数据集中选择一些随机的行-假设我创建一个R变量，其中包含我想选择的随机行:

rows_to_select = sample.int(10, 5, replace = TRUE)
[1] 1 1 8 8 7

然后我尝试从我的表中选择这些行-但是这个"rows_to_select"变量由于某些原因没有被识别:

DBI::dbGetQuery(con, "select a.* from (select *, row_number() over (order by id) as rnum from iris)a where a.rnum in (rows_to_select) limit 100;")
Error: no such column: rows_to_select

如果我手动指定我想要的行(例如，我想要第一行和第五行被选中两次)，这段代码工作得很好:

#works - but does not return the 5th row twice
DBI::dbGetQuery(con, "select a.* from (select *, row_number() over (order by id) as rnum from iris)a where a.rnum in (1,5,5) limit 100;")

• 有人知道如何解决这个问题吗?

谢谢!

Answer 1

一般来说，仅仅在查询中包含rows_to_select并不会知道到达SQLite环境之外并"入侵";R环境(完全不同!)并查找变量。(对于这个问题，为什么select a.* ...找不到dplyr::select?)这既是出于实用的原因，也是出于安全的考虑(尽管主要是出于实用的考虑)。

您可能需要考虑参数化查询，而不是手动构造查询字符串。除了对恶意SQL注入的安全担忧(例如，XKCD的利用一个妈妈又名"小鲍比表")之外，它还关注格式错误的字符串或Unicode-vs-ANSI错误，即使它是一个数据分析人员运行查询。DBI支持参数化查询

长话短说，试试这个:

set.seed(42)
rows_to_select = sample.int(10, 5, replace = TRUE)
rows_to_select
# [1]  1  5  1  9 10
qmarks <- paste(rep("?", length(rows_to_select)), collapse = ",")
qmarks
# [1] "?,?,?,?,?"
DBI::dbGetQuery(con, paste(
"select a.*
from (select *, row_number() over (order by id) as rnum from iris) a
where a.rnum in (", qmarks, ") limit 100;"),
params = as.list(rows_to_select))
#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species id rnum
# 1          5.1         3.5          1.4         0.2  setosa  1    1
# 2          5.0         3.6          1.4         0.2  setosa  5    5
# 3          4.4         2.9          1.4         0.2  setosa  9    9
# 4          4.9         3.1          1.5         0.1  setosa 10   10

在这种情况下，它是相当微不足道的，但如果您有一个更复杂的查询，在查询的不同位置使用问号("binding ")，则顺序必须与分配给dbGetQuery的params=参数的list的元素完全对齐。

---

备选方案:插入一个包含候选值的临时表，然后对它进行左连接。

dbWriteTable(con, "mytemp", data.frame(rnum = rows_to_select), temporary = TRUE)
DBI::dbGetQuery(con, 
"select i.* from mytemp m left join iris i on i.id=m.rnum")
#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species id
# 1          5.1         3.5          1.4         0.2  setosa  1
# 2          5.0         3.6          1.4         0.2  setosa  5
# 3          5.1         3.5          1.4         0.2  setosa  1
# 4          4.4         2.9          1.4         0.2  setosa  9
# 5          4.9         3.1          1.5         0.1  setosa 10
DBI::dbExecute(con, "drop table mytemp")
# [1] 0