R语言使用dplyr计算各用户不同产品日期间隔的重叠天数

思路说明 #

• 第一步先合并同一用户、同一产品的所有重叠/相邻购买区间，避免后续计算重复统计重叠时长
• 第二步对每个用户生成所有不重复的产品两两组合，避免A_B和B_A重复计算
• 第三步匹配两类产品的所有区间，逐对计算重叠天数后求和
• 最后将长表宽化为要求的输出格式

完整实现代码 #

library(dplyr)
library(lubridate)
library(tidyr)
library(purrr)

# 第一步：合并同用户同产品的重叠/连续区间
merged_interval <- data %>%
  group_by(user_id, product) %>%
  arrange(start_date, .by_group = TRUE) %>%
  # 标记独立的不重叠区间分组
  mutate(
    grp = cumsum(start_date > lag(cummax(end_date), default = first(start_date) - days(1)))
  ) %>%
  group_by(user_id, product, grp) %>%
  summarise(
    start_date = min(start_date),
    end_date = max(end_date),
    .groups = "drop"
  ) %>%
  select(-grp)

# 第二步：计算两两产品总重叠天数并格式化输出
overlap_result <- merged_interval %>%
  group_by(user_id) %>%
  # 生成所有无重复的产品两两组合
  reframe(
    comb = combn(unique(product), 2, simplify = FALSE),
    pair = map_chr(comb, ~paste(sort(.x), collapse = "_"))
  ) %>%
  unnest(comb) %>%
  # 拆分组合为两个独立产品字段
  mutate(
    p1 = map_chr(comb, ~.x[1]),
    p2 = map_chr(comb, ~.x[2])
  ) %>%
  # 关联两个产品对应的所有区间
  left_join(merged_interval, by = c("user_id", "p1" = "product")) %>%
  left_join(merged_interval, by = c("user_id", "p2" = "product"), suffix = c("_p1", "_p2")) %>%
  # 逐对计算区间重叠天数，无重叠则为0
  mutate(
    overlap_start = pmax(start_date_p1, start_date_p2),
    overlap_end = pmin(end_date_p1, end_date_p2),
    days = as.numeric(difftime(overlap_end, overlap_start, units = "days")),
    days = ifelse(days < 0, 0, days)
  ) %>%
  # 按用户和产品对汇总总重叠天数
  group_by(user_id, pair) %>%
  summarise(total_days = sum(days), .groups = "drop") %>%
  # 格式化为要求的“X days”格式
  mutate(total_days = paste0(total_days, " days")) %>%
  # 宽表转换得到最终输出结构
  pivot_wider(
    names_from = pair,
    values_from = total_days,
    values_fill = "0 days"
  )

结果说明 #

运行后overlap_result即为符合要求的输出，会自动生成所有存在的产品组合列，没有重叠的组合默认填充为0 days，无需手动指定产品组合。

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