PostgreSQL无需crosstab实现行转列并提取最新字段值

先明确你的核心需求：

• 从行式存储的ticket_events表中提取指定key对应的字段：plan_tier(key=22820430)、status(key='status')、category(key=360050595934)
• status和plan_tier需保留每个工单的最后一条记录值
• 要获取每个工单的创建时间，以及status变为closed时的关闭时间
• 禁止使用crosstab函数

你的现有SQL存在几个关键问题：

1. 关联条件错误：用te.created_at = t.created_at关联工单事件和工单表，会导致只有事件创建时间和工单创建时间完全一致的记录才会被匹配，直接丢失大部分工单事件数据，正确关联条件应该是te.ticket_id = t.ticket_id。
2. 分组逻辑不符合需求：按te.created_at分组会把同一个工单的不同事件拆分成多行，根本无法聚合出每个工单的最后status和plan_tier值。
3. 未覆盖关闭时间需求：现有SQL完全没处理提取status为closed时时间戳的需求。

优化后的SQL语句 #

WITH latest_events AS (
    SELECT
        ticket_id,
        key,
        value,
        created_at,
        -- 按工单分组，对每个key的记录按时间倒序排序，标记最新的一条
        ROW_NUMBER() OVER (PARTITION BY ticket_id, key ORDER BY created_at DESC) AS rn
    FROM ticket_events
    WHERE key IN (22820430, 'status', 360050595934)
),
closed_times AS (
    SELECT
        ticket_id,
        created_at AS closed_at
    FROM ticket_events
    WHERE key = 'status' AND value = 'closed'
    -- 取每个工单最后一次关闭的时间（处理多次关闭的情况）
    QUALIFY ROW_NUMBER() OVER (PARTITION BY ticket_id ORDER BY created_at DESC) = 1
)
SELECT
    t.ticket_id,
    t.type,
    t.created_at AS ticket_created_at,
    -- 提取最新的plan_tier值
    MAX(CASE WHEN le.key = 22820430 THEN le.value END) AS plan_tier,
    -- 提取最新的status值
    MAX(CASE WHEN le.key = 'status' THEN le.value END) AS status,
    -- 提取最新的category值
    MAX(CASE WHEN le.key = 360050595934 THEN le.value END) AS category,
    ct.closed_at
FROM tickets t
LEFT JOIN latest_events le ON t.ticket_id = le.ticket_id AND le.rn = 1
LEFT JOIN closed_times ct ON t.ticket_id = ct.ticket_id
WHERE t.created_at > '2023-06-01'
GROUP BY t.ticket_id, t.type, t.created_at, ct.closed_at
ORDER BY t.created_at DESC;

优化说明 #

1. latest_events CTE：用窗口函数ROW_NUMBER()给每个工单下的指定key记录按时间倒序排号，标记出最新的那条（rn=1），确保后续聚合时拿到的是每个字段的最后更新值。
2. closed_times CTE：单独提取每个工单最后一次变为closed状态的时间，避免同一个工单多次关闭时取到旧的时间。
3. 关联与聚合：通过LEFT JOIN关联工单表和两个CTE，用MAX(CASE...)聚合出每个工单对应的字段值，同时完整保留工单创建时间和关闭时间。
4. 逻辑修正：彻底修复了原SQL的关联错误，确保工单和其所有事件正确关联，完全覆盖了你提出的所有需求点。

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