也许这对你来说是一个解决方案,使用glue_sql,它适用于我的电脑
https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/13 https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/13
Rmd 块:
library(DBI)
library(glue)
library(RSQLite)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")
SELECT * FROM mtcars
WHERE cyl IN (?cyl_sql)
LIMIT 3
对于日期列,您可能必须根据您的数据库或用途正确设置其格式to_date(etc, '%d%...')
在 sqlite 中,使用 ISO-8601 日期很容易,例如:
library(DBI)
library(glue)
library(RSQLite)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
mtcars$DATE_1 <- seq(from = Sys.Date() - 15, to = Sys.Date() + nrow(mtcars) -16, by = 1) %>% as.character()
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)
cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")
d_date_sql <- glue_sql("{Sys.Date()*}", .con = con)
请注意,您必须为字符参数指定 .con 参数,如上所述here https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/14
SELECT cyl, mpg, DATE_1 FROM mtcars
WHERE DATE_1 >= ?d_date_sql
ORDER BY DATE_1