R中如何加速非等值连接及关联值最大值计算？merge环节待优化

针对你千万级数据量下的个税计算需求，原代码中cut+merge的方案在merge环节效率不足，这里提供两种更高效的优化方案，均基于data.table的底层优化特性实现：

方案1：直接用非等值连接完成匹配+赋值 #

你之前尝试的非等值连接速度慢，大概率是写法不够高效。data.table的非等值连接经过底层优化，可直接完成「年份匹配+收入区间匹配」，无需额外生成Component列再做merge：

library(data.table)

# 原参数表与数据保持不变
parameters <- data.table("Component" = c("A","A","B","B","C","C"),
                         "Year" = c(2020, 2021, 2020, 2021,
                                    2020, 2021),
                         "Threshold_lower" = c(0,0,18000,18000,40000,50000),
                         "Threshold_upper" = c(18000,18000,40000,50000,Inf,Inf),
                         "Rate" = c(0,0,0.2,0.2,0.4,0.45),
                         "Tax paid (up to MTR)" = c(0,0,0,0,4400,6400)) 

taxation_data <- data.table("Year" = c(2020,2020,2021,2021),
                            "Income" = c(20000, 15000,80000,45000))

# 非等值连接：一次性匹配同年份下收入对应的税率参数
taxation_data[parameters, 
              on = .(Year, Income >= Threshold_lower, Income <= Threshold_upper),
              `:=`(Component = i.Component,
                   Rate = i.Rate,
                   `Tax paid (up to MTR)` = i.`Tax paid (up to MTR)`,
                   Threshold_lower = i.Threshold_lower)]

# 计算个税
taxation_data[, `Gross tax` := (Income - Threshold_lower) * Rate + `Tax paid (up to MTR)`]

方案2：分组+findInterval快速匹配 #

利用findInterval的C级执行效率，结合按年份分组，完全避免连接操作，直接在原数据表内完成参数匹配：

library(data.table)

parameters <- data.table("Component" = c("A","A","B","B","C","C"),
                         "Year" = c(2020, 2021, 2020, 2021,
                                    2020, 2021),
                         "Threshold_lower" = c(0,0,18000,18000,40000,50000),
                         "Threshold_upper" = c(18000,18000,40000,50000,Inf,Inf),
                         "Rate" = c(0,0,0.2,0.2,0.4,0.45),
                         "Tax paid (up to MTR)" = c(0,0,0,0,4400,6400)) 

taxation_data <- data.table("Year" = c(2020,2020,2021,2021),
                            "Income" = c(20000, 15000,80000,45000))

# 按年份拆分参数表，方便分组调用
param_list <- split(parameters, by = "Year")

# 按年份分组，用findInterval快速定位对应参数
taxation_data[, c("Component", "Rate", "Threshold_lower", "Tax paid (up to MTR)") := {
  current_param <- param_list[[as.character(Year[1])]]
  # 找到每个收入对应的参数行索引
  idx <- findInterval(Income, current_param$Threshold_upper, left.open = TRUE) + 1
  current_param[idx, .(Component, Rate, Threshold_lower, `Tax paid (up to MTR)`)]
}, by = Year]

# 计算个税
taxation_data[, `Gross tax` := (Income - Threshold_lower) * Rate + `Tax paid (up to MTR)`]

原代码效率瓶颈说明 #

原方案中lapply+cut生成Component后再做merge，merge操作需要对两个表按Year+Component排序，千万级数据下排序开销极大；而上述两种方案要么直接用非等值连接跳过中间列生成，要么用分组+快速匹配完全避免连接，能大幅提升处理速度。

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