在R中无需合并两个data.table，如何统计匹配的唯一id2数量？

需求说明 #

现有两个百万行级别的data.table对象table1和table2，需为table1的每个id1统计满足以下条件的唯一id2数量：

• class、type字段完全匹配
• table1的number落在table2的min_number与max_number区间内

原通过全量合并后统计的方法因中间数据量过大，效率极低，需寻求无需全量合并的解决方案。

错误原因分析 #

你尝试的代码报错object 'id1' not found，是因为在table2[table1, ..., by = .(id1)]的语法中，by的作用域默认是左表table2，而id1仅存在于右表table1中，导致无法找到该变量。

高效解决方案 #

方案1：分组定向筛选（内存友好） #

先对table2按class+type预分组，避免重复全表扫描，再针对table1的每个唯一行计算匹配数：

library(data.table)

# 预分组table2，按class+type聚合id2和对应的区间列表
table2_grouped <- table2[, .(id2_list = list(id2), 
                             min_list = list(min_number), 
                             max_list = list(max_number)), 
                         by = .(class, type)]

# 为table1的每个唯一行计算匹配的唯一id2数量
table1[, nMatch := {
  # 匹配当前行的class+type分组
  matched_grp <- table2_grouped[class == .BY$class & type == .BY$type]
  if (nrow(matched_grp) == 0) {
    0
  } else {
    # 筛选区间包含当前number的id2，去重计数
    valid_idx <- which(matched_grp$min_list[[1]] <= number & matched_grp$max_list[[1]] >= number)
    uniqueN(matched_grp$id2_list[[1]][valid_idx])
  }
}, by = .(id1, class, type, number)]

# 去重得到最终结果
result <- unique(table1)

方案2：利用data.table连接+按行聚合（简洁高效） #

借助data.table的.EACHI参数，在连接时按table1的每一行直接聚合，不生成全量合并表：

# 连接并按table1每行聚合匹配的唯一id2数
temp <- table1[table2, on = c("class", "type", "number >= min_number", "number <= max_number"), 
               .(nMatch = uniqueN(id2)), by = .EACHI]

# 聚合相同id1的结果，并补充无匹配的行（设nMatch=0）
result <- table1[temp, on = .(id1, class, type, number), 
                 .(id1, class, type, number, nMatch = fifelse(is.na(nMatch), 0, nMatch))]
result <- unique(result)

验证结果 #

运行上述代码后，得到与期望一致的输出：

> result
    id1 class type number nMatch
1: 1324     1    A    1.0      2
2: 7822     1    A    2.5      2
3: 2324     1    A   98.0      1
4:   29     2    B  100.0      0
5: 9999     2    C   80.0      1
6: 1010     1    D   50.0      2

效率优势 #

两种方案均避免了百万行级别的全量合并，仅针对table1的每个唯一行/分组，定向筛选table2中匹配的子集进行计算，大幅降低内存占用与计算时间。若table2的class+type分组数量远小于总行数，方案1的效率提升会更显著。

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