如何基于data.table优化大规模住院数据集的关联匹配效率？

数据集概述 #

• 住院数据集：包含9200万条记录，每条以「住院ID-医师ID」为唯一标识，字段涵盖住院/出院日期、所属医院ID等；涉及15万名医师、6000家医院。
• 医师专科数据集：记录医师的所属专科信息，部分医师拥有多个专科资质。

核心需求 #

针对每条标记为sample==1的「住院ID-医师ID」记录，找出满足以下全部条件的其他住院ID：

1. 属于同一医院的住院记录；
2. 该住院的结束日期在目标住院开始日期前30天范围内（即目标住院开始日期 - 结束日期介于1到30天之间）；
3. 仅由同专科的其他医师参与的住院（参与医师的专科与目标医师的专科存在交集，但不是目标医师本人）。

示例数据 #

df <- data.frame(hospitalization_id = c(1, 2, 3,
                                        1, 2, 3,
                                        4, 5, 
                                        6, 7, 8),
                 hospital_id = c("A", "A", "A", 
                                 "A", "A", "A", 
                                 "A", "A",
                                 "B", "B", "B"),
                 physician_id = c(1, 1, 1, 
                                  2, 2, 2,
                                  3, 3, 
                                  2, 2, 2),
                 date_start = as.Date(c("2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-01", "2000-01-12", "2000-01-20",
                                        "2000-01-12", "2000-01-20",
                                        "2000-02-10", "2000-02-11", "2000-02-12")),
                 date_end = as.Date(c("2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-03", "2000-01-18", "2000-01-22",
                                      "2000-01-18", "2000-01-22",
                                      "2000-02-11", "2000-02-14", "2000-02-17")))
df <- df %>%
  mutate(sample = c(0,0,0,0,0,1,1,1,0,0,0))

physician_spec <- data.frame(physician_id = c(1, 2, 2, 3),
                             specialty_code = c(100, 100, 200, 200))

现有低效代码 #

当前实现逻辑可满足需求，但运行效率极低：3天仅完成300家医院的处理。代码如下：

setDT(df)
setDT(physician_spec)

peers_in_spec <- function(p) {
  physician_spec[
    physician_id != p &
      specialty_code %in% physician_spec[physician_id==p, specialty_code],
    physician_id]
}

f <- function(p, st) { 
  peers_in_spec = peers_in_spec(p)
  exclude_hosps = df_hospital[physician_id == p, unique(hospitalization_id)]
  unique(df_hospital[
    physician_id %in% peers_in_spec(p) &
      (st - date_end)>=1 & (st - date_end)<=30 & 
      !hospitalization_id %in% exclude_hosps
  ]$hospitalization_id) 
}

for(h in unique(df$hospital_id)) {
  
  print(paste0("Hospital id: ", h))
  df_hospital <- df[hospital_id==h] 
  
  tryCatch({
    output <- df_hospital[sample==1,
                .(peer_hospid = f(physician_id, date_start)), 
                .(physician_id, hospitalization_id)]
    print(output)
  }, error=function(e){cat("ERROR :",conditionMessage(e), "\n")})

}

优化方案建议 #

1. 预计算医师的同专科peer列表 #

避免重复查询专科数据集，提前生成每个医师对应的同专科其他医师集合：

# 建立专科-医师映射表
spec_phys <- physician_spec[, .(physician_ids = list(unique(physician_id))), by = specialty_code]

# 为每个医师生成同专科peer列表（排除自身）
physician_spec[, .(specialty_code = unique(specialty_code)), by = physician_id] -> phys_spec_unique
phys_spec_unique[, 
                 peer_physicians := lapply(specialty_code, function(s) spec_phys[specialty_code == s, physician_ids][[1]]),
                 by = physician_id]
phys_spec_unique[, peer_physicians := lapply(peer_physicians, function(x) x[x != physician_id])]

# 将peer列表合并到住院数据集
df <- df[phys_spec_unique, on = "physician_id"]

2. 使用data.table非等值连接替代行循环 #

利用data.table的高效非等值连接，替代逐行调用函数的低效逻辑：

setDT(df)
setDT(physician_spec)

# （先执行上述预计算步骤）

result_list <- list()
for (h in unique(df$hospital_id)) {
  cat("处理医院：", h, "\n")
  dt_hospital <- df[hospital_id == h]
  
  # 分离目标记录（需处理的sample==1）和候选记录
  target_dt <- dt_hospital[sample == 1]
  candidate_dt <- dt_hospital[hospitalization_id != target_dt$hospitalization_id] # 排除自身住院记录
  
  # 展开目标记录的peer列表，用于后续连接
  target_expanded <- target_dt[, .(peer_physician_id = unlist(peer_physicians)), 
                               by = .(hospitalization_id, date_start)]
  
  # 非等值连接：匹配peer医师、日期范围
  matched <- target_expanded[candidate_dt, 
                             on = .(peer_physician_id = physician_id,
                                    date_start >= date_end + 1,
                                    date_start <= date_end + 30),
                             .(target_hospid = hospitalization_id,
                               peer_hospid = i.hospitalization_id),
                             allow.cartesian = TRUE]
  
  # 去重：每个目标记录保留唯一的peer住院ID
  matched_unique <- matched[, .(peer_hospid = unique(peer_hospid)), by = target_hospid]
  
  result_list[[h]] <- matched_unique
}

# 合并所有医院的结果
final_result <- rbindlist(result_list)

3. 其他优化细节 #

• 索引优化：为住院数据集设置索引，加速分组与过滤：setkey(df, hospital_id, physician_id, date_end)
• 内存优化：分块处理医院数据，处理完成后及时释放内存；读取数据时仅加载必要字段
• 并行处理：使用foreach+doParallel包对医院循环进行并行计算，利用多核心CPU资源
• 避免重复计算：移除原代码中重复调用peers_in_spec(p)的逻辑

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