Postgresql按创建日期排序，按事务id分组

Question

我的表格格式如下

状态>updated_at已完成失败待决2022:08-05 9:22-34待定2022:08-01 4:05待决

	id	transaction_id	created_at
uuid-1	a293b1fe0369e0198df3293a8aef9c97ea532b30	2022-08-25 02:32:44	2022:08-25 02:302:44
uuid-2	a293b1fe0369e0198df3293a8aef9c97ea532b24	2022-08-24 12:33:22	2022:08-24 12:32:22
uuid-3	3b97c805fc7ce0119433c5284102b47781f9f66	2022-08-24 12:30:22	2022:08-24 12:33:22
uuid-4	a293b1fe0369e0198df3293a8aef9c97ea532b30	失败	2022-08-23 9:32:14
uuid-5	a293b1fe0369e0198df3293a8aef9c97ea532b30	失败	2022-08-05 9:22:34
uuid-6	a293b1fe0369e0198df3293a8aef9c97ea532b24	2022-08-04 03:33:12	2022:08-04 03:303:12
uuid-7	a293b1fe0369e0198df3293a8aef9c97ea532b30	失败	2022-08-01 4:04:25
uuid-8	a293b1fe0369e0198df3293a8aef9c97ea532b30	2022-07-20 7:43:22	2022-07-200 7:43:22

我会在这里使用ROW_NUMBER()和MAX()：

WITH cte AS (
SELECT *, MAX(created_at) OVER (PARTITION BY transaction_id) AS max_created_at,
ROW_NUMBER() OVER (PARTITION BY transaction_id
ORDER BY CASE status WHEN 'pending'   THEN 1
WHEN 'failed'    THEN 2
WHEN 'completed' THEN 3 END,
created_at) rn
FROM transactions
)
SELECT transaction_id, status, created_at, updated_at
FROM cte
ORDER BY max_created_at DESC, rn;

排序逻辑是，我们首先使用最近创建的时间戳对属于同一事务的每个记录块进行排序。接下来，在每个块中，我们按状态排序。最后，对于具有相同状态的两个或多个记录，我们再次使用创建的时间戳打破这种联系。