如何移除R代码循环，加速计算同地点员工出勤重叠天数

核心思路 #

放弃构建日期矩阵的低效方式，直接通过区间重叠公式计算员工间的出勤重叠天数，结合dplyr的向量运算和自连接替代嵌套循环，大幅提升性能。

首先修正原始数据类型（原示例中arrival_day和departure_day为字符型，需转为整数才能正常计算）：

library(dplyr)
library(tidyr)

# 预处理：修正数据类型
Attendance <- Attendance %>%
  mutate(across(c(arrival_day, departure_day), as.integer))

1. 生成DataFrame格式结果（res1bis风格） #

通过自连接生成同地点的所有员工对，利用区间重叠公式计算天数，再转换为宽表：

# 步骤1：计算所有同地点员工对的重叠天数
overlap_pairs <- Attendance %>%
  # 自连接获取同地点的所有员工组合
  inner_join(Attendance, by = "location", suffix = c("_x", "_y")) %>%
  # 排除员工与自身的组合
  filter(worker_x != worker_y) %>%
  # 计算区间重叠天数：max(0, 结束日最小值 - 开始日最大值 + 1)
  mutate(
    overlap_days = pmax(0, pmin(departure_day_x, departure_day_y) - pmax(arrival_day_x, arrival_day_y) + 1)
  ) %>%
  # 仅保留有重叠的组合
  filter(overlap_days > 0) %>%
  select(location, worker = worker_x, other_worker = worker_y, overlap_days)

# 步骤2：转换为宽表（res1bis风格，含所有员工列）
all_workers <- sort(unique(Attendance$worker))

res1bis <- Attendance %>%
  select(location, worker) %>%
  # 生成所有员工对（含自身）
  crossing(other_worker = all_workers) %>%
  # 关联重叠天数数据
  left_join(overlap_pairs, by = c("location", "worker", "other_worker")) %>%
  # 自身重叠天数设为0，无重叠的设为0
  mutate(
    overlap_days = case_when(
      worker == other_worker ~ 0,
      is.na(overlap_days) ~ 0,
      TRUE ~ overlap_days
    )
  ) %>%
  # 转换为宽表
  pivot_wider(
    id_cols = c(location, worker),
    names_from = other_worker,
    values_from = overlap_days
  )

2. 生成嵌套列表格式结果（res2风格） #

通过分组汇总和deframe转换为嵌套列表，补全无重叠的员工空列表：

# 步骤1：按地点、员工分组整理重叠数据
overlap_groups <- overlap_pairs %>%
  group_by(location, worker) %>%
  summarise(
    overlap_list = setNames(as.list(overlap_days), other_worker),
    .groups = "drop"
  )

# 步骤2：补全所有员工（含无重叠的）
all_workers_by_loc <- Attendance %>%
  group_by(location) %>%
  summarise(all_workers = list(worker), .groups = "drop")

# 步骤3：生成最终嵌套列表
res2 <- overlap_groups %>%
  right_join(all_workers_by_loc, by = "location") %>%
  unnest(all_workers) %>%
  mutate(worker = coalesce(worker, all_workers)) %>%
  group_by(location, worker) %>%
  summarise(
    overlap_list = ifelse(is.null(first(overlap_list)), list(), first(overlap_list)),
    .groups = "drop"
  ) %>%
  group_by(location) %>%
  summarise(worker_list = setNames(overlap_list, worker), .groups = "drop") %>%
  deframe()

性能提升说明 #

1. 内存优化：无需构建包含200列日期的巨大矩阵，避免内存占用爆炸；
2. 速度优化：用向量运算和数据库风格的自连接替代三重嵌套循环，时间复杂度从O(n²)的逐行循环变为高效的批量运算，处理20000员工时速度可提升数倍；
3. 逻辑简洁：直接用区间重叠公式计算天数，无需逐天判断，逻辑更清晰易维护。

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