R语言中基于时间窗条件的DataFrame行计数向量化实现问询

问题背景 #

有两个数据框：

• snap：包含SITE列，以及代表15分钟时间窗的SNAPSHOT_DATE1（起始）和SNAPSHOT_DATE2（结束）日期时间列
• custexperience：需匹配到snap的业务数据

需求为给snap新增AS_PREALLOC_ACTIVE列，统计custexperience中满足以下所有条件的行数：

1. 排除AS_ORDER_CREATE为NA的行
2. AS_ORDER_CREATE < SNAPSHOT_DATE1
3. AS_FRST_ALLOC_DTTM > SNAPSHOT_DATE2
4. 站点SITE匹配

（注：原描述中"时间窗内"与"早于/晚于时间窗边界"的条件存在逻辑矛盾，此处按合理业务逻辑调整为订单生命周期覆盖当前snap时间窗，若需严格遵循原描述可自行调整条件）

数据结构示例 #

snap 结构 #

structure(list(SITE = c("A", "B", "C"), 
               SNAPSHOT_DATE1 = structure(c(1620000000, 1620000900, 1620001800), 
                                          class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
               SNAPSHOT_DATE2 = structure(c(1620000900, 1620001800, 1620002700), 
                                          class = c("POSIXct", "POSIXt"), tzone = "UTC")), 
          row.names = c(NA, -3L), class = "data.frame")

custexperience 结构 #

structure(list(SITE = c("A", "A", "B", "C", "C"), 
               AS_ORDER_CREATE = structure(c(1619999500, 1620000200, NA, 1620001500, 1620001000), 
                                           class = c("POSIXct", "POSIXt"), tzone = "UTC"), 
               AS_FRST_ALLOC_DTTM = structure(c(1620001000, 1620000800, 1620002000, 1620003000, 1620002000), 
                                              class = c("POSIXct", "POSIXt"), tzone = "UTC")), 
          row.names = c(NA, -5L), class = "data.frame")

原循环代码（性能瓶颈） #

snap$AS_PREALLOC_ACTIVE <- 0

for (i in 1:nrow(snap)) {
  current_site <- snap$SITE[i]
  date1 <- snap$SNAPSHOT_DATE1[i]
  date2 <- snap$SNAPSHOT_DATE2[i]
  
  filtered <- custexperience %>%
    filter(SITE == current_site,
           !is.na(AS_ORDER_CREATE),
           AS_ORDER_CREATE < date1,
           AS_FRST_ALLOC_DTTM > date2)
  
  snap$AS_PREALLOC_ACTIVE[i] <- nrow(filtered)
}

向量化解决方案 #

方法1：data.table非等连接（最优性能，适合大数据） #

data.table的非等连接是处理这类范围匹配计数最快的方式，内存效率高：

library(data.table)

# 转换为data.table格式
setDT(snap)
setDT(custexperience)

# 先过滤NA行
cust_filtered <- custexperience[!is.na(AS_ORDER_CREATE)]

# 非等连接并按snap每行计数
match_counts <- cust_filtered[snap,
                              on = .(SITE = SITE,
                                     AS_ORDER_CREATE < SNAPSHOT_DATE1,
                                     AS_FRST_ALLOC_DTTM > SNAPSHOT_DATE2),
                              .N,
                              by = .EACHI]

# 将计数合并回snap，无匹配项填充0
snap[match_counts, AS_PREALLOC_ACTIVE := N, on = .(SITE, SNAPSHOT_DATE1, SNAPSHOT_DATE2)]
snap[is.na(AS_PREALLOC_ACTIVE), AS_PREALLOC_ACTIVE := 0]

方法2：dplyr + fuzzyjoin（高可读性，适合中等数据） #

fuzzyjoin提供直观的模糊连接语法，适合习惯tidyverse的用户：

library(dplyr)
library(fuzzyjoin)

# 过滤NA行
cust_filtered <- custexperience %>% filter(!is.na(AS_ORDER_CREATE))

# 模糊连接后按snap分组计数
snap <- snap %>%
  fuzzy_left_join(cust_filtered,
                  by = c("SITE" = "SITE",
                         "SNAPSHOT_DATE1" = "AS_ORDER_CREATE",
                         "SNAPSHOT_DATE2" = "AS_FRST_ALLOC_DTTM"),
                  match_fun = list(`==`, `>`, `<`)) %>%
  group_by(SITE.x, SNAPSHOT_DATE1, SNAPSHOT_DATE2) %>%
  summarise(AS_PREALLOC_ACTIVE = n(), .groups = "drop") %>%
  rename(SITE = SITE.x) %>%
  right_join(snap, by = c("SITE", "SNAPSHOT_DATE1", "SNAPSHOT_DATE2")) %>%
  mutate(AS_PREALLOC_ACTIVE = ifelse(is.na(AS_PREALLOC_ACTIVE), 0, AS_PREALLOC_ACTIVE))

方法3：Base R向量化（无依赖，适合小数据） #

无需额外包，用矩阵运算实现，但大数据量下内存压力大：

# 过滤NA行
cust_filtered <- custexperience[!is.na(custexperience$AS_ORDER_CREATE), ]

# 生成所有匹配条件的布尔矩阵
site_match <- outer(snap$SITE, cust_filtered$SITE, `==`)
order_before <- outer(snap$SNAPSHOT_DATE1, cust_filtered$AS_ORDER_CREATE, `>`)
alloc_after <- outer(snap$SNAPSHOT_DATE2, cust_filtered$AS_FRST_ALLOC_DTTM, `<`)

# 统计每行满足所有条件的数量
snap$AS_PREALLOC_ACTIVE <- rowSums(site_match & order_before & alloc_after)

方案选择建议 #

• 百万级以上大数据：优先选data.table方案，性能碾压其他方法
• 中等数据量（万级）：选dplyr+fuzzyjoin，代码易读易维护
• 小数据量：Base R方案，无需额外包

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