r - 如何在 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 代码这样更简单的事情也会很棒。

我曾希望 SQL translation在 dbplyr文档可能会有所帮助，但我不这么认为。

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))将执行查询而不返回结果(用于删除表、创建索引等)