如何高效处理带起始日期和时长的药物处方堆叠（R/SQL方案）

使用dplyr分组结合purrr::accumulate实现向量化迭代，避免循环带来的性能损耗：

library(tidyverse)
library(lubridate)

# 预处理：转换日期格式并按患者+起始日期排序
df_processed <- df %>%
  mutate(StartDate = dmy(StartDate)) %>%
  arrange(Patient, StartDate) %>%
  group_by(Patient) %>%
  mutate(
    # 计算原始结束日期
    OriginalEnd = StartDate + days(Duration),
    # 累积计算调整后的起始日期
    AdjustedStart = accumulate(seq_along(StartDate), function(prev_idx, curr_idx) {
      if (curr_idx == 1) {
        StartDate[curr_idx]
      } else {
        max(StartDate[curr_idx], AdjustedEnd[prev_idx] + days(1))
      }
    }),
    # 计算调整后的结束日期
    AdjustedEnd = AdjustedStart + days(Duration)
  ) %>%
  ungroup() %>%
  # 整理输出列
  select(
    Patient,
    OriginalStart = StartDate,
    AdjustedStart,
    Duration,
    OriginalEnd,
    AdjustedEnd
  )

# 查看结果
print(df_processed)

核心逻辑 #

• accumulate是向量化迭代工具，比for循环效率高一个数量级，适合百万级数据
• 每一行的调整后起始日期取原始起始日期与上一行调整后结束日期+1天的较大值，同时覆盖重叠和无重叠场景
• 必须先按患者+起始日期排序，确保处理顺序正确

利用递归CTE和窗口函数处理逐行依赖，适合直接在数据库中处理大规模数据（以PostgreSQL为例，其他数据库可调整日期函数）：

WITH ranked_prescriptions AS (
  -- 按患者分组，处方按起始日期排序并生成行号
  SELECT
    Patient,
    StartDate,
    Duration,
    TO_DATE(StartDate, 'DD/MM/YYYY') AS OriginalStart,
    ROW_NUMBER() OVER (PARTITION BY Patient ORDER BY TO_DATE(StartDate, 'DD/MM/YYYY')) AS rn
  FROM prescriptions_table
),
recursive_adjustment AS (
  -- 递归计算调整后的起始/结束日期
  SELECT
    Patient,
    OriginalStart,
    Duration,
    rn,
    OriginalStart + INTERVAL '1 day' * Duration AS OriginalEnd,
    OriginalStart AS AdjustedStart,
    OriginalStart + INTERVAL '1 day' * Duration AS AdjustedEnd
  FROM ranked_prescriptions
  WHERE rn = 1
  
  UNION ALL
  
  SELECT
    rp.Patient,
    rp.OriginalStart,
    rp.Duration,
    rp.rn,
    rp.OriginalStart + INTERVAL '1 day' * rp.Duration AS OriginalEnd,
    GREATEST(rp.OriginalStart, ra.AdjustedEnd + INTERVAL '1 day') AS AdjustedStart,
    GREATEST(rp.OriginalStart, ra.AdjustedEnd + INTERVAL '1 day') + INTERVAL '1 day' * rp.Duration AS AdjustedEnd
  FROM ranked_prescriptions rp
  JOIN recursive_adjustment ra 
    ON rp.Patient = ra.Patient AND rp.rn = ra.rn + 1
)
-- 格式化输出日期
SELECT
  Patient,
  TO_CHAR(OriginalStart, 'DD/MM/YYYY') AS OriginalStart,
  TO_CHAR(AdjustedStart, 'DD/MM/YYYY') AS AdjustedStart,
  Duration,
  TO_CHAR(OriginalEnd, 'DD/MM/YYYY') AS OriginalEnd,
  TO_CHAR(AdjustedEnd, 'DD/MM/YYYY') AS AdjustedEnd
FROM recursive_adjustment
ORDER BY Patient, rn;

数据库适配说明 #

• MySQL：替换日期函数为STR_TO_DATE(StartDate, '%d/%m/%Y')，日期加减用DATE_ADD(..., INTERVAL ... DAY)
• SQL Server：替换日期函数为CONVERT(date, StartDate, 103)，日期加减用DATEADD(day, ..., ...)

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