如何将SQL传递到R的dplyr中？

如何将SQL传递到R的dplyr中？

看来您想将自定义SQL代码与从中自动生成的SQL代码结合起来

dbplyr

。为此，重要的是要区分：

• DBI::db*

  命令-在数据库上执行提供的SQL并返回结果。

• dbplyr

  转换-您可以在远程连接表的情况下工作

您只能以某些方式组合这些。下面根据您的特定用例给出了几个示例。所有这些都假定该

DISTINCT

命令是您的特定SQL环境中接受的命令。

涵盖许多不同用例的参考示例

如果您会自我推广，我建议您看一下我的

dbplyr_helpers

GitHub存储库（此处）。这包括：

• union_all

  该函数接收通过访问的两个表，

  dbplyr

  并使用一些自定义SQL代码输出一个表。

• write_to_datebase

  该函数接受通过访问的表

  dbplyr

  并将其转换为可以通过执行的代码

  DBI::dbExecute

自动管道

dbplyr

当您使用

dplyr

定义了SQL翻译的标准动词时，系统会自动将您的代码传送到下一个查询中。只要定义了sql转换，您就可以将许多管道（我一次使用10个或更多）链接在一起，（几乎）唯一的缺点是sql转换的查询很难被人阅读。

例如，考虑以下内容：

library(dbplyr)library(dplyr)tmp_df = data.frame(col1 = c(1,2,3), col2 = c("a","b","c"))df1 = tbl_lazy(tmp_df, con = simulate_postgres())df2 = tbl_lazy(tmp_df, con = simulate_postgres())df = left_join(df1, df2, by = "col1") %>%  distinct()

当您随后调用

show_query(df)

R时，将返回以下自动生成的SQL代码：

SELECt DISTINCT *FROM (SELECt `LHS`.`col1` AS `col1`, `LHS`.`col2` AS `col2.x`, `RHS`.`col2` AS `col2.y`FROM `df` AS `LHS`LEFT JOIN `df` AS `RHS`ON (`LHS`.`col1` = `RHS`.`col1`)) `dbplyr_002`

但格式不佳。请注意，初始命令（左联接）以嵌套查询的形式出现，外部查询中的字母与众不同。因此，

df

是一个指向上述sql查询定义的远程数据库表的R链接。

创建自定义SQL函数

您可以通过管道传递

dbplyr

到自定义SQL函数。管道意味着被管道传输的东西成为接收函数的第一个参数。

custom_distinct <- function(df){  db_connection <- df$src$con  sql_query <- build_sql(con = db_connection,   "SELECt DISTINCT * FROM (n",   sql_render(df),   ") AS nested_tbl"  )  return(tbl(db_connection, sql(sql_query)))}df = left_join(df1, df2, by = "col1") %>%  custom_distinct()

然后，当您调用

show_query(df)

R时，应该返回以下SQL代码（我说“应该”，因为我无法在模拟的SQL连接中使用它），但格式不正确：

SELECt DISTINCT * FROM (SELECt `LHS`.`col1` AS `col1`, `LHS`.`col2` AS `col2.x`, `RHS`.`col2` AS `col2.y`FROM `df` AS `LHS`LEFT JOIN `df` AS `RHS`ON (`LHS`.`col1` = `RHS`.`col1`)) nested_tbl

与前面的示例一样，

df

R链接到上述sql查询定义的远程数据库表。

将dbplyr转换为DBI

您可以从现有的

dbplyr

远程表中获取代码，并将其转换为可以使用执行的字符串

DBI::db*

。

作为编写独特查询的另一种方式：

df1 = tbl_lazy(tmp_df, con = simulate_postgres())df2 = tbl_lazy(tmp_df, con = simulate_postgres())df = left_join(df1, df2, by = "col1")custom_distinct2 = paste0("SELECt DISTINCT * FROM (",    as.character(sql_render(df)),    ") AS nested_table")local_table =   dbGetQuery(db_connection, custom_distinct2)

根据前面的示例，该命令将使用等效的sql命令返回本地R数据帧。