在R的data.table中实现带日期容错的交易匹配

核心思路 #

给Quicken和银行的交易表分别标记来源，基于金额相等 + 日期差在±5天内的条件完成匹配，最后筛选出无对应匹配项的交易。

一、data.table 实现（性能最优，推荐） #

方法1：foverlaps 区间匹配 #

1. 给两个表添加日期区间（当前日期±5天）并标记来源
2. 按金额+日期区间执行重叠连接，找到匹配交易
3. 筛选出未匹配的记录

library(data.table)

# 假设quicken_dt、bank_dt均包含date(Date类型)、amount列
quicken_dt[, `:=`(source = "quicken", start_date = date - 5, end_date = date + 5)]
bank_dt[, source := "bank"]

# 设置连接键
setkey(bank_dt, amount, date, date)
setkey(quicken_dt, amount, start_date, end_date)

# 执行重叠连接，获取匹配项
matched <- foverlaps(quicken_dt, bank_dt, type = "within", nomatch = NULL)

# 生成唯一ID并收集匹配ID
quicken_dt[, id := .I]
bank_dt[, id := .I]
matched_quicken_ids <- unique(matched$id)
matched_bank_ids <- unique(matched$id.1)

# 筛选未匹配交易
unmatched_quicken <- quicken_dt[!id %in% matched_quicken_ids]
unmatched_bank <- bank_dt[!id %in% matched_bank_ids]
unmatched_all <- rbindlist(list(unmatched_quicken, unmatched_bank))

# 清理临时列
unmatched_all[, c("start_date", "end_date", "id") := NULL]

方法2：双向滚动连接 #

先从Quicken匹配银行，再反向验证，取无匹配的记录：

library(data.table)

setDT(quicken_dt)[, source := "quicken"]
setDT(bank_dt)[, source := "bank"]

# 生成唯一ID
quicken_dt[, id := .I]
bank_dt[, id := .I]

# 设置键：金额+日期，允许±5天滚动匹配
setkey(quicken_dt, amount, date)
setkey(bank_dt, amount, date)

# 双向匹配
quicken_matched <- quicken_dt[bank_dt, roll = -5:5, nomatch = NULL]
bank_matched <- bank_dt[quicken_dt, roll = -5:5, nomatch = NULL]

# 收集所有匹配ID
all_matched_ids <- unique(c(quicken_matched$id, bank_matched$id.1))

# 筛选未匹配项
unmatched_all <- rbindlist(list(quicken_dt, bank_dt))[!id %in% all_matched_ids]

二、tidyverse 实现 #

借助fuzzyjoin包简化模糊连接逻辑：

library(tidyverse)
library(fuzzyjoin)

# 标记来源并生成唯一ID
quicken_df <- quicken_df %>% mutate(source = "quicken", id = row_number())
bank_df <- bank_df %>% mutate(source = "bank", id = row_number())

# 模糊匹配：金额相等，日期差≤5天
matched <- fuzzy_inner_join(
  quicken_df,
  bank_df,
  by = c("amount" = "amount", "date" = "date"),
  match_fun = list(`==`, function(x, y) abs(x - y) <= 5)
)

# 收集匹配ID
matched_quicken_ids <- unique(matched$id.x)
matched_bank_ids <- unique(matched$id.y)

# 筛选未匹配交易
unmatched_quicken <- quicken_df %>% filter(!id %in% matched_quicken_ids)
unmatched_bank <- bank_df %>% filter(!id %in% matched_bank_ids)
unmatched_all <- bind_rows(unmatched_quicken, unmatched_bank)

三、base R 实现 #

无需额外包，用merge+条件筛选完成：

# 标记来源并生成唯一ID
quicken_df$source <- "quicken"
quicken_df$id <- seq(nrow(quicken_df))
bank_df$source <- "bank"
bank_df$id <- seq(nrow(bank_df))

# 按金额合并后筛选日期差符合条件的匹配项
merged <- merge(quicken_df, bank_df, by = "amount", suffixes = c("_q", "_b"))
matched <- merged[abs(merged$date_q - merged$date_b) <= 5, ]

# 收集匹配ID
matched_q_ids <- unique(matched$id_q)
matched_b_ids <- unique(matched$id_b)

# 筛选未匹配交易
unmatched_quicken <- quicken_df[!quicken_df$id %in% matched_q_ids, ]
unmatched_bank <- bank_df[!bank_df$id %in% matched_b_ids, ]
unmatched_all <- rbind(unmatched_quicken, unmatched_bank)

注意事项 #

• 日期列必须是Date类型，若不是请先用as.Date()转换
• 可根据实际对账需求调整日期差阈值（比如将5改为3）
• 若存在多笔金额相同、日期差在阈值内的交易，建议补充备注、交易类型等字段辅助匹配，避免误判

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