如何在data.table中实现非等值连接并提取匹配值的最小/最大值

需要在data.table中对两个表执行id匹配+时间范围匹配的非等值连接，提取每个dtA行对应的dtB中符合start <= time < end条件的time的最小/最大值，同时要避免全表展开以适配百万级数据规模。

示例数据 #

set.seed(42)
dtA <- data.table(id=rep(c("A","B"),each=3), start=rep(1:3, times=2), end=rep(2:4, times=2))
dtB <- data.table(id=rep(c("A","B"),times=20), time=sort(runif(40, 1, 4)))

问题分析 #

你之前尝试的两种方法存在核心问题：

• 第一种方法使用mult="first"，会忽略每个dtA行对应的所有匹配dtB行，直接取全局的min/max，结果不符合预期。
• 第二种方法使用by=.EACHI，但非等值连接中start <= time会覆盖dtA的start列，直接调用time实际引用的是被覆盖后的列，而非dtB的原始time值，导致只能得到单个值。

data.table 解决方案 #

方法一：明确引用右表列 #

使用i.time明确引用dtB的time列，结合by=.EACHI按dtA的每一行聚合匹配的dtB行：

dtA_result <- copy(dtA)
dtA_result[dtB, c("Min", "Max") := .(min(i.time), max(i.time)),
    on = .(id, start <= time, end > time),
    by = .EACHI]

执行后得到预期结果：

#        id start   end      Min      Max
#    <char> <int> <int>    <num>    <num>
# 1:      A     1     2 1.011845 1.858419
# 2:      A     2     3 2.170610 2.970977
# 3:      A     3     4 3.115194 3.934679
# 4:      B     1     2 1.022002 1.766286
# 5:      B     2     3 2.164325 2.925237
# 6:      B     3     4 3.055509 3.966675

方法二：反转连接方向 #

从dtB出发连接dtA，按dtA的行聚合，避免列覆盖问题：

result <- dtB[dtA, .(Min = min(time), Max = max(time)),
    on = .(id, time >= start, time < end),
    by = .EACHI]

性能说明 #

两种方法均基于data.table的高效非等值连接+分组聚合，不会展开全量匹配数据，能够适配40万行dtA和200万行dtB的规模。

collapse 包解决方案 #

方案一：非等值连接+聚合 #

library(collapse)

# 执行非等值连接
joined <- fmerge(dtA, dtB, by.x = "id", by.y = "id",
                 condition = ~ start <= time & end > time)

# 按dtA核心列分组聚合
result_collapse <- fsummarise(joined, 
                              Min = fmin(time), 
                              Max = fmax(time), 
                              by = c("id", "start", "end"))

方案二：分组范围筛选（避免全表展开） #

library(collapse)

# 按id分组提取dtB的time序列
time_list <- split(dtB$time, dtB$id)

# 对dtA每行筛选对应id的时间范围并求最值
dtA_collapse <- fmutate(dtA,
                        Min = sapply(seq_len(nrow(dtA)), function(i) {
                          fmin(fsubset(time_list[[id[i]]], start[i] <= time & time < end[i]))
                        }),
                        Max = sapply(seq_len(nrow(dtA)), function(i) {
                          fmax(fsubset(time_list[[id[i]]], start[i] <= time & time < end[i]))
                        })
)

后者利用分组后的序列筛选，避免了全表连接，性能更优。

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