问题描述:

How can I pass a column name as a parameter to SQL in R sqldf?

Neither

q <- "Q10"

A = fn$sqldf('SELECT * FROM Customer WHERE $q < 100')

or

q <- "Q10"

A = fn$sqldf('SELECT * FROM Customer WHERE '$q' < 100')

works.

网友答案:

sprintf() will work charmingly in this case. Personally, I find code using sprintf() to be more easy to read (and even write) than paste().

q <- "Q10"
sql <- sprintf("SELECT * FROM Customer WHERE %s < 100", q)

sql
[1] "SELECT * FROM Customer WHERE Q10 < 100"

In fact, you can go much further with compiling complex SQL queries. Just to illustrate:

q <- "Q10"
value <- "150"
sql <- sprintf("SELECT * FROM Customer WHERE %s < %s", q, value)

sql
[1] "SELECT * FROM Customer WHERE Q10 < 150"
网友答案:

Solution:

You can use paste command to concatenate variable and string.

> library(sqldf)
> my_names <- names(sqldf("select * from iris limit 10"))
> sqldf(paste("select",my_names[1], "from iris limit 2", sep=" "))
      Sepal_Length
1          5.1
2          4.9

or using fn$ to perform the string interpolation, the last line could be written:

> fn$sqldf("select `my_names[1]` from iris limit 2")

Potential Problem:

The names might be different when you try to use sqldf to query. For example, the default names for dataset iris is:

> names(iris)
[1] "Sepal.Length" "Sepal.Width"  "Petal.Length" "Petal.Width"  "Species" 

However, the names have to be formatted in a different way when you try to use the column names to make queries:

> sqldf("select * from iris limit 1")
  Sepal_Length Sepal_Width Petal_Length Petal_Width Species
1          5.1         3.5          1.4         0.2  setosa    

This is the official explaination:

Here is an example of sorting and limiting output from an SQL select statement on the iris data frame that comes with R. Note that although the iris dataset uses the name Sepal.Length the RSQLite layer converts that to Sepal_Length.

So that is the reason in my solution I first created the my_names variable from sqldf select statement instead of names(iris).

网友答案:

For me the sprintf is the perfect solution, you need to change the %s by %d for integers i think.

q= 'Q10' ; value= 150
sql <- sprintf("SELECT * FROM Customer WHERE %s < %d", q, value)

sql
[1] "SELECT * FROM Customer WHERE Q10 < 150"
相关阅读:
Top