如何在PostgreSQL中根据某些条件从其他列中筛选最后一行

Question

假设我有一个表(DeliveryEvent)，像这样由delivery_id分组，其中event_type2是'计划'的事件，3是'未计划'的事件，4是'完成'的事件:

<表类> id 创建event_typedelivery_id的tbody><<tr>12022-10-27 18:0421000522022-10-27点310005{"不能deliver"32022-10-27第19章20节21000542022-10-27 20:30310005{"timeout"52022-10-27) 21:1521000562022-10-27 22:40310005{"timeout"72022-10-27 22:5521000582022-10-2723点410005

试试这个:

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子句删除所有具有重复unschedule_id的行，并使用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'

对于这样的连接表有一个警告。虽然只与一个相关行连接非常方便，但往往会带来明显的性能缺陷，因此请务必检查这两种方法中的任何一种的性能是否适合您的特定情况。