试试这个：

SELECT a.*
FROM (
SELECT delivery_id
     , lag(id, 1) OVER w AS scheduled_id
     , lag(created, 1) OVER w AS scheduled_time
     , lag(event , 1) OVER w AS scheduled_event
     , id AS unscheduled_id
     , created AS unscheduled_time
     , event AS unscheduled_event
     , extra
  FROM delivery_event
 WHERE delivery_id = 10005
WINDOW w AS (PARTITION BY delivery_id ORDER BY created ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
) AS a
WHERE a.scheduled_event = 2
  AND a.unscheduled_event = 3
  AND a.extra = '{"timeout"}' :: json

我能想到两种解决方案。最简单的可能是使用distinct on子句来删除所有具有重复unscheduled_id的行，并在scheduled.created上使用order by子句来确保返回正确的行。

SELECT DISTINCT ON (unscheduled.id)
    scheduled.id as scheduled_id, 
    scheduled.created as scheduled_time, 
    scheduled.event as scheduled_event, 
    scheduled.delivery_id as delivery_id,
    unscheduled.id as unscheduled_id, 
    unscheduled.created as unscheduled_time, 
    unscheduled.event as unscheduled_event, 
    unscheduled.extra as extra
FROM
    delivery_event scheduled_event
JOIN
    delivery_event unscheduled_event ON scheduled.delivery_id = 10005 
    AND unscheduled.delivery_id = 10005 
    AND unscheduled.event = 3 
    AND scheduled.event = 2 
    AND scheduled.created < unscheduled.created
    AND unscheduled.extra->>'timeout'
ORDER BY scheduled.created DESC

另一种解决方案是使用左侧连接，以便只连接最近安排的事件。

SELECT
    scheduled.id as scheduled_id, 
    scheduled.created as scheduled_time, 
    scheduled.event as scheduled_event, 
    scheduled.delivery_id as delivery_id,
    unscheduled.id as unscheduled_id, 
    unscheduled.created as unscheduled_time, 
    unscheduled.event as unscheduled_event, 
    unscheduled.extra as extra
FROM
    delivery_event unscheduled_event
JOIN
    LEFT JOIN LATERAL (
        SELECT scheduled.id, 
        scheduled.created, 
        scheduled.event, 
        scheduled.delivery_id
        FROM delivery_event scheduled
        WHERE scheduled.delivery_id = 10005 
        AND scheduled.event = 2 
        AND scheduled.created < unscheduled.created
        ORDER BY scheduled.created DESC 
    LIMIT 1) scheduled ON TRUE 
WHERE unscheduled.delivery_id = 10005 
    AND unscheduled.event = 3 
    AND unscheduled.extra->>'timeout'

对于这样的连接表，需要注意一点：尽管只使用一个相关行进行连接非常方便，但它往往会带来严重的性能缺陷，因此请务必检查这两种方法中的任何一种方法的性能是否满足您的特定环境。