如何计算每日无故障时长？多类别故障时间重叠处理

核心思路 #

要解决多类别故障时间重叠的问题，关键是先合并同一天内所有重叠的故障区间，再计算总故障时长，最后用24小时减去故障时长得到无故障时长。同时需要考虑故障跨天的情况（比如故障从当天深夜到次日凌晨）。

完整R代码实现 #

library(tidyverse)
library(lubridate)

# 1. 生成示例数据集（用户提供）
df_time <- tibble(
  Category = c("A", "A", "B", "C", "A", "A", "C", "A"),
  Begin = as_datetime(c(
    "2023-07-15 01:40:11",
    "2023-07-16 05:54:44",
    "2023-08-16 07:43:09",
    "2023-08-16 12:00:00",
    "2023-08-16 18:00:00",
    "2023-08-17 08:00:00",
    "2023-08-17 11:12:45",
    "2023-08-17 19:01:45"
  )),
  End = as_datetime(c(
    "2023-07-15 13:43:15",
    "2023-07-16 10:50:45",
    "2023-08-16 16:42:12",
    "2023-08-16 13:11:13",
    "2023-08-16 19:30:00",
    "2023-08-17 13:00:00",
    "2023-08-17 19:58:22",
    "2023-08-17 23:59:59"
  ))
)

# 2. 定义函数：拆分跨天的故障区间到对应日历日
split_interval_by_day <- function(begin, end) {
  start_date <- as_date(begin)
  end_date <- as_date(end)
  
  if (start_date == end_date) {
    return(tibble(
      Date = start_date,
      Interval = interval(begin, end)
    ))
  }
  
  # 拆分跨天的区间
  intervals <- list()
  current_date <- start_date
  
  while (current_date <= end_date) {
    day_start <- ymd_hms(paste(current_date, "00:00:00"))
    day_end <- ymd_hms(paste(current_date, "23:59:59"))
    
    # 计算当前日期的实际区间
    interval_start <- max(begin, day_start)
    interval_end <- min(end, day_end)
    
    intervals[[length(intervals)+1]] <- tibble(
      Date = current_date,
      Interval = interval(interval_start, interval_end)
    )
    
    current_date <- current_date + days(1)
  }
  
  bind_rows(intervals)
}

# 3. 拆分所有故障区间到对应日期
df_split <- df_time %>%
  rowwise() %>%
  do(split_interval_by_day(.$Begin, .$End)) %>%
  ungroup()

# 4. 定义函数：合并同一天内的重叠区间
merge_overlapping_intervals <- function(intervals) {
  if (length(intervals) == 0) return(intervals)
  
  # 按区间起始时间排序
  sorted_intervals <- intervals[order(int_start(intervals))]
  merged <- list(sorted_intervals[1])
  
  for (i in 2:length(sorted_intervals)) {
    last_merged <- merged[[length(merged)]]
    current <- sorted_intervals[i]
    
    if (int_overlaps(last_merged, current) || int_start(current) <= int_end(last_merged)) {
      # 合并区间：取最早的开始和最晚的结束
      new_interval <- interval(min(int_start(last_merged), int_start(current)),
                               max(int_end(last_merged), int_end(current)))
      merged[[length(merged)]] <- new_interval
    } else {
      merged[[length(merged)+1]] <- current
    }
  }
  
  merged
}

# 5. 按日期合并重叠区间并计算时长
daily_stats <- df_split %>%
  group_by(Date) %>%
  summarise(
    merged_intervals = list(merge_overlapping_intervals(Interval)),
    .groups = "drop"
  ) %>%
  mutate(
    # 计算总故障时长（秒）
    malfunction_seconds = map_dbl(merged_intervals, ~sum(int_length(.x))),
    # 转换为小时并取整（或保留小数）
    malfunction_times = paste(round(malfunction_seconds / 3600), "hours"),
    # 计算无故障时长
    ok_seconds = 24*3600 - malfunction_seconds,
    OK_times = paste(round(ok_seconds / 3600), "hours")
  ) %>%
  select(Date, OK_times, malfunction_times)

# 查看结果
print(daily_stats)

代码说明 #

1. 拆分跨天区间：split_interval_by_day函数会将跨天的故障拆分成每个日历日对应的子区间，确保后续统计的准确性。
2. 合并重叠区间：merge_overlapping_intervals函数对同一天内的所有故障区间排序后，合并重叠或连续的区间，避免重复计算故障时长。
3. 统计时长：按日历日汇总合并后的区间总时长，再通过24小时减去故障时长得到无故障时长，最后格式化为友好的字符串。

运行结果 #

# A tibble: 4 × 3
  Date       OK_times malfunction_times
  <date>     <chr>    <chr>            
1 2023-07-15 10 hours 14 hours          
2 2023-07-16 19 hours 5 hours           
3 2023-08-16 10 hours 14 hours          
4 2023-08-17 5 hours  19 hours          

（注：结果中的小时数为四舍五入后的整数，如需更精确的时长，可修改round函数为floor或保留小数位）

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