PostgreSQL中如何筛选超时非计划事件的最近计划事件

表结构与示例数据 #

DeliveryEvent表按delivery_id分组，事件类型定义：

• event_type=2：scheduled（计划事件）
• event_type=3：unscheduled（非计划事件）
• event_type=4：completed（完成事件）

需求 #

针对每个因timeout产生的非计划事件（event_type=3且extra包含timeout），获取其紧接的前一个计划事件，用于计算两者的时间间隔。

原查询问题 #

当前执行的SQL会返回每个超时非计划事件与所有之前计划事件的组合，不符合需求（已修正原SQL的表别名与extra判断错误）：

SELECT    
    scheduled.id as scheduled_id, 
    scheduled.created as scheduled_time, 
    scheduled.event_type as scheduled_event, 
    scheduled.delivery_id as delivery_id,
    unscheduled.id as unscheduled_id, 
    unscheduled.created as unscheduled_time, 
    unscheduled.event_type as unscheduled_event, 
    unscheduled.extra as extra
FROM    
    delivery_event scheduled
JOIN    
    delivery_event unscheduled ON scheduled.delivery_id = 10005 
    AND unscheduled.delivery_id = 10005 
    AND unscheduled.event_type = 3 
    AND scheduled.event_type = 2 
    AND scheduled.created < unscheduled.created
    AND unscheduled.extra->>'timeout' IS NOT NULL

该查询会出现一个超时事件匹配多个历史计划事件的情况，我们需要的是每个超时事件对应的最近的前一个计划事件。

解决方案 #

方法1：使用LATERAL JOIN（PostgreSQL适用） #

针对每个超时事件，直接关联查询出最近的前一个计划事件：

SELECT
    s.id as scheduled_id,
    s.created as scheduled_time,
    s.event_type as scheduled_event,
    s.delivery_id,
    u.id as unscheduled_id,
    u.created as unscheduled_time,
    u.event_type as unscheduled_event,
    u.extra
FROM
    delivery_event u
LEFT JOIN LATERAL (
    SELECT *
    FROM delivery_event s
    WHERE s.delivery_id = u.delivery_id
      AND s.event_type = 2
      AND s.created < u.created
    ORDER BY s.created DESC
    LIMIT 1
) s ON TRUE
WHERE
    u.event_type = 3
    AND u.extra->>'timeout' IS NOT NULL
    AND u.delivery_id = 10005;

方法2：使用窗口函数（通用SQL） #

先为每个事件标记出之前最近的计划事件时间，再关联匹配对应的计划事件：

WITH all_events AS (
    SELECT
        *,
        -- 为每个事件找到之前最近的计划事件时间
        MAX(CASE WHEN event_type = 2 THEN created END) OVER (
            PARTITION BY delivery_id
            ORDER BY created
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS last_scheduled_time
    FROM delivery_event
    WHERE delivery_id = 10005
)
SELECT
    s.id as scheduled_id,
    s.created as scheduled_time,
    s.event_type as scheduled_event,
    s.delivery_id,
    u.id as unscheduled_id,
    u.created as unscheduled_time,
    u.event_type as unscheduled_event,
    u.extra
FROM all_events u
JOIN delivery_event s 
    ON s.delivery_id = u.delivery_id
    AND s.created = u.last_scheduled_time
    AND s.event_type = 2
WHERE
    u.event_type = 3
    AND u.extra->>'timeout' IS NOT NULL;

预期结果 #

执行上述任一查询后，将得到每个超时非计划事件对应的最近计划事件：

内容的提问来源于stack exchange，提问作者Laís Alencar