如何在dbplyr中使用自定义SQL函数？

Question

我希望在数据库中计算Jaro-Winkler字符串距离。如果我将数据导入R（使用collect），我可以轻松使用stringdist包中的stringdist函数。

但我的数据非常大，我想在将数据导入R之前对Jaro-Winkler距离进行过滤。

有Jaro-Winkler的SQL代码（https://androidaddicted.wordpress.com/2010/06/01/jaro-winkler-sql-code/和适用于T-SQL的版本），但我不确定如何最好地让该SQL代码与dbplyr一起使用。我很乐意尝试将stringdist函数映射到Jaro-Winkler sql代码中，但我不知道从哪里开始。但即使是像直接在远程数据上从R执行SQL代码这样更简单的事情也会很棒。

我曾希望dbplyr文档中的SQL翻译可以帮助，但我不认为会有所帮助。

- jfeigenbaum

---

请问您能否提供一个包含样例数据代码的最小完整可验证示例（MCVE）？https://stackoverflow.com/help/mcve - Hack-R

你可以直接进行SQL调用，然后在导入数据后使用dplyr吗？ - Dason

Answer 1

你可以在R中构建自己的SQL函数。它们只需要生成一个有效的SQL查询字符串即可。我不知道Jaro-Winkler距离，但是我可以为你提供一个示例来构建：

union_all = function(table_a,table_b, list_of_columns){
  # extract database connection
  connection = table_a$src$con

  sql_query = build_sql(con = connection,
                      sql_render(table_a),
                      "\nUNION ALL\n",
                      sql_render(table_b)
  )

  return(tbl(connection, sql(sql_query)))
}

unioned_table = union_all(table_1, table_2, c("who", "where", "when"))

这里有两个关键命令:

• sql_render, 用于获取dbplyr表并返回生成它的SQL代码。
• build_sql, 用于将字符串组合成查询语句。

您可以选择以下执行命令：

• tbl(connection, sql(sql_query)) 将返回结果表格。
• dbExecute(db_connection, as.character(sql_query)) 将执行一个查询并不返回结果（用于删除表格、创建索引等操作）。

- Simon.S.A.

---

我已经尝试了您的解决方案，它有效。但是有一件事困扰着我：第一次通过 table_a <-tbl(con, "table_a") 连接到数据库时，dbplyr 需要执行一个虚拟查询，即 "select * from table_a where 0 = 1"。如果我现在在 sql_render/build_sql 中使用 R 对象 table_a，它将再次执行相同类型的 WHERE 0 = 1 查询。有没有办法防止 dbplyr 这样做，而是重用第一个 tbl(...) 调用中的元数据？ - Yunus King

我曾经看到dbplyr生成包含“WHERE 0 = 1”的SQL查询，但它们从未影响过我的自定义SQL函数。这可能是由于您连接到SQL表的方式，或者因为您调用了render_sql(tbl(con, "table_a"))而不是在调用render_sql(tbl_a)之前先分配tbl_a = tbl(con, "table_a")。但是根据您提供的细节，我无法确定。考虑开一个新问题并在此处发布链接-我可以进行更详细的查看。 - Simon.S.A.

Answer 2

您可以使用sql()来运行任何原始SQL。

示例

这里，lubridate的等效方法在数据库后端上不起作用。

因此，我将自定义SQL代码sql("EXTRACT(WEEK FROM ildate)")放入sql()中，如下所示：

your_dbplyr_object %>%
  mutate(week = sql("EXTRACT(WEEK FROM meeting_date)"))

- stevec

Answer 3

或者，找到一种方法在SQL中定义函数作为用户定义的函数，然后您可以像使用R函数一样（在dbplyr查询中）使用该函数的名称。当R无法在本地找到该函数时，它会将其传递给SQL后端，并假定它是在SQL领域可用的函数。

这是一种很好的解耦逻辑的方法。缺点是dbplyr表达式现在依赖于db后端；您无法在本地数据集上运行相同的代码。解决这个问题的一种方法是创建一个模仿现有R函数的UDF。dplyr将使用本地R，而dbplyr将使用SQL UDF。

- dsz