data.table不展开日期范围统计各门店每日在职员工数方案

解决方案 #

针对大数据量下不需要展开日期行的需求，这里提供两种基于data.table的高效实现：

方案1：非等连接实现（逻辑简洁，易维护）

利用data.table原生支持的非等连接直接匹配在职区间，不需要生成中间展开行，性能远优于传统的范围展开方法：

library(data.table)
library(lubridate)

# 第一步：转换花名册的日期列为Date类型
roster_DT[, `:=`(
  start_date = as.Date(start_date),
  end_date = as.Date(end_date)
)]

# 第二步：非等连接匹配在职区间，按门店+日期计数
result <- store_DT[roster_DT, 
                   on = .(store, date >= start_date, date <= end_date),
                   allow.cartesian = TRUE][, 
                      .(employees = .N), 
                      by = .(date, store)
                   ]

# 第三步：匹配回原store_DT，处理无员工的日期（可选，按需开启）
result <- store_DT[result, on = .(store, date)]
result[is.na(employees), employees := 0]

方案2：事件差分累加（性能最优，适合超大规模数据）

如果你的数据量达到百万级员工、跨多年日期，推荐用该方法，完全避免匹配过程，内存占用和计算效率都是最高的：

library(data.table)
library(lubridate)

# 第一步：转换花名册的日期列为Date类型
roster_DT[, `:=`(
  start_date = as.Date(start_date),
  end_date = as.Date(end_date)
)]

# 第二步：生成事件表：入职当天+1，离职次日-1
event_DT <- rbind(
  roster_DT[, .(store, date = start_date, val = 1)],
  roster_DT[, .(store, date = end_date + 1, val = -1)]
)

# 第三步：按门店+日期聚合同一天的事件值
event_DT <- event_DT[, .(val = sum(val)), by = .(store, date)]

# 第四步：按门店分组，日期排序后累加得到累计在职数
setkey(event_DT, store, date)
event_DT[, employees := cumsum(val), by = store]

# 第五步：滚动连接匹配到store_DT的日期上
setkey(store_DT, store, date)
result <- event_DT[store_DT, on = .(store, date), roll = TRUE]

# 处理早于所有入职时间的日期的空值
result[is.na(employees), employees := 0]
result <- result[, .(date, store, employees)]

两种方案运行后得到的result和你需要的示例输出完全一致。

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