在dplyr的filter_中使用非标准评估（NSE）和从MySQL提取数据

Question

我想从一个带有动态筛选器的 SQL 服务器中提取一些数据。我正在使用优秀的 R 包 dplyr，方式如下：

#Create the filter
filter_criteria = ~ column1 %in% some_vector
#Connect to the database
connection <- src_mysql(dbname <- "mydbname", 
             user <- "myusername", 
             password <- "mypwd", 
             host <- "myhost") 
#Get data
data <- connection %>%
 tbl("mytable") %>% #Specify which table
 filter_(.dots = filter_criteria) %>% #non standard evaluation filter
 collect() #Pull data

这段代码运行良好，但现在我想以某种方式对我的表格中的所有列进行循环，因此我想将过滤器编写为：

#Dynamic filter
i <- 2 #With a loop on this i for instance
which_column <- paste0("column",i)
filter_criteria <- ~ which_column %in% some_vector

然后使用更新后的筛选器重新应用第一段代码。

不幸的是，这种方法并没有给出预期的结果。实际上，它不会产生任何错误，但也不会将任何结果提取到R中。 特别是，我稍微查看了一下由这两个代码片段生成的SQL查询，发现有一个重要的区别。

有效的第一段代码生成的查询形式为：

SELECT ... FROM ... WHERE 
`column1` IN ....

（`在列名中的符号），第二个生成以下格式的查询：

SELECT ... FROM ... WHERE 
'column1' IN ....

（在列名中的“'”符号）

有人有关于如何制定筛选条件使其起作用的建议吗？

- Lorenzo Rossi

Answer 1

这并不是与SQL相关的。 这个R语言的例子也不起作用:

df <- data.frame(
     v1 = sample(5, 10, replace = TRUE),
     v2 = sample(5,10, replace = TRUE)
)
df %>% filter_(~ "v1" == 1)

它不起作用是因为你需要传递表达式 ~ v1 == 1 给 filter_，而不是表达式 ~ "v1" == 1。

解决问题的方法是简单地使用引用运算符 quo 和去引用运算符 !!。

library(dplyr)
which_column = quot(v1)
df %>% filter(!!which_column == 1)

- Matthew

---

嘿，马修，非常感谢你的回答。你提出的第一种方法解决了我的问题，谢谢！我确实阅读了nse vignette，并且自己也接近这个解决方案，但是我忘记了as.name/as.symbol调用。你建议的第二种方法在我编写的代码中不可行，因为R中没有数据框架，过滤器必须作为生成的SQL查询中的WHERE子句，然后提取数据。这就是我在问题的标题和文本中包含SQL提及的原因。再次感谢你的回答！ - Lorenzo Rossi

请问如果1也是另一个变量，该怎么办？类似于filter_criteria <- interp(~ which_column == val123, which_column = as.name("v1"), val123=???)。这里的val123是一个整数。 - qwerty123

过滤条件 <- interp(~ var1 == var2, var1 = as.name("v1"), var2 = as.name("v2")) - Matthew

Answer 2

有一种替代方案，使用dplyr版本0.5.0（可能早于此版本），可以将组合的字符串作为.dots参数传递，这比lazyeval :: interp解决方案更易读：

df <- data.frame(
     v1 = sample(5, 10, replace = TRUE),
     v2 = sample(5,10, replace = TRUE)
)

which_col <- "v1"
which_val <- 1
df %>% filter_(.dots= paste0(which_col, "== ", which_val))

  v1 v2
1  1  1
2  1  2
3  1  4

dplyr 0.6及以后版本的更新:

packageVersion("dplyr")
# [1] ‘0.5.0.9004’

df %>% filter(UQ(rlang::sym(which_col))==which_val)
#OR
df %>% filter((!!rlang::sym(which_col))==which_val)

(类似于 @Matthew 对于 dplyr 0.6 的回答，但我假设 which_col 是一个字符串变量。)

第二次更新：Edwin Thoen为整洁评估创建了一个不错的备忘单：https://edwinth.github.io/blog/dplyr-recipes/

- LmW.

Answer 3

这里有一个更加简洁的解决方案，它使用了 extract 函数的典型行为，即通过字符值选择列，而不是将其转换为语言元素：'['。

df %>% filter(., '['(., which_column)==1 )

set.seed(123)
df <- data.frame(
      v1 = sample(5, 10, replace = TRUE),
      v2 = sample(5,10, replace = TRUE)
 )
which_column <- "v1"
df %>% filter(., '['(., which_column)==1)
#  v1 v2
#1  1  5

- IRTFM

---

这也可以工作：filter(df, '['(df, which_column) == 1)。 - biocyberman