R语言如何查找同ID分组下早于指定日期的最近历史日期

方案1：窗口函数实现（适用支持窗口函数的数据库：MySQL8.0+/PostgreSQL/Spark SQL/Hive等） #

SELECT 
    ID,
    visit_date,
    MAX(coupon_date) OVER (
        PARTITION BY ID 
        ORDER BY visit_date ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS most_recent_coupon_date
FROM 你的表名
ORDER BY ID, visit_date DESC;

逻辑说明：

• 按用户ID分组，每个用户的到店记录单独计算
• 组内按到店日期升序排序，保证日期按时间先后排列
• 窗口范围限定为当前用户最早的到店记录到当前记录，MAX函数会自动忽略空值的coupon_date，取到的最大值就是当前到店时间前（含本次）最近一次使用优惠券的日期，完全匹配你给出的期望输出。

方案2：自连接实现（兼容无窗口函数的低版本数据库，如MySQL5.x） #

SELECT 
    t1.ID,
    t1.visit_date,
    MAX(t2.coupon_date) AS most_recent_coupon_date
FROM 你的表名 t1
LEFT JOIN 你的表名 t2 
    ON t1.ID = t2.ID 
    AND t2.visit_date <= t1.visit_date 
    AND t2.coupon_date IS NOT NULL
GROUP BY t1.ID, t1.visit_date
ORDER BY t1.ID, t1.visit_date DESC;

逻辑说明：

• 将每条到店记录和同用户所有早于等于当前到店日期的记录做左关联，同时过滤掉关联侧未使用优惠券的记录
• 按当前记录的用户ID和到店日期分组，取关联侧最大的优惠券日期即可得到目标结果。

注意事项 #

• 执行计算前请确认visit_date和coupon_date为日期类型，若为字符串格式请先转换为日期类型，避免日期比较出错：
  • MySQL转换示例：STR_TO_DATE(visit_date, '%d/%m/%Y')
  • PostgreSQL转换示例：TO_DATE(visit_date, 'DD/MM/YYYY')
• 若数据库支持IGNORE NULLS参数，也可以用LAST_VALUE(coupon_date) IGNORE NULLS OVER (PARTITION BY ID ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)实现，效果和方案1完全一致。

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