如何加速R中按参数分组的时间窗口均值计算？

问题背景 #

现有如下结构的DataFrame：

ID   ParameterID Time                   value   group_end_time      group_start_time
       <dbl>       <dbl> <dttm>                    <dbl> <dttm>              <dttm>
 1         1           1 2022-01-01 10:05:00           1 2022-01-01 10:20:00 2022-01-01 10:05:00
 2         1           2 2022-01-01 10:05:00           1 2022-01-01 10:20:00 2022-01-01 09:50:00
 3         1           1 2022-01-01 10:10:00           2 2022-01-01 10:20:00 2022-01-01 10:05:00
 4         1           2 2022-01-01 10:10:00           2 2022-01-01 10:20:00 2022-01-01 09:50:00
 5         1           1 2022-01-01 10:15:00           3 2022-01-01 10:20:00 2022-01-01 10:05:00
 6         1           1 2022-01-01 10:20:00           4 2022-01-01 10:20:00 2022-01-01 10:05:00
 7         1           1 2022-01-01 10:25:00           5 2022-01-01 10:35:00 2022-01-01 10:20:00
 8         1           1 2022-01-01 10:30:00           6 2022-01-01 10:35:00 2022-01-01 10:20:00
 9         1           2 2022-01-01 10:30:00           3 2022-01-01 10:35:00 2022-01-01 10:05:00
10         1           2 2022-01-01 11:36:00           4 2022-01-01 11:50:00 2022-01-01 11:20:00

需求：对每个ParameterID，针对其每个group_end_time，计算满足Time >= group_start_time且Time < group_end_time的同ParameterID的value均值。

现有低效方案 #

当前通过自定义summarise函数实现逻辑，但大数据集下速度极慢：

df %>%
  group_by(ID, ParameterID, group_end_time) %>%
  summarise(aggregation_function(ID, ParameterID, group_end_time, group_start_time, .))

aggregation_function <- function(id, par_id, end_time, start_time, full_data) {
  ret <- full_data %>%
    filter(ID == id[[1]] & ParameterID == par_id[[1]] &
             Time < end_time[[1]] & Time >= start_time[[1]]) %>%
    group_by(PatientID, ParameterID) %>% # 注：原代码中PatientID应为ID，疑似笔误
    summarise(mean = mean(value, na.rm = TRUE)
    )
  return(ret)
}

输出结果：

ret
# A tibble: 5 × 4
# Groups:   PatientID, ParameterID [2]
        ID   ParameterID group_end_time       mean
      <dbl>       <dbl> <dttm>              <dbl>
1         1           1 2022-01-01 10:20:00   2
2         1           2 2022-01-01 10:20:00   1.5
3         1           1 2022-01-01 10:35:00   5
4         1           2 2022-01-01 10:35:00   2
5         1           2 2022-01-01 11:50:00   4

优化方案 #

方案1：dplyr非等值连接 + 分组聚合 #

先提取唯一时间区间分组，再通过非等值连接批量匹配符合条件的行，最后计算均值，避免循环遍历全表：

library(dplyr)

# 提取唯一的时间区间分组
interval_groups <- df %>%
  distinct(ID, ParameterID, group_end_time, group_start_time)

# 非等值连接匹配数据并计算均值
result <- interval_groups %>%
  left_join(df, by = c("ID", "ParameterID")) %>%
  filter(Time >= group_start_time & Time < group_end_time) %>%
  group_by(ID, ParameterID, group_end_time) %>%
  summarise(mean = mean(value, na.rm = TRUE), .groups = "drop")

方案2：data.table快速非等值连接 #

data.table的非等值连接性能远优于dplyr，适合超大数据集场景：

library(data.table)

setDT(df)

# 提取唯一区间
interval_groups <- unique(df[, .(ID, ParameterID, group_end_time, group_start_time)])

# 非等值连接并计算均值
result <- interval_groups[df, on = .(ID, ParameterID, group_start_time <= Time, group_end_time > Time),
                          .(mean = mean(value, na.rm = TRUE)),
                          by = .(ID, ParameterID, group_end_time)]

方案3：预分组排序后滑动窗口（时间有序场景） #

如果数据已按ID、ParameterID、Time排序，可利用累积统计量计算区间均值，避免重复求和：

library(dplyr)

# 排序并计算累积统计量
preprocessed <- df %>%
  arrange(ID, ParameterID, Time) %>%
  group_by(ID, ParameterID) %>%
  mutate(cum_sum = cumsum(value),
         cum_count = row_number()) %>%
  ungroup()

# 提取唯一区间并匹配起止点的累积值
interval_groups <- df %>%
  distinct(ID, ParameterID, group_end_time, group_start_time)

result <- interval_groups %>%
  # 匹配区间内的最后一条数据
  left_join(preprocessed, by = c("ID", "ParameterID")) %>%
  filter(Time < group_end_time) %>%
  group_by(ID, ParameterID, group_end_time, group_start_time) %>%
  slice_max(Time) %>%
  # 匹配区间内的第一条数据
  left_join(preprocessed, by = c("ID", "ParameterID"), suffix = c("_end", "_start")) %>%
  filter(Time_start >= group_start_time) %>%
  slice_min(Time_start) %>%
  # 通过累积值差计算均值
  mutate(mean = (cum_sum_end - cum_sum_start + value_start) / (cum_count_end - cum_count_start + 1)) %>%
  select(ID, ParameterID, group_end_time, mean)

关键优化点 #

• 摒弃分组内循环全表过滤：原方案时间复杂度为O(n²)，优化后降至O(n log n)或O(n)
• 批量匹配区间与数据行：利用非等值连接减少重复计算
• 优先用data.table处理大数据：底层实现更高效，适合海量数据
• 时间有序场景用累积统计量：避免重复计算区间内的总和与计数

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