如何用dplyr或SQL为连续时间区间添加首个区间ID作为分组标识

我有如下格式的数据集：

a = data.frame( id = c(1:5),
                time_left = c('2010-01-04', '2010-02-15', 
                              '2010-03-01', '2010-03-08', 
                              '2010-03-15'), 
                time_right = c('2010-01-28', '2010-03-02', 
                               '2010-03-07', '2010-03-14', 
                               '2010-03-23'))

需要为连续重叠/衔接的时间区间添加组ID，组ID为该组首个时间区间的id。由于主数据集规模极大，需要高效的解决方案。

输入示例 #

id  time_left time_right
1   2010-01-04 2010-01-28
2   2010-02-15 2010-03-02
3   2010-03-01 2010-03-07
4   2010-03-08 2010-03-14
5   2010-03-15 2010-03-23

期望输出示例 #

id  time_left time_right   group_id
1   2010-01-04 2010-01-28   1
2   2010-02-15 2010-03-02   1
3   2010-03-01 2010-03-07   3
4   2010-03-08 2010-03-14   3
5   2010-03-15 2010-03-23   3

针对大规模数据集，推荐使用data.table（内存效率与速度最优）或dplyr的向量化操作，避免低效循环。

方法1：data.table 实现 #

library(data.table)

# 转换为data.table并将时间列转为Date类型
setDT(a)[, c("time_left", "time_right") := lapply(.SD, as.Date), .SDcols = c("time_left", "time_right")]

# 计算分组标记与组ID
a[, max_right := cummax(time_right)]
a[, new_group := shift(max_right, fill = as.Date("1970-01-01")) < time_left]
a[, group_id := id[1], by = .(cumsum(new_group))]

# 移除中间变量（可选）
a[, c("max_right", "new_group") := NULL]

方法2：dplyr 实现 #

library(dplyr)

a %>%
  mutate(across(c(time_left, time_right), as.Date)) %>%
  mutate(
    max_right = cummax(time_right),
    new_group = lag(max_right, default = as.Date("1970-01-01")) < time_left,
    group_num = cumsum(new_group)
  ) %>%
  group_by(group_num) %>%
  mutate(group_id = first(id)) %>%
  ungroup() %>%
  select(-max_right, -new_group, -group_num)

核心原理 #

1. 先将时间列转为Date类型，确保时间比较逻辑正确
2. 计算cummax(time_right)：到当前行为止的最大时间右边界，用于判断后续区间是否与前序组重叠/衔接
3. 标记new_group：当当前区间左边界大于前序组的最大右边界时，标记为新组
4. 通过cumsum(new_group)生成组编号，再按组提取首个区间的id作为group_id

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