如何用data.table高效统计时间戳前后w秒窗口内的行数？

首先，你的问题非常典型——在时间序列数据中统计每个点的滑动窗口行数，循环方法确实会因为逐行遍历导致性能瓶颈，尤其是数据量增大时。你目前用sapply的方法已经有提升，但原生data.table的非等连接或二分查找方法能带来数量级的性能飞跃。

先回顾你的示例数据（为了清晰，我用代码块展示）：

library(data.table)
dt <- data.table(
  TIMESTAMP = as.POSIXct(c(
    "2017-11-16 02:50:19", "2017-11-16 03:01:38", "2017-11-16 08:07:03",
    "2017-11-16 09:33:31", "2017-11-16 11:00:04", "2017-11-16 11:01:50",
    "2017-11-16 12:00:46", "2017-11-16 16:31:16", "2017-11-16 16:37:33",
    "2017-11-16 17:23:06", "2017-11-16 17:31:57", "2017-11-16 17:35:17",
    "2017-11-16 18:32:47", "2017-11-16 19:02:11", "2017-11-16 19:02:23",
    "2017-11-16 19:40:25", "2017-11-16 20:54:38", "2017-11-16 21:26:35",
    "2017-11-16 21:30:08", "2017-11-16 23:42:37"
  )),
  FEAT1 = c(2332843, 40913, 2758077, 156899, 3639410, 46274, 3336248, 3262457, 3110064, 3059651, 51569, 2254123, 2321751, 469452, 2248207, 62245, 2245512, 4191734, 2202018, 2434562),
  FEAT2 = c(1282, 129, 15281, 448, 1901, 242, 1975, 1006, 840, 765, 143, 899, 1182, 1257, 923, 150, 936, 1044, 958, 2559)
)
w <- 2000 # 窗口大小：2000秒

方案1：非等自连接（最直观的原生data.table实现） #

data.table的非等连接是专门为这类范围匹配场景设计的，完全用C级别的优化实现，性能远超循环或sapply：

# 1. 为每行计算时间窗口的上下限
dt[, `:=`(lower = TIMESTAMP - w, upper = TIMESTAMP + w)]

# 2. 非等自连接，统计每个行对应的窗口内行数
dt[, COUNT := dt[dt, on = .(TIMESTAMP > lower, TIMESTAMP < upper), .N, by = .EACHI]$N]

# 3. 可选：删除临时的上下限列
dt[, c("lower", "upper") := NULL]

代码解释 #

• dt[dt, on = ...]：对原表做自连接
• on = .(TIMESTAMP > lower, TIMESTAMP < upper)：指定连接条件——右侧表的TIMESTAMP要在左侧行的lower和upper之间（不包含边界，和你原逻辑一致）
• by = .EACHI：表示对左侧的每一行，单独计算匹配到的右侧行数.N
• 最后把计算得到的.N赋值给新列COUNT

方案2：二分查找（性能最优的方案） #

如果你的数据已经按TIMESTAMP排序（或者可以先排序），用findInterval做二分查找会更快，因为它完全避免了连接操作：

# 1. 确保数据按时间戳排序（如果未排序）
setorder(dt, TIMESTAMP)
tme_vec <- dt$TIMESTAMP

# 2. 用二分查找快速定位每个窗口的边界位置
lower_pos <- findInterval(tme_vec - w, tme_vec)
upper_pos <- findInterval(tme_vec + w, tme_vec)

# 3. 计算窗口内行数：上限位置 - 下限位置
dt[, COUNT := upper_pos - lower_pos]

代码解释 #

• findInterval(x, vec)：返回vec中小于等于x的最大元素的索引，是非常高效的二分查找实现
• 因为TIMESTAMP有序，每个窗口的行数就是upper_pos（窗口上限对应的位置）减去lower_pos（窗口下限对应的位置），完全符合你要的TIMESTAMP > lower & TIMESTAMP < upper的逻辑

性能对比 #

这两种原生方法的性能远优于你目前的sapply方案：

• 对于10万行数据，原生方法通常能在1秒以内完成，而sapply需要175秒左右，循环更是需要几百秒
• 数据量越大，性能差距越明显，这是因为data.table的操作都是向量级别的C实现，避免了R层面的循环开销

你提到的现有sapply方法：

tme <- dt$TIMESTAMP
lower <- tme - w
upper <- tme + w
dt$count <- sapply(1:length(tme ), function(z) return(sum(tme > lower[z] & tme < upper[z])))

虽然比循环快，但本质还是逐行计算，在数据量较大时依然会有性能瓶颈，推荐替换为上面的原生data.table方案。

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