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

Question

我可以在R中使用以下代码在任何通用SQL数据库中选择不同的行。我会使用，dplyr::distinct()但SQL语法不支持它。无论如何，这确实有效：

dbGetQuery(database_name, 
           "SELECT t.* 
           FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS SEQNUM 
           FROM table_name t
           ) t 
           WHERE SEQNUM = 1;")

我一直在成功使用它，但是想知道如何在其他dplyr步骤之后传递相同的SQL查询，而不是像上面所示那样仅将其用作第一步。最好用一个例子来说明：

distinct.df <- 
  left_join(sql_table_1, sql_table_2, by = "col5") %>% 
  sql("SELECT t.* 
      FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS SEQNUM 
      FROM table_name t
      ) t 
      WHERE SEQNUM = 1;")

因此，我有dplyr::left_join()两个SQL表，然后我要查看不同的行，并保留所有列。是否如上所述将SQL代码通过管道传递到R中（简单地利用该sql()函数）？如果是的话我会怎么使用的table_name就行了FROM table_name t？

在我的第一个示例中，我使用了从中提取的实际表名。太明显了！但是在这种情况下，我正在使用管道，并且习惯于使用magrittr代词，.
或者有时使用rlang的.data代词，如果我在内存中使用R而没有数据库的话。

我虽然在SQL数据库中…所以如何处理这种情况？我如何正确地将已知有效的SQL管道传输到我的R代码中（使用适当的表名代词）？dbplyr的参考页是一个很好的起点，但是并不能真正回答这个特定问题。

Answer 1

看来您想将自定义SQL代码与从中自动生成的SQL代码结合起来dbplyr。为此，重要的是要区分：

• DBI::db* 命令-在数据库上执行提供的SQL并返回结果。
• dbplyr 转换-您可以在远程连接表的情况下工作

您只能以某些方式组合这些。下面根据您的特定用例给出了几个示例。所有这些都假定该DISTINCT命令是您的特定SQL环境中接受的命令。

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

如果您会自我推广，我建议您看一下我的dbplyr_helpersGitHub存储库（此处）。这包括：

• 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

与前面的示例一样，dfR链接到上述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数据帧。