如何在R中基于列值实现带区间条件的表连接

需求与对应SQL #

需要将基础表（DF1）与查找表（TIERLKP）按以下条件关联：

• 等值匹配：DF1$PERIL = TIERLKP$PERIL、DF1$COVERAGE = TIERLKP$COVERAGE、DF1$LocStCd = TIERLKP$ATTRIBUTE_1
• 范围匹配：DF1$Score > TIERLKP$ATTRIBUTE_2 且 DF1$Score <= TIERLKP$ATTRIBUTE_3

对应的SQL语句：

SELECT base.PolicyNo
    ,base.Score
    ,lkp.value ScoreFactor
FROM base base
INNER JOIN lkp lkp
    ON base.PERIL = lkp.PERIL
    AND base.COVERAGE = lkp.COVERAGE
    AND base.LocStCd = lkp.ATTRIBUTE_1
    AND base.Score > lkp.ATTRIBUTE_2
    AND base.Score <= lkp.ATTRIBUTE_3

一、data.table 正确实现 #

data.table支持原生非等值连接，但语法有特定要求，之前的写法错误在于将范围条件直接放入on=的字符串向量中，正确写法如下：

写法1：先关联再过滤 #

library(data.table)

# 转换为data.table格式（如果还不是的话）
setDT(DF1)
setDT(TIERLKP)

# 执行连接：先做等值匹配，再过滤范围条件
result_dt <- TIERLKP[DF1,
                     on = c("PERIL", "COVERAGE", "ATTRIBUTE_1" = "LocStCd"),
                     .(PolicyNo = i.PolicyNo, Score = i.Score, ScoreFactor = value),
                     nomatch = 0L  # 对应SQL的INNER JOIN，丢弃无匹配的行
                     ][Score > ATTRIBUTE_2 & Score <= ATTRIBUTE_3]

写法2：直接在on=中指定所有条件（更高效） #

result_dt <- DF1[TIERLKP,
                 on = c("PERIL", "COVERAGE", "LocStCd" = "ATTRIBUTE_1",
                        "Score > ATTRIBUTE_2", "Score <= ATTRIBUTE_3"),
                 .(PolicyNo, Score, ScoreFactor = value),
                 nomatch = 0L]

注意：如果查找表中存在多个符合条件的行，可能需要添加allow.cartesian = TRUE参数（根据实际数据情况调整）；另外你之前代码中的TOTALTIERSCOREVA是笔误，应该是Score。

二、dplyr 正确实现 #

dplyr的传统left_join/inner_join仅支持等值连接，要实现非等值连接有两种方案：

方案1：先等值连接再过滤（兼容所有dplyr版本） #

library(dplyr)

result_dplyr <- DF1 %>%
  # 先做等值匹配
  inner_join(TIERLKP, by = c("PERIL", "COVERAGE", "LocStCd" = "ATTRIBUTE_1")) %>%
  # 过滤范围条件
  filter(Score > ATTRIBUTE_2, Score <= ATTRIBUTE_3) %>%
  # 保留需要的列
  select(PolicyNo, Score, ScoreFactor = value)

如果担心重复行过多影响性能，可以先给查找表加索引，或者先筛选出可能符合范围的查找表行再连接。

方案2：使用join_by原生非等值连接（dplyr 1.1.0+版本） #

新版本dplyr支持通过join_by()直接定义所有连接条件，包括非等值条件：

result_dplyr <- DF1 %>%
  inner_join(TIERLKP,
             join_by(PERIL == PERIL,
                     COVERAGE == COVERAGE,
                     LocStCd == ATTRIBUTE_1,
                     Score > ATTRIBUTE_2,
                     Score <= ATTRIBUTE_3)) %>%
  select(PolicyNo, Score, ScoreFactor = value)

之前的错误在于：left_join的by参数仅接受等值连接的列映射，不能放入比较运算符，因此会报“Join columns must be present in data.”错误。

性能优化提示 #

• 数据量较大时，优先选择data.table方案，其非等值连接的性能显著优于dplyr
• 给查找表的等值匹配列加索引能大幅提升速度：

  # data.table加索引
  setkey(TIERLKP, PERIL, COVERAGE, ATTRIBUTE_1)
  
  # dplyr加索引（转换为因子类型）
  TIERLKP <- TIERLKP %>%
    mutate(across(c(PERIL, COVERAGE, ATTRIBUTE_1), as.factor))
  

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