dplyr 的 filter_ & 中的非标准评估 (NSE)从 MySQL 中提取数据 [英] Non-standard evaluation (NSE) in dplyr's filter_ & pulling data from MySQL

问题描述

我想从带有动态过滤器的 sql 服务器中提取一些数据.我正在通过以下方式使用很棒的 R 包 dplyr:

I'd like to pull some data from a sql server with a dynamic filter. I'm using the great R package dplyr in the following way:

#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

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

This piece of code works fine but now I'd like to loop it somehow on all the columns of my table, thus I'd like to write the filter as:

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

然后使用更新的过滤器重新应用第一个代码.

And then reapply the first code with the updated filter.

不幸的是，这种方法没有给出预期的结果.事实上，它不会给出任何错误，但甚至不会将任何结果放入 R 中.特别是，我仔细研究了两段代码生成的 SQL 查询，有一个重要区别.

Unfortunately this approach doesn't give the expected results. In fact it does not give any error but doesn't even pull any result into R. In particular, I looked a bit into the SQL query generated by the two pieces of code and there is one important difference.

虽然第一个有效的代码生成了以下形式的查询:

While the first, working, code generates a query of the form:

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

(` 在列名中签名)，第二个生成如下形式的查询:

(` sign in the column name), the second one generates a query of the form:

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

('在列名中签名)

有没有人对如何制定过滤条件使其工作有任何建议?

Does anyone have any suggestion on how to formulate the filtering condition to make it work?

推荐答案

它与 SQL 没有真正的关系.R 中的这个例子也不起作用:

It's not really related to SQL. This example in R does not work either:

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

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

It does not work because you need to pass to filter_ the expression ~ v1 == 1 — not the expression ~ "v1" == 1.

要解决问题，只需使用引用运算符quo 和反引用运算符!!

To solve the problem, simply use the quoting operator quo and the dequoting operator !!

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

这篇关于dplyr 的 filter_ & 中的非标准评估 (NSE)从 MySQL 中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！