高效统计各时间点被区间包含次数：data.table优化方案问询

Great question! When you're counting how many intervals contain each time point—especially with large datasets—data.table is hands down one of the fastest tools you can use. It avoids the slow O(n*m) loops that plague base R or even dplyr for big data, leveraging optimized non-equi joins instead. Let me break this down with a concrete example.

First, Let's Define Sample Data #

Let's assume your df has start/end time columns, and times is a vector of time points we need to check:

library(data.table)
set.seed(123) # For reproducibility

# Sample interval data (100k rows)
df <- data.table(
  start = as.POSIXct("2024-01-01 00:00:00") + rnorm(100000, 0, 86400),
  end = as.POSIXct("2024-01-01 00:00:00") + rnorm(100000, 86400*7, 86400)
)

# Sample time points (hourly over 2 weeks)
times <- as.POSIXct("2024-01-01 00:00:00") + seq(0, 86400*14, 3600)

The Slow "Conventional" Approach (Avoid for Large Data) #

A typical base R method would loop through each time point and count overlapping intervals. But this is terribly slow for big datasets (100k intervals + 336 time points = 33 million operations):

# Not recommended for large data!
slow_counts <- sapply(times, function(t) sum(df$start <= t & df$end >= t))

The Fast data.table Solution #

We'll use non-equi joins—a superpower of data.table—to match each time point to all intervals that contain it, then count matches per time point. This runs in O(n log n + m log n) time, which is way more efficient.

Step 1: Convert times to a data.table #

First, we need our time points in a data.table to enable the join:

dt_times <- data.table(time = times)

Step 2: Non-Equi Join + Count Matches #

This one line does the heavy lifting: we join dt_times to df where time falls between start and end, then count matches per time point:

# Join and count matches
temp_result <- dt_times[df, on = .(time >= start, time <= end), 
                        .N, by = .EACHI]

Step 3: Fill in Zero-Count Time Points #

Some time points might not fall into any interval (so they won't show up in temp_result). We can merge back to our original time points to add these zero counts:

final_result <- dt_times[temp_result, on = .(time), 
                         .(time, count = ifelse(is.na(N), 0, N))]

Optional: Pre-Sort for Even More Speed #

If your interval data isn't already sorted, pre-sorting df by start and end can give an extra speed boost (data.table's join is optimized for sorted keys):

setorder(df, start, end)

Why This Works So Well #

Data.table's non-equi joins use binary search under the hood, which is way faster than looping through every interval for every time point. For datasets with millions of rows or time points, this difference is night and day—what takes minutes in base R takes seconds with data.table.

Pro tip: This logic works exactly the same if your time data is in Date format instead of POSIXct—just make sure all columns are the same date/time class to avoid mismatches.

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