R语言基于另一data.table条件查询为目标data.table新增列

你之前的宽表转长表思路是正确的，只是缺少了「天数区间」这一匹配维度，补充后通过data.table的非等值连接即可实现批量匹配，无需单独按date分组。

步骤1：处理利率表为带匹配区间的长表 #

library(data.table)
# 转换宽表为长表
melted_rates <- melt(rates_dt, id.vars = "index", variable.name = "term", value.name = "rate")
# 给每个期限匹配对应的天数区间，可根据实际业务规则调整区间阈值
melted_rates[, `:=`(
  min_days = fcase(
    term == "1_MO", 0,
    term == "2_MO", 30,
    term == "3_MO", 60,
    term == "6_MO", 180,
    term == "1_YR", 365,
    term == "2_YR", 730,
    term == "3_YR", 1095
  ),
  max_days = fcase(
    term == "1_MO", 29,
    term == "2_MO", 59,
    term == "3_MO", 179,
    term == "6_MO", 364,
    term == "1_YR", 729,
    term == "2_YR", 1094,
    term == "3_YR", Inf
  )
)]

步骤2：非等值连接匹配利率 #

通过日期、天数区间两个维度同时关联，直接给dt新增rates列：

dt[melted_rates, on = .(date == index, days_remaining >= min_days, days_remaining <= max_days), rates := i.rate]

最终输出结果 #

运行后dt的内容完全符合预期：

date   exp_date days_remaining year_remaining rates
1: 2021-10-04 2021-10-15             11     0.03013699  0.09
2: 2021-10-04 2022-01-21            109     0.29863014  0.04
3: 2021-10-04 2023-01-20            473     1.29589041  0.09

该方案支持全量日期的批量匹配，性能优异，适合大业务量场景使用。

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