sql - 如何将 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 代词 .或者有时 .data如果我在没有数据库的情况下在 R 中工作的内存中，则来自 rlang 的代词。

虽然我在一个 SQL 数据库中......那么我该如何处理这种情况呢？如何正确地将已知的工作 SQL 传输到我的 R 代码中(使用正确的表名代词)？ dbplyr's reference page是一个很好的起点，但并没有真正回答这个具体问题。

Answer 1

您似乎希望将自定义 SQL 代码与来自 dbplyr 的自动生成的 SQL 代码结合起来。 .为此，重要的是要区分:

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

dbplyr翻译 - 使用远程连接到表的地方

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

涵盖许多不同用例的引用示例

如果你会原谅一些自我推销，我建议你看看我的 dbplyr_helpers GitHub 存储库 ( here)。这包括:

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是由上述 sql 查询定义的远程数据库表的 R 链接。

将 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 数据帧。