使用Teradata/SQL按4周时间窗口移除周期内重复检测记录

需求规则 #

数据集基础字段包含ID（患者唯一标识）、Date（检测日期）、Agent（检测项），以及衍生的Week（周）、Month（月）、Year（年）维度字段，同一ID对应患者存在多条检测记录，需要按ID分组执行迭代筛选：

• 每组内先保留时间最早的检测记录，删除该记录日期之后4周内的所有其余检测记录
• 对筛选后剩余的记录重复上一步操作：保留剩余记录中时间最早的记录，删除其后4周内的其余记录
• 循环执行直到遍历完该ID下所有记录

规则说明：4周按自然日28天计算，同一天的多条不同检测项记录属于同一次检测，需全部保留；若存在同一天同一检测项的重复录入记录，可提前去重保留第一条。

示例输入 #

ID Date        Week Month Year  Agent
1    1  2010-12-09  49   12    2010  Agent1
2    1  2010-12-09  49   12    2010  Agent2 
3    1  2010-12-09  49   12    2010  Agent3 
4    1  2010-12-09  49   12    2010  Agent4 
5    1  2010-12-09  49   12    2010  Agent1 
6    1  2010-12-09  49   12    2010  Agent2 
7    1  2010-12-09  49   12    2010  Agent3
8    1  2010-12-09  49   12    2010  Agent4
9    1  2010-12-27  52   12    2010  Agent1
10   1  2010-12-27  52   12    2010  Agent2
11   1  2010-12-27  52   12    2010  Agent3
12   1  2010-12-27  52   12    2010  Agent4
13   1  2011-01-14  2    1     2011  Agent1
14   1  2011-01-14  2    1     2011  Agent2
15   1  2011-01-14  2    1     2011  Agent3
16   1  2011-01-14  2    1     2011  Agent4
17   1  2011-01-14  2    1     2011  Agent1
18   1  2011-01-14  2    1     2011  Agent2
19   1  2011-01-14  2    1     2011  Agent3
20   1  2011-01-14  2    1     2011  Agent4

示例期望输出 #

ID Date        Week Month Year  Agent
1    1  2010-12-09  49   12    2010  Agent1
2    1  2010-12-09  49   12    2010  Agent2 
3    1  2010-12-09  49   12    2010  Agent3 
4    1  2010-12-09  49   12    2010  Agent4 
13   1  2011-01-14  2    1     2011  Agent1
14   1  2011-01-14  2    1     2011  Agent2
15   1  2011-01-14  2    1     2011  Agent3
16   1  2011-01-14  2    1     2011  Agent4

实现代码 #

不需要逐行遍历全量数据，只需要对每个患者的唯一检测日期做判断，执行效率更高，以下提供两种常用工具链的实现：

R语言（dplyr）实现 #

library(dplyr)
library(lubridate)

result <- df %>%
  # 日期格式转换 + 排序
  mutate(Date = ymd(Date)) %>%
  arrange(ID, Date) %>%
  # 同ID、同日期、同检测项去重，和示例输出对齐
  distinct(ID, Date, Agent, .keep_all = TRUE) %>%
  # 按患者分组处理
  group_by(ID) %>%
  group_modify(~{
    # 取组内所有唯一检测日期，按升序排列
    all_dates <- sort(unique(.x$Date))
    # 初始化保留日期列表，第一个日期必然保留
    keep_dates <- c(all_dates[1])
    last_keep <- all_dates[1]
    # 遍历后续日期，间隔满28天就加入保留列表
    for(d in all_dates[-1]){
      if(d - last_keep >= 28){
        keep_dates <- c(keep_dates, d)
        last_keep <- d
      }
    }
    # 提取所有保留日期对应的记录
    .x[.x$Date %in% keep_dates, ]
  }) %>%
  ungroup()

Python（pandas）实现 #

import pandas as pd
from datetime import timedelta

# 日期格式转换 + 排序
df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values(by=['ID', 'Date']).reset_index(drop=True)
# 同ID、同日期、同检测项去重，和示例输出对齐
df = df.drop_duplicates(subset=['ID', 'Date', 'Agent'], keep='first').reset_index(drop=True)

keep_groups = []
for patient_id, group in df.groupby('ID', sort=False):
    all_dates = sorted(group['Date'].unique())
    # 初始化保留日期列表
    keep_dates = [all_dates[0]]
    last_keep = all_dates[0]
    # 遍历后续日期判断间隔
    for d in all_dates[1:]:
        if d - last_keep >= timedelta(weeks=4):
            keep_dates.append(d)
            last_keep = d
    # 提取保留记录
    keep_groups.append(group[group['Date'].isin(keep_dates)])

result = pd.concat(keep_groups).reset_index(drop=True)

逻辑验证 #

对照示例数据执行代码：

1. 第一个保留日期为2010-12-09，下一个检测日期2010-12-27和它间隔18天，不满28天，对应记录全部剔除
2. 再下一个检测日期2011-01-14和2010-12-09间隔36天，满足4周间隔要求，对应记录全部保留
3. 同天重复的同检测项记录提前去重，最终输出和期望结果完全一致。

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