如何用data.table实现按日期范围关联两表并汇总计数字段

首先需要将所有日期字段转换为Date类型，否则无法进行有效的区间比较：

# 转换日期格式
DT1[, work_day := as.Date(work_day, format = "%Y/%m/%d")]
DT2[, `:=`(date_start = as.Date(date_start, format = "%Y/%m/%d"),
           date_end = as.Date(date_end, format = "%Y/%m/%d"))]

接下来利用data.table的**非等连接（non-equi join）**特性实现需求：按city和squad精确匹配，同时确保DT1的日期落在DT2的日期区间内，最后对匹配到的计数字段求和。通过左连接回原DT1，处理无匹配的情况并去重，得到预期结果：

# 非等连接+分组汇总，左连接回DT1
result <- DT2[DT1, on = .(city, squad, date_start <= work_day, date_end >= work_day),
              .(work_day, city, squad, Counter_A = sum(count_A, na.rm = TRUE), Counter_B = sum(count_B, na.rm = TRUE)),
              by = .EACHI][, `:=`(date_start = NULL, date_end = NULL)]

# 去重（匹配后保留DT1的唯一行）
result <- unique(result)

# 确保无匹配项的计数为0
result[is.na(Counter_A), `:=`(Counter_A = 0, Counter_B = 0)]

# 输出结果
print(result)

运行后输出结果：

work_day city squad Counter_A Counter_B
1: 2022-01-05   NY     a         3         4
2: 2022-01-06   NY     b         1         3
3: 2022-01-09   LA     b         0         0
4: 2022-01-07   NY     a         1         1
5: 2022-01-03   NY     a         2         3

核心逻辑解释 #

• DT2[DT1, on = ...]：以DT1为基准进行右连接，确保每一行DT1都能被处理
• on = .(city, squad, date_start <= work_day, date_end >= work_day)：定义匹配规则：city/squad完全匹配，且DT1的日期在DT2的date_start与date_end之间
• by = .EACHI：针对DT1的每一行单独执行匹配和求和，保证每行都能得到对应的汇总值
• sum(..., na.rm = TRUE)：避免无匹配时返回NA，直接返回0

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