使用data.table::foverlaps实现员工项目任职时间区间截断的方法

实现代码 #

library(data.table)
library(lubridate)

# 生成原始数据集
df1<-data.table(id = rep(1:2,each=3),
                start_date = ymd(c("1998-04-03","1999-03-08","2000-08-13",
                                   "2005-03-03","2007-10-12","2014-02-23")),
                end_date = ymd(c("1999-03-07","2000-08-12","2021-04-23",
                                 "2007-09-05","2014-02-22","2019-05-04")),
                proj_id = c("A","B","A","B","C","A"))

df2 <- data.table(id = 1:2,
                  start_date = ymd("1998-07-20", "2006-06-12"),
                  end_date = ymd("1998-08-15", "2016-04-08"))

# 给df1加唯一行标识，用于后续区分重叠/非重叠行
df1[, row_id := .I]
# 设置关联键
setkey(df1, id, start_date, end_date)
setkey(df2, id, start_date, end_date)

# 匹配所有重叠区间
overlap_dt <- foverlaps(df2, df1, type = "any")

# 拆分重叠区间，保留有效片段
split_dt <- overlap_dt[, {
  # 拆分前半段：原区间开始到剔除区间前1天
  part1_start = start_date
  part1_end = i.start_date - days(1)
  # 拆分后半段：剔除区间后1天到原区间结束
  part2_start = i.end_date + days(1)
  part2_end = end_date
  
  # 仅保留开始时间<=结束时间的有效区间
  .(start_date = c(part1_start, part2_start),
    end_date = c(part1_end, part2_end),
    proj_id = rep(proj_id, 2))
}, by = .(id, row_id)][start_date <= end_date]

# 取出df1中未发生重叠的行
no_overlap_dt <- df1[!row_id %in% overlap_dt$row_id, .(id, start_date, end_date, proj_id)]

# 合并两部分结果并排序
result <- rbind(split_dt[, .(id, start_date, end_date, proj_id)], no_overlap_dt)
setorder(result, id, start_date)

输出结果和你预期完全一致：

id start_date   end_date proj_id
1:  1 1998-04-03 1998-07-19       A
2:  1 1998-08-16 1999-03-07       A
3:  1 1999-03-08 2000-08-12       B
4:  1 2000-08-13 2021-04-23       A
5:  2 2005-03-03 2006-06-11       B
6:  2 2016-04-09 2019-05-04       A

实现逻辑 #

• 给df1加唯一行号，快速区分是否和剔除区间重叠
• 对每一行重叠记录，尝试拆分出剔除区间前后两个片段，自动过滤掉无效的空区间（比如完全被剔除区间覆盖的记录拆分后会被直接丢弃，对应你案例里id=2的C项目任职记录）
• 最后合并拆分后的有效片段和无重叠的原始记录，排序得到最终结果

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