如何对data.table列表列实现3期滚动聚合？

需求说明 #

数据集包含人员ID（ind_id）、日期（date）、岗位ID（job_id），每个人在某一日期可能有多份工作，也可能无岗位（对应行缺失）。需要生成汇总表，要求：

• 每个ind_id-date组合仅保留一行
• 创建列表列，存储该人员在当前日期t、前一日期t-1、前两日期t-2的所有岗位ID

示例数据与期望输出 #

输入数据生成代码 #

library(data.table)

# Input data
data <- data.table(
  ind_id = c(rep(1, 3), rep(2, 4), rep(3, 2), rep(4, 5)),
  date = c(1, 2, 3, 1, 2, 2, 3, 1, 3, 1, 1, 2, 2, 3),
  job_id = c("A", "A", "A", "B", "B", "C", "B", "D", "E", "F", "G", "F", "G", "G")
)

期望输出数据生成代码 #

# Desired output
output <- data.table(
  ind_id = c(rep(1, 3), rep(2, 3), rep(3, 3), rep(4, 3)),
  date = rep(1:3, 4),
  job_id = list("A", "A", "A", "B", c("B", "C"), c("B", "C"), "D", c("D"), c("D", "E"), c("F", "G"), c("F", "G"), c("F", "G"))
)

现有尝试 #

• 已实现同期（仅当前日期）的ind_id-date聚合：

data_contemp <- data[, .(job_id = list(job_id)), by = .(date, ind_id)]

• 尝试使用frollapply实现滚动聚合，但因该函数不支持非数值型输出而失败：

data[, all_jobs := frollapply(job_id, 3, list), by = ind_id]

性能要求 #

实际数据集规模达6.07亿行，强烈偏好使用data.table解决方案，需兼顾速度、内存效率与语法简洁性。以下是生成超大测试数据的代码：

n <- 600e6
n <- round(n / 15)
  
t1 <- data.table(ind_id = rep(1, 3), date = 1:3, job_id = rep("A", 3))
t2 <- data.table(ind_id = rep(2, 3), date = 1:3, job_id = c("A", "B", "B"))
t3 <- data.table(ind_id = rep(3, 5), date = c(1, 2, 2, 3, 3), job_id = c("A", "A", "B", "A", "B"))
t4 <- data.table(ind_id = rep(4, 2), date = c(1, 3), job_id = c("A", "B"))
t5 <- data.table(ind_id = rep(5, 4), date = c(1, 1, 2, 3), job_id = c("A", "B", "A", "A"))
  
data <- rbind(t1, t2, t3, t4, t5)
data <- data[rep(seq_len(nrow(data)), n)]
data[, ind_id := rleid(ind_id)]

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