如何用R按shop_id分组计算去重后的会话总运行时长？

需求说明 #

• 按shop_id汇总各门店的会话总运行时长（拆分小时和分钟）
• 统计规则：
  • 完全并发的会话仅计算一次，例如两个9:00-12:00的会话总时长为3小时
  • 重叠会话的延伸部分需计入，例如9:00-12:00与9:15-12:15的总时长为3小时15分钟

示例数据 #

# 示例数据集
df <- data.frame(shop_id = c("1","1","2","2","2","3","3","3","4","5"),
                 session_start_time = c("09:00","12:00","09:00","09:15","13:00","09:00"
                                ,"09:00","13:00","08:30","09:30"), 
                 session_end_time = c("12:00","16:00","12:00","12:15","16:00","12:00"
                                ,"12:00","15:00","11:30","12:30")
                 )

解决方案 #

通过时间格式转换→分组合并重叠区间→计算总时长的步骤实现需求：

步骤1：将时间转换为分钟数 #

把字符串格式的时间转换成从0点开始的分钟数，方便后续计算：

library(dplyr)

df <- df %>%
  mutate(
    start_min = as.numeric(substr(session_start_time, 1, 2)) * 60 + as.numeric(substr(session_start_time, 4, 5)),
    end_min = as.numeric(substr(session_end_time, 1, 2)) * 60 + as.numeric(substr(session_end_time, 4, 5))
  )

步骤2：分组合并重叠/并发的时间区间 #

对每个门店的会话区间排序后，合并重叠或包含的区间：

merge_intervals <- function(intervals) {
  # 按开始时间排序
  intervals <- intervals[order(intervals$start_min), ]
  
  if(nrow(intervals) == 0) return(data.frame(start_min = numeric(0), end_min = numeric(0)))
  
  # 初始化合并后的第一个区间
  merged <- intervals[1, ]
  
  for(i in 2:nrow(intervals)) {
    current <- intervals[i, ]
    last_merged <- merged[nrow(merged), ]
    
    # 如果当前区间与最后一个合并区间重叠，更新结束时间为较大值
    if(current$start_min <= last_merged$end_min) {
      merged[nrow(merged), "end_min"] <- max(last_merged$end_min, current$end_min)
    } else {
      # 不重叠则添加新的区间
      merged <- rbind(merged, current)
    }
  }
  
  return(merged)
}

# 按shop_id分组并合并区间
merged_df <- df %>%
  group_by(shop_id) %>%
  do(merge_intervals(.)) %>%
  ungroup()

步骤3：计算总时长并拆分为小时和分钟 #

对每个门店的合并区间求和总分钟数，再转换为小时和分钟：

result <- merged_df %>%
  group_by(shop_id) %>%
  summarise(total_min = sum(end_min - start_min)) %>%
  mutate(
    Hours = total_min %/% 60,
    Mins = total_min %% 60
  ) %>%
  select(shop_id, Hours, Mins)

# 输出结果
print(result, row.names = FALSE)

运行结果 #

shop_id Hours Mins 
      1     7     0
      2     6    15
      3     5     0
      4     3     0
      5     3     0

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