我在服务器上有这个表 - 我正在使用 R 查询它:
library(dplyr)
library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "iris", iris)
我能够运行这个查询:
DBI::dbGetQuery(con, " select *, row_number() OVER ( ORDER BY Species) as rn
from iris
where Species = 'setosa'")
我想将此查询转换为 CTE - 但我不确定如何执行此操作(我认为可能需要围绕此放置某种 sql 包装器?):
#does not work
with cte_setosa as (select *, row_number() OVER ( ORDER BY Species) as rn
from iris
where Species = 'setosa')
最后,我会尝试像这样“调用”CTE:
library(glue)
setosa_sample_vector <- glue_sql(paste0("(", paste(sample(1:50, 30, replace = T), collapse = "),("), ")"), .con = con)
DBI::dbGetQuery(con, " select iris.*
from (values ?setosa_sample_vector) sv
left join cte_setosa as base on iris.rn = sv.column1 ;")
- 有人可以告诉我如何在 R/SQL 中定义 CTE 吗?
谢谢你!