这是一种使用tidyverse(包括lubridate)的方法.输出是一个绘图框，您可以将其用作图表的基础.
#set initial data as per question
df <- structure(list(job_started_at = c("2024-04-29 18:35:09", "2024-04-29 18:33:19",
"2024-04-29 19:37:17", "2024-04-29 21:12:35",
"2024-04-29 20:31:52"
), job_finished_at = c("2024-04-29 19:36:25", "2024-04-29 20:34:40",
"2024-04-29 19:44:51", "2024-04-29 21:31:31",
"2024-04-29 20:53:23"
), position = c(1L, 2L, 1L, 2L, 1L)), class = "data.frame", row.names = c(NA, -5L))
df <- df %>%
mutate(across(starts_with("job"), ymd_hms), #convert to datetimes
job_int = interval(job_started_at, job_finished_at)) #calculate intervals
#create a df of the hours to be analysed
hours_df <- tibble(hour = seq(from = ymd_hms("2024-04-29 17:00:00"),
to = ymd_hms("2024-04-29 22:00:00"),
by = "hour")) %>%
mutate(hour_int = interval(hour, hour + hours(1))) #intervals
#for each item, calculate a vector of overlaps, unnest, group and summarise
overlaps_df <- df %>%
mutate(overlap = map(job_int, ~seconds(intersect(hours_df$hour_int, .))/3600),
hour_start = list(hours_df$hour)) %>%
unnest_longer(c(overlap, hour_start)) %>%
group_by(position, hour_start) %>%
summarise(utilisation = sum(overlap, na.rm = TRUE))
overlaps_df
# A tibble: 12 × 3
# Groups: position [2]
position hour_start utilisation
<int> <dttm> <dbl>
1 1 2024-04-29 17:00:00 0
2 1 2024-04-29 18:00:00 0.414
3 1 2024-04-29 19:00:00 0.733
4 1 2024-04-29 20:00:00 0.359
5 1 2024-04-29 21:00:00 0
6 1 2024-04-29 22:00:00 0
7 2 2024-04-29 17:00:00 0
8 2 2024-04-29 18:00:00 0.445
9 2 2024-04-29 19:00:00 1
10 2 2024-04-29 20:00:00 0.578
11 2 2024-04-29 21:00:00 0.316
12 2 2024-04-29 22:00:00 0