PostgreSQL实现按交易ID分组并按指定规则排序结果的方法

表结构 #

排序规则 #

• 最新提交的交易排在最顶部（按每个交易组的最晚创建时间倒序排列）
• 每个交易下的操作记录按指定状态顺序排序：pending → failed → completed，同状态下按创建时间正序排列

期望结果 #

尝试的SQL #

select
    transaction_id,
    created_at,
    status,
    RANK() over (partition by transaction_id order by created_at) group_rank
from
    transactions;

解决方案 #

要实现需求，需要同时处理交易组的排序和组内记录的排序，最终SQL如下：

select
    transaction_id,
    status,
    created_at,
    updated_at
from (
    select
        transaction_id,
        status,
        created_at,
        updated_at,
        -- 获取每个交易组的最晚创建时间，用于交易组排序
        max(created_at) over (partition by transaction_id) as latest_transaction_time,
        -- 为状态分配自定义排序优先级
        case status
            when 'pending' then 1
            when 'failed' then 2
            when 'completed' then 3
        end as status_order
    from transactions
) t
-- 按规则排序：交易组最新时间倒序 → 状态优先级正序 → 创建时间正序
order by
    latest_transaction_time desc,
    status_order asc,
    created_at asc;

逻辑说明 #

1. 交易组排序：通过max(created_at) over (partition by transaction_id)计算每个交易的最晚提交时间，以此作为组的排序依据，确保最新提交的交易排在最前
2. 组内状态排序：用case语句给每个状态分配排序值，实现pending→failed→completed的指定顺序
3. 同状态排序：同状态下按created_at正序排列，符合创建顺序要求

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