PostgreSQL：如何将多会话多行数据合并为单行结构化结果？

我有一张简化后的CDR表，结构及示例数据如下：

系统中可能同时存在多个会话，需要编写高效的SQL查询，将同一个session_id下不同action对应的timestamp转换为如下单行结构化结果：

后续会将部分字段改为时长计算，但之前尝试遍历session_id的循环方式性能极差，虽可通过后端代码将结果存入临时表，但更希望直接创建视图实现。经过摸索，最终得到可用于生成KPI报表的查询语句：

select
    session_id,
    initial,
    queued - initial as time_to_queue, -- 初始化到入队时长
    connected - queued as time_in_queue, -- 队列等待时长
    hangup - connected as time_in_call, -- 通话时长
    hangup - initial as total_time, -- 会话总时长
    hangup
from
(SELECT
    session_id,
    MIN(CASE WHEN action = '4363d58b-c9fe-43a1-b636-c65822329aa3' THEN timestamp END) AS initial,
    MIN(CASE WHEN action = 'd4294aaf-3fee-4154-a3de-b2f9c05a0cf1' THEN timestamp END) AS queued,
    MIN(CASE WHEN action = 'dc40eaec-2aed-4b24-9b8e-ff1e25194036' THEN timestamp END) AS connected,
    MIN(CASE WHEN action = 'dd93f0d4-7db9-4a68-876b-956db9300841' THEN timestamp END) AS hangup
FROM
    cdr
where date(((timestamp at TIME zone 'UTC') at TIME zone 'US/Eastern')::timestamptz) >= '2023-12-07' -- 筛选美东时区2023-12-07及之后的数据
GROUP BY
    session_id) x
where initial is not null -- 过滤外呼通话

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