如何高效按时间间隔动态过滤含重复PersonID的数据集？

给定包含重复PersonID及多条日期记录的数据集，需按以下规则筛选记录：

• 保留每个PersonID的首条记录
• 过滤掉与已保留记录间隔3天内的后续记录
• 重复上述逻辑直至处理完所有记录

原数据集 #

目标保留数据集 #

高效实现方案 #

1. SQL（以PostgreSQL为例） #

用递归CTE可避免逐行循环，一次性完成筛选：

WITH RECURSIVE filtered_records AS (
    -- 先取每个PersonID的第一条记录
    SELECT PersonID, Date
    FROM (
        SELECT 
            PersonID, 
            Date,
            ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Date) AS rn
        FROM original_table
    ) t
    WHERE rn = 1

    UNION ALL

    -- 递归查找下一条符合条件的记录：日期比上一条保留的日期晚3天以上，且是该用户最早的符合条件的记录
    SELECT 
        t.PersonID, 
        t.Date
    FROM filtered_records fr
    JOIN (
        SELECT 
            PersonID, 
            Date,
            ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Date) AS rn
        FROM original_table ot
        WHERE ot.Date > (SELECT MAX(Date) FROM filtered_records WHERE PersonID = ot.PersonID) + INTERVAL '3 days'
    ) t ON fr.PersonID = t.PersonID AND t.rn = 1
)
SELECT DISTINCT PersonID, Date FROM filtered_records ORDER BY PersonID, Date;

2. Python Pandas #

分组后用自定义函数遍历筛选，代码简洁高效：

import pandas as pd

# 构造原数据（实际场景可替换为读取文件）
df = pd.DataFrame({
    'PersonID': [1,1,1,1,2,2,3,3,3],
    'Date': pd.to_datetime(['2024-01-01','2024-01-02','2024-01-09','2024-01-15','2024-08-05','2024-08-06','2024-01-07','2024-01-08','2024-01-15'])
})

def filter_user_dates(group):
    # 组内按日期排序
    sorted_group = group.sort_values('Date').reset_index(drop=True)
    keep_indices = [0]  # 首条必保留
    last_kept_date = sorted_group.loc[0, 'Date']
    
    for idx in range(1, len(sorted_group)):
        current_date = sorted_group.loc[idx, 'Date']
        if (current_date - last_kept_date).days > 3:
            keep_indices.append(idx)
            last_kept_date = current_date
    
    return sorted_group.loc[keep_indices]

# 按PersonID分组应用筛选
result = df.groupby('PersonID', group_keys=False).apply(filter_user_dates)
print(result)

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