合并两个事件跟踪表，生成含事件起止日期的结果表

现有两个事件跟踪表，分别记录不同事件的发生日期，需要合并后为每个事件生成开始日期和结束日期，规则为：

• 每个事件的Event_Start_Date等于自身的Event_Date
• 每个事件的Event_End_Date等于后续事件的Event_Date（最后一个事件可根据需求指定默认结束日期）

原始表结构及数据 #

表1（事件xxxxxxxxxx） #

表2（事件yyyyyyyyyy） #

解决方案（SQL实现） #

由于所有事件名称已知且顺序固定，可通过合并数据+窗口函数的方式实现需求：

完整SQL代码 #

WITH combined_events AS (
    SELECT ID, Event_Name, Event_Date,
           -- 按已知事件顺序指定排序优先级，新增事件直接追加序号即可
           CASE Event_Name
               WHEN 'xxxxxxxxxx' THEN 1
               WHEN 'yyyyyyyyyy' THEN 2
           END AS event_order
    FROM table1
    UNION ALL
    SELECT ID, Event_Name, Event_Date,
           CASE Event_Name
               WHEN 'xxxxxxxxxx' THEN 1
               WHEN 'yyyyyyyyyy' THEN 2
           END AS event_order
    FROM table2
)
SELECT 
    ID,
    Event_Name,
    Event_Date AS Event_Start_Date,
    -- LEAD函数获取下一个事件的日期作为结束日期，第三个参数为无后续事件时的默认值
    LEAD(Event_Date, 1, '2019-12-05') OVER (PARTITION BY ID ORDER BY event_order) AS Event_End_Date
FROM combined_events
ORDER BY ID, event_order;

执行结果 #

关键说明 #

• event_order字段确保事件按业务逻辑指定的顺序排列，避免日期排序可能出现的混乱
• LEAD()窗口函数用于提取同ID分组内下一个事件的日期，默认值可根据实际需求调整为NULL或其他日期

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