R语言按分组统计指定数值范围连续出现的最长持续时长

现有如下结构的dataframe（因实际数据量较大，此处仅展示示例数据构造方式）：

minutes <- seq(0,585, by = 15)
salinity <-  as.numeric(sample(x = 29:55, size  = 40, replace = TRUE))
site <- c(1, 2, 3, ...etc.)
year <- c(2020, 2019, ...etc.)
season <- c("Dry", "Wet")

df <- cbind.data.frame(year, season, site, minutes, salinity)

> head(df)
  year season site minutes salinity
1 2020    DRY    1       0       54
2 2020    DRY    1      15       39
3 2020    DRY    1      30       44
4 2020    DRY    1      45       54
5 2020    DRY    1      60       43
6 2020    DRY    1      75       40

实际数据集包含过去数年n个站点以15分钟为增量连续测量的80余万条数据。现需要在R中复现原本由Excel制作的矩阵/统计表，无需还原表格配色，仅需复现表格结构：按年份、季节、站点分组，统计每个分组下盐度值持续落在指定区间（例如40-50）的最长连续观测分钟数，生成对应统计结果表。

针对80万条数据的规模，优先选择data.table实现，运行速度快、内存占用低，核心逻辑为：

• 先按分组维度和时间字段排序，保证时间序列顺序正确
• 标记每条记录的盐度是否落在目标区间
• 对每个分组内连续处于「符合区间/不符合区间」状态的记录划分连续段
• 筛选所有符合区间的连续段，计算每段总时长（单条记录间隔15分钟，段时长=段内记录数*15）
• 按分组取最长段时长作为最终结果

完整代码如下：

# 加载data.table，未安装先运行 install.packages("data.table")
library(data.table)

# 可自行修改目标盐度区间阈值
low_threshold <- 40
high_threshold <- 50

# 数据格式转换与排序
setDT(df)
setorder(df, year, season, site, minutes)

# 计算统计结果
max_duration_result <- df[,
  .(in_target_range = salinity >= low_threshold & salinity <= high_threshold, minutes),
  by = .(year, season, site)
][,
  .(in_target_range, minutes, seg_id = cumsum(c(1, diff(in_target_range) != 0))),
  by = .(year, season, site)
][
  in_target_range == TRUE
][,
  .(seg_total_min = .N * 15),
  by = .(year, season, site, seg_id)
][,
  .(max_continuous_minutes = ifelse(.N == 0, 0, max(seg_total_min))),
  by = .(year, season, site)
]

如果习惯使用tidyverse语法，也可以用dplyr实现，逻辑完全一致：

# 加载dplyr，未安装先运行 install.packages("dplyr")
library(dplyr)

low_threshold <- 40
high_threshold <- 50

max_duration_result <- df %>%
  arrange(year, season, site, minutes) %>%
  group_by(year, season, site) %>%
  mutate(in_target_range = salinity >= low_threshold & salinity <= high_threshold) %>%
  mutate(seg_id = cumsum(c(1, diff(in_target_range) != 0))) %>%
  filter(in_target_range == TRUE) %>%
  group_by(year, season, site, seg_id) %>%
  summarise(seg_total_min = n() * 15, .groups = "drop_last") %>%
  summarise(max_continuous_minutes = ifelse(n() == 0, 0, max(seg_total_min)), .groups = "drop")

注意：运行代码前请确认每个站点-年份-季节分组内的15分钟时间序列无缺测，如果存在缺测记录，需要先补全对应时间点，否则连续时长计算结果会偏大。

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