将R的stringdist_inner_join模糊连接改写为Netezza可识别SQL的方法

结论 #

该需求完全可以实现，核心逻辑是用Netezza内置的编辑距离函数替换R侧的stringdist计算，把整个连接逻辑下压到数据库运行，性能远高于本地计算。 #

方案1：直接手写SQL查询（最快速适配） #

Netezza原生内置EDITDISTANCE()函数，默认计算Damerau-Levenshtein距离，和stringdist_inner_join默认使用的距离规则完全一致，直接对应你代码中max_dist=2的匹配要求。
你原来的R代码逻辑等价于如下SQL语句，直接传给sqlQuery即可运行：

library(RODBC)
con = odbcConnect("some name", uid = "some id", pwd = "abc")

# 构造符合Netezza语法的模糊连接查询语句
sample_sql <- "
SELECT t1.*, t2.*
FROM table_1 t1
INNER JOIN table_2 t2
  ON EDITDISTANCE(CAST(t1.id2 AS VARCHAR), CAST(t2.id2 AS VARCHAR)) <= 2
WHERE t1.date_1 >= t2.date_2
  AND t1.date_1 <= t2.date_3
"

sample_query <- sqlQuery(con, sample_sql)
View(sample_query)

注：加CAST转字符是为了避免id2字段为数值类型时编辑距离计算报错，若本身是字符类型可去掉该转换。

方案2：适配dplyr/dbplyr管道写法 #

如果要保留原来的dplyr风格写法，不需要手动写SQL，可以通过dbplyr注册Netezza的编辑距离函数，实现几乎零修改适配：

library(dplyr)
library(dbplyr)
library(RODBCDBI) # 建议用DBI系列包替代原生RODBC，适配性更好

# 建立数据库连接
con <- dbConnect(odbc::odbc(), dsn = "some name", uid = "some id", pwd = "abc")

# 注册Netezza内置的编辑距离函数到dbplyr翻译器
editdistance <- function(x, y) {
  sql_expr(EDITDISTANCE(!!x, !!y))
}

# 直接用管道写法写逻辑，计算会自动下压到Netezza侧运行
t1 <- tbl(con, "table_1")
t2 <- tbl(con, "table_2")

sample_query <- t1 %>%
  inner_join(t2, by = character()) %>% # 先做笛卡尔积后加距离过滤，等价于模糊内连接
  filter(editdistance(id2.x, id2.y) <= 2,
         date_1 >= date_2,
         date_1 <= date_3) %>%
  collect() # 只有collect的时候才把结果拉回本地
View(sample_query)

性能优化建议 #

如果两张表数据量较大，全表笛卡尔积计算编辑距离会比较慢，可以增加前置过滤条件缩小匹配范围：

• 先过滤两个id2字段的长度差不超过2（因为编辑距离不可能小于长度差）
• 或者先匹配两个id2的第一个/最后一个字符相同，再计算编辑距离，能减少90%以上的无效计算

内容的提问来源于stack exchange，提问作者stats_noob