如何在R中基于多条件左连接两个百万级数据集？

现有两个约180万行的数据集：

• Dataset1：秒级连续时间序列数据，包含字段Col2_、Col3_、DateTime_、Value_
• Dataset2：时间间隔为1/2/3秒的非连续数据，包含字段Col2、Col3、DateTime、Value

需要执行以Dataset2为左表的左连接，匹配规则：

1. 首先匹配分组键：Col2 == Col2_且Col3 == Col3_
2. 同一分组内，为Dataset2的每条DateTime匹配Dataset1中最接近的DateTime_（支持精确匹配及非精确的最近时间匹配）

示例数据 #

# Dataset1
Col2_ Col3_ DateTime_           Value_ 
1     1     2025-07-21 10:00:01 199 
1     1     2025-07-21 10:00:02 198 
1     1     2025-07-21 10:00:03 199 
1     1     2025-07-21 10:00:04 196 
... 
2     1     2025-07-21 11:01:00 200 
2     1     2025-07-21 11:01:01 205 
...

# Dataset2
Col2 Col3 DateTime            Value
1    1    2025-07-21 10:00:01 199
1    1    2025-07-21 10:00:03 199
1    1    2025-07-21 10:00:04 196
...
2    1    2025-07-21 11:01:00 200
2    1    2025-07-21 11:01:04 210
...

已尝试方法及问题 #

• 使用fuzzyjoin包：因数据量过大触发内存不足错误，提示'cannot allocate vector size ...'
• 使用data.table的roll="nearest"参数：执行连接后丢失.on中指定的匹配列，代码如下：

merged <- Dataset1[Dataset2, on = .('Col2_' = 'Col2', 'Col3_' = 'Col3', 'DateTime_' = 'DateTime'), roll = "nearest"]

• 使用dplyr::left_join：仅支持精确匹配，无法实现基于时间的最近匹配逻辑

期望合并结果 #

# Merged dataset
Col2 Col3 DateTime            Value  Col2_ Col3_ DateTime_           Value_ 
1    1    2025-07-21 10:00:01 199    1     1     2025-07-21 10:00:01 199 
1    1    2025-07-21 10:00:03 199    1     1     2025-07-21 10:00:03 199 
1    1    2025-07-21 10:00:04 196    1     1     2025-07-21 10:00:04 196 
2    1    2025-07-21 11:01:00 200    2     1     2025-07-21 11:01:00 200 
2    1    2025-07-21 11:01:04 210    2     1     2025-07-21 11:01:04 205

解决方案建议 #

1. 修正data.table连接逻辑（推荐） #

data.table的X[Y]语法中，X是左表、Y是右表，之前的错误是颠倒了表的位置；同时需确保时间字段为POSIXct类型，并按分组键排序以保证roll逻辑生效。

步骤如下：

library(data.table)
# 转换为data.table格式并处理时间字段
setDT(Dataset1)
setDT(Dataset2)
Dataset1[, DateTime_ := as.POSIXct(DateTime_)]
Dataset2[, DateTime := as.POSIXct(DateTime)]

# 按分组键+时间排序，确保roll逻辑正确执行
setkey(Dataset1, Col2_, Col3_, DateTime_)
setkey(Dataset2, Col2, Col3, DateTime)

# 执行左连接（Dataset2为左表），保留所有字段
merged <- Dataset2[Dataset1, on = .(Col2 = Col2_, Col3 = Col3_, DateTime = DateTime_), roll = "nearest", nomatch = NA]

2. 分组逐批处理（内存友好） #

若内存仍紧张，可按Col2+Col3拆分分组，逐组匹配后合并，减少单批次处理的数据量：

# 获取所有唯一分组
groups <- unique(Dataset2[, .(Col2, Col3)])

# 逐组处理
merged_list <- lapply(1:nrow(groups), function(i) {
  current_group <- groups[i]
  # 筛选当前分组的子数据集
  d2_sub <- Dataset2[Col2 == current_group$Col2 & Col3 == current_group$Col3]
  d1_sub <- Dataset1[Col2_ == current_group$Col2 & Col3_ == current_group$Col3]
  # 执行当前分组的时间匹配
  d2_sub[d1_sub, on = .(DateTime = DateTime_), roll = "nearest"]
})

# 合并所有分组结果
merged <- rbindlist(merged_list)

3. 精确匹配优先，补全最近匹配 #

如果大部分时间点为精确匹配，可先执行精确连接，再单独处理未匹配的行：

library(dplyr)
# 先执行精确左连接
exact_merge <- left_join(Dataset2, Dataset1, by = c("Col2" = "Col2_", "Col3" = "Col3_", "DateTime" = "DateTime_"))

# 筛选未匹配的行，用data.table执行最近匹配
unmatched <- exact_merge[is.na(Value_), ]
setDT(unmatched)
setDT(Dataset1)
unmatched_merged <- unmatched[Dataset1, on = .(Col2 = Col2_, Col3 = Col3_, DateTime = DateTime_), roll = "nearest"]

# 合并精确匹配和补全的结果
merged <- rbind(exact_merge[!is.na(Value_), ], unmatched_merged)

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