基于唯一ID计算多日期区间的重叠天数总和（R语言）

原始数据与现有代码 #

数据定义 #

df <- data.frame(
  ID = c(123, 123, 123, 12345, 12345, 12345, 1357, 246810, 246810, 9876),
  SubID = c(321, 456, 967, 98, 765, 1938, 210, 878, 2, 33),
  Unit = c("King", "Double", "Suite", "King", "King", "Double", "Suite", "Double", "King", "Double"),
  Startdate = as.Date(c("2020-08-16", "2020-01-01", "2010-02-15", "2020-05-01", "2020-05-01", "2022-08-02", "2022-08-06", "2022-10-09", "2017-07-08", "2019-02-08")),
  Enddate = as.Date(c("2023-09-25", "2020-08-20", "2020-08-16", "2020-05-01", "2023-09-16", "2022-08-15", "2022-10-01", "2023-10-15", "2022-10-09", "2023-09-08"))
)

现有代码与结果 #

library(dplyr)
library(lubridate)

dfoverlapdays <- df %>%
  mutate(
    overlap_days = pmax(0, min(Enddate, max(Enddate, na.rm = TRUE)) - Startdate + 1))    

total_days <- sum(dfoverlapdays$overlap_days) 

cat("Sum of days", total_days, "\n")

输出结果：

Sum of days 5331 

现有代码的问题 #

当前代码逻辑存在错误：

• max(Enddate, na.rm = TRUE)取的是整个数据集的最晚结束日期，而非同一客人（ID分组）的日期，无法精准计算同一客人的入住区间重叠。
• 计算逻辑未区分不同客人的入住记录，也未识别同一客人多条记录的重叠关系，结果不具备参考性。

正确实现方案 #

方案1：基于区间合并的重叠天数计算 #

核心思路：先计算同一客人所有入住区间的总天数，再合并重叠/相邻区间计算实际覆盖天数，两者差值即为该客人的重叠天数总和，最后累加所有客人的结果。

library(dplyr)
library(lubridate)

# 先计算每个入住区间的天数
df <- df %>%
  mutate(interval_days = as.numeric(Enddate - Startdate + 1))

# 按ID分组处理，计算重叠天数
result <- df %>%
  group_by(ID) %>%
  arrange(Startdate) %>%
  # 标记合并区间的起点
  mutate(
    is_new_interval = ifelse(row_number() == 1, TRUE, Startdate > lag(Enddate)),
    group_id = cumsum(is_new_interval)
  ) %>%
  group_by(ID, group_id) %>%
  # 每个合并区间的最早开始、最晚结束日期
  summarise(
    merged_start = min(Startdate),
    merged_end = max(Enddate),
    .groups = "drop_last"
  ) %>%
  # 计算该客人的重叠天数
  summarise(
    merged_total_days = sum(as.numeric(merged_end - merged_start + 1)),
    original_total_days = sum(df$interval_days[df$ID == cur_group()$ID]),
    overlap_days = original_total_days - merged_total_days,
    .groups = "drop"
  )

# 所有客人的重叠天数总和
total_overlap <- sum(result$overlap_days)
cat("Sum of overlapping days:", total_overlap, "\n")

方案2：基于配对区间的去重重叠计算 #

核心思路：通过自连接找到同一客人的所有入住记录对，计算每对的重叠区间，再合并重复的重叠区间，最终得到去重后的重叠天数总和。

library(dplyr)
library(fuzzyjoin)
library(lubridate)

# 自连接找到同一ID下的重叠记录对（避免重复计算，只保留SubID.x < SubID.y的配对）
overlap_pairs <- df %>%
  fuzzy_inner_join(
    df,
    by = c("ID" = "ID", "Startdate" = "Enddate", "Enddate" = "Startdate"),
    match_fun = list(`==`, `<=`, `>=`)
  ) %>%
  filter(SubID.x < SubID.y) %>%
  mutate(
    overlap_start = pmax(Startdate.x, Startdate.y),
    overlap_end = pmin(Enddate.x, Enddate.y),
    overlap_days = as.numeric(overlap_end - overlap_start + 1)
  ) %>%
  filter(overlap_days > 0)

# 合并重复的重叠区间，计算去重后的总重叠天数
all_overlap_intervals <- overlap_pairs %>%
  select(ID, overlap_start, overlap_end) %>%
  group_by(ID) %>%
  arrange(overlap_start) %>%
  mutate(
    is_new_overlap = ifelse(row_number() == 1, TRUE, overlap_start > lag(overlap_end)),
    group_id = cumsum(is_new_overlap)
  ) %>%
  group_by(ID, group_id) %>%
  summarise(
    merged_overlap_start = min(overlap_start),
    merged_overlap_end = max(overlap_end),
    .groups = "drop_last"
  ) %>%
  summarise(
    id_overlap_days = sum(as.numeric(merged_overlap_end - merged_overlap_start + 1)),
    .groups = "drop"
  )

total_overlap_unique <- sum(all_overlap_intervals$id_overlap_days)
cat("Sum of unique overlapping days:", total_overlap_unique, "\n")

验证方法 #

1. 小样本手动计算：选取单个ID（如ID=123），手动梳理其所有入住区间的重叠部分，对比代码输出结果是否一致。
2. 多方法结果对比：运行上述两种方案，若结果一致，说明代码逻辑可靠。
3. 抽样验证：从3000行数据中随机抽取5-10个ID，手动计算重叠天数，与代码输出交叉验证。

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