R使用fuzzy_inner_join实现多字段日期范围匹配连接报错

问题场景 #

• 待关联的两个数据框属性：
  • 数据框a：共170万条记录，含非唯一标识字段numbera、日期区间起始字段date1、日期区间截止字段date2，多数为分析不需要的冗余数据
  • 数据框b：共1600条记录，含同类型标识字段numberb、单日期字段datex
• 关联规则（内连接）：
  1. 标识字段值相等：numberb == numbera
  2. b表的datex落在a表对应记录的[date1, date2]闭区间内
• 测试数据构造代码：

a <- data.frame(
  numbera=c('1','2','3','1'),
  date1=as.Date(c('10/04/2021','21/06/2021','02/10/2021','17/02/2022'), format="%d/%m/%Y"),
  date2=as.Date(c('10/07/2021','21/09/2021','02/01/2022','17/05/2022'), format="%d/%m/%Y")
)

b <- data.frame(
  numberb=c('1','2','2','3','1'),
  datex=as.Date(c('16/05/2021','01/08/2021','03/08/2021','02/09/2021','17/03/2022'), format="%d/%m/%Y")
)

• 预期输出：编号3因日期不匹配被过滤，编号2因b表存在2条符合区间要求的记录返回2条结果，对应结果构造代码：

c <- data.frame(
  numberb=c('1','2','2','1'),
  datex=as.Date(c('16/05/2021','01/08/2021','03/08/2021','17/03/2022'), format="%d/%m/%Y"),
  numbera=c('1','2','2','1'),
  date1=as.Date(c('10/04/2021','21/06/2021','21/06/2021','17/02/2022'), format="%d/%m/%Y"),
  date2=as.Date(c('10/07/2021','21/09/2021','21/09/2021','17/05/2022'), format="%d/%m/%Y")
)

• 初始实现代码及报错：

# 初始代码
Joined <- fuzzy_inner_join(b, a, by = c("numberb"="numbera", "datex"="date1", "datex"="date2"),
                           match_fun = list("=", ">=", "<="))

# 报错信息
Error in which(m) : argument to 'which' is not logical

报错原因 #

fuzzy_inner_join的match_fun参数要求传入与by参数列对一一对应的函数对象列表，初始代码存在两个问题：

1. 把比较运算符用引号包裹成了字符串，传入后无法执行逻辑判断，返回值不是布尔类型，触发which()参数类型错误
2. 标识符相等判断误用了赋值运算符=，R中相等比较的运算符为==

实现方案 #

方案1：修复fuzzyjoin参数（小数据量适用） #

去掉match_fun中运算符的引号，将赋值符=替换为相等比较符==即可，代码如下：

# 未安装包先运行 install.packages("fuzzyjoin")
library(fuzzyjoin)

Joined <- fuzzy_inner_join(
  b, a, 
  by = c("numberb"="numbera", "datex"="date1", "datex"="date2"),
  match_fun = list(`==`, `>=`, `<=`)
)

方案2：精确匹配预过滤（适配170万条大数据量场景，推荐） #

纯模糊join会先对两表做笛卡尔积再判断匹配规则，170万条记录的计算量会超过270亿次，运行极慢甚至内存溢出。推荐先用标识符做精确内连接，直接筛掉所有标识不匹配的冗余数据（通常能过滤99%以上的无效记录），再做日期区间判断，性能可提升数十倍，秒级返回结果：

# 未安装包先运行 install.packages("dplyr")
library(dplyr)

Joined_opt <- b %>%
  # 第一步：按标识符做精确内连接，过滤绝大多数冗余数据
  inner_join(a, by = c("numberb" = "numbera")) %>%
  # 第二步：筛选日期落在目标区间内的记录
  filter(datex >= date1, datex <= date2)

两种方案的运行结果和预期输出完全一致。

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