Python/Pandas：为每个ID匹配起始日期前的最近历史事件

我来帮你搞定这个需求——针对每个ID，找到其起始日期（包含当天）之前发生的最近历史事件，没有匹配事件时返回NULL。下面提供几种适配不同数据库的解决方案：

方案一：窗口函数（推荐，支持现代数据库） #

如果你的数据库支持窗口函数（比如MySQL 8.0+、PostgreSQL 9.4+、SQL Server 2012+等），这是最清晰高效的写法：

WITH ranked_events AS (
    SELECT 
        t1.id,
        t1.start_date,
        t2.event_date,
        t2.event,
        -- 按ID分组，事件日期倒序排序，同一天的事件按event倒序取最后一个（匹配你的示例结果）
        ROW_NUMBER() OVER (PARTITION BY t1.id ORDER BY t2.event_date DESC, t2.event DESC) AS rn
    FROM 表1 t1
    -- 左连接保留所有表1的ID，同时筛选事件日期<=起始日期的记录
    LEFT JOIN 表2 t2 ON t1.id = t2.id AND t2.event_date <= t1.start_date
)
SELECT id, start_date, event_date, event
FROM ranked_events
-- 取每个ID的第一条记录，或者无匹配时的NULL记录
WHERE rn = 1 OR rn IS NULL;

逻辑说明： #

1. 用LEFT JOIN关联两张表，确保表1的所有ID都被保留；
2. 窗口函数ROW_NUMBER()给每个ID的符合条件的事件按日期从新到旧排序，同一天的事件按event字段倒序（保证ID2的Paid被选中，完全匹配你的示例）；
3. 最后筛选出每个ID的第一条记录（排名为1），或者无匹配事件的记录（rn为NULL）。

方案二：子查询（兼容旧版数据库） #

如果你的数据库不支持窗口函数（比如MySQL 5.x），可以用子查询先找出每个ID的最晚符合条件事件日期，再关联回事件表获取具体内容：

SELECT 
    t1.id,
    t1.start_date,
    t2.event_date,
    t2.event
FROM 表1 t1
-- 第一步：找到每个ID的最晚事件日期，且该日期<=起始日期
LEFT JOIN (
    SELECT 
        id,
        MAX(event_date) AS max_event_date
    FROM 表2
    GROUP BY id
) t2_max ON t1.id = t2_max.id AND t2_max.max_event_date <= t1.start_date
-- 第二步：关联回事件表，拿到对应日期的事件内容
LEFT JOIN 表2 t2 ON t2_max.id = t2.id AND t2_max.max_event_date = t2.event_date
ORDER BY t1.id;

补充：LATERAL JOIN 写法（部分数据库支持） #

如果你的数据库支持LATERAL JOIN（比如PostgreSQL、SQL Server），可以用更直观的写法，直接为每个ID查询符合条件的最近事件：

SELECT 
    t1.id,
    t1.start_date,
    t2.event_date,
    t2.event
FROM 表1 t1
LEFT JOIN LATERAL (
    SELECT event_date, event
    FROM 表2
    WHERE id = t1.id AND event_date <= t1.start_date
    ORDER BY event_date DESC, event DESC
    LIMIT 1
) t2 ON true;

这个写法逻辑最直接：对表1的每个ID，从表2中筛选出日期<=起始日期的记录，按日期倒序取第一条，没有则返回NULL。

验证你的示例数据 #

用上述任意方案运行你的示例数据，都会得到你期望的结果：

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