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

bd1hkmkf  于 11个月前  发布在  其他
关注(0)|答案(3)|浏览(126)

我想计算一个数据库中的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一起使用。Winkler的sql代码,但我不知道从哪里开始。但即使是像直接从R对远程数据执行SQL代码这样简单的代码,太好了
我曾希望dbplyr文档中的SQL translation可能会有所帮助,但我不这么认为。

cvxl0en2

cvxl0en21#

你可以在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,它接受一个dbmgr表并返回生成该表的SQL代码
  • build_sql,它从字符串中组装查询。

您可以选择执行命令:

  • tbl(connection, sql(sql_query))将返回结果表
  • dbExecute(db_connection, as.character(sql_query))将执行查询而不返回结果(用于删除表,创建索引等)
dxxyhpgq

dxxyhpgq2#

或者,找到一种方法将SQL中的函数定义为用户定义的函数,然后您可以简单地使用该函数的名称,就像它是一个R函数一样(在dblog r查询中)。当R无法在本地找到该函数时,它只需将其传递给SQL后端,并假设它将是SQL中可用的函数。
这是一个很好的解耦逻辑的方法。缺点是dbm 0n1x表达式现在依赖于db-backend;你不能在本地数据集上运行来的代码。一种方法是创建一个模仿现有R函数的UDF。dplyr将使用本地R,dbplyr将使用SQL UDF。

uxhixvfz

uxhixvfz3#

你可以使用sql()来运行你提供的任何原始SQL。

示例

在这里,lubridate等价物在数据库后端上不起作用。
因此,我将自定义SQL代码sql("EXTRACT(WEEK FROM ildate)")放在sql()中,如下所示:

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

字符串
另一件事,这是搜索如何使用dbmgr渲染原始sql时的最佳搜索结果。如果有人从搜索中到达这里,答案是管道到dbplyr::sql_render()。从文档:
sql_build()创建一个select_query S3对象,该对象由sql_render()呈现为SQL字符串

相关问题