如何将CTEs（公共表表达式）提取为R中的数据框列表？

方法1：用dbplyr分步构建CTE（推荐） #

如果可以用dbplyr语法替代原生SQL编写查询，这种方法最直观，能直接保留每个CTE的独立引用，自动处理依赖关系：

library(dbplyr)
library(tidyverse)

# 连接数据库（PostgreSQL只需替换DBI::dbConnect的驱动参数）
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
copy_to(con, iris)

# 分步定义每个CTE（作为数据库端的tbl对象）
tbl_set <- tbl(con, "iris") %>% filter(Species == 'setosa')
tbl_ver <- tbl(con, "iris") %>% filter(Species == 'versicolor')
tbl_all <- union_all(tbl_set, tbl_ver)

# 将所有CTE收集为本地数据框列表
cte_list <- list(
  tbl_set = collect(tbl_set),
  tbl_ver = collect(tbl_ver),
  tbl_all = collect(tbl_all)
)

# 查看结果
cte_list

方法2：针对已有原生SQL查询的处理 #

如果已经写好原生SQL，可以通过以下两种方式提取CTE：

方式A：多结果集查询（PostgreSQL支持） #

修改原SQL，将每个CTE的查询语句依次列出，一次性获取所有结果：

multi_query <- sql(
  "WITH
  tbl_set AS (SELECT * FROM iris WHERE Species = 'setosa'),
  tbl_ver AS (SELECT * FROM iris WHERE Species = 'versicolor'),
  tbl_all AS (
    SELECT *
    FROM tbl_set 
    UNION ALL SELECT * FROM tbl_ver)
SELECT * FROM tbl_set;
SELECT * FROM tbl_ver;
SELECT * FROM tbl_all;"
)

# 执行并获取所有结果集
results <- DBI::dbSendQuery(con, multi_query)
cte_list <- list(
  tbl_set = DBI::dbFetch(results),
  tbl_ver = DBI::dbFetch(results),
  tbl_all = DBI::dbFetch(results)
)
DBI::dbClearResult(results)

方式B：按依赖顺序逐个执行 #

如果数据库不支持多结果集，按CTE的依赖顺序依次查询，依赖其他CTE的部分可以用本地数据框拼接：

# 提取无依赖的CTE
tbl_set_df <- tbl(con, sql("SELECT * FROM iris WHERE Species = 'setosa'")) %>% collect()
tbl_ver_df <- tbl(con, sql("SELECT * FROM iris WHERE Species = 'versicolor'")) %>% collect()

# 处理依赖前两个CTE的tbl_all
tbl_all_df <- bind_rows(tbl_set_df, tbl_ver_df)

# 组合成目标列表
cte_list <- list(
  tbl_set = tbl_set_df,
  tbl_ver = tbl_ver_df,
  tbl_all = tbl_all_df
)

方法3：SQL解析自动提取（进阶） #

对于超复杂的查询，可以用sqlparse包解析SQL，自动提取CTE定义并执行：

if(!require("sqlparse")){install.packages("sqlparse")}; library(sqlparse)

# 解析原SQL语句
parsed <- sql_parse(query)
# 提取CTE子句
cte_clause <- parsed[[1]]$children[parsed[[1]]$type == 'with_clause'][[1]]
# 提取每个CTE的名称和查询语句
cte_defs <- map(cte_clause$children, function(node) {
  if(node$type == 'cte'){
    list(
      name = node$children[[1]]$name,
      query = sql_render(node$children[[3]])
    )
  }
}) %>% compact()

# 逐个执行CTE查询（需确保依赖顺序正确，复杂场景需额外处理依赖）
cte_list <- map(cte_defs, function(def) {
  tbl(con, sql(def$query)) %>% collect()
}) %>% set_names(map_chr(cte_defs, ~.$name))

# 添加原查询的最终结果
final_query_str <- str_remove(as.character(query), "^WITH.*?SELECT") %>% str_trim()
cte_list[['final_result']] <- tbl(con, sql(final_query_str)) %>% collect()

内容的提问来源于stack exchange，提问作者Paul