PostgreSQL：从坐席状态表生成类CDR视图/表

需求说明 #

现有呼叫中心坐席状态变更记录表（假设表名为agent_status_changes），结构如下：

需要将其转换为包含状态时间区间和持续时长的视图/表，适配PowerBI使用，目标结构：

核心规则：

• 状态的结束时间为该坐席下一次状态变更的时间戳
• 计算状态持续时长（单位：秒）

解决方案（PostgreSQL SQL） #

使用PostgreSQL的窗口函数LEAD()获取每个坐席的下一次状态变更时间，再计算时长：

CREATE VIEW agent_status_intervals AS
SELECT
    时间戳 AS 开始时间,
    LEAD(时间戳) OVER (PARTITION BY 坐席 ORDER BY 时间戳) AS 结束时间,
    坐席,
    状态,
    -- 将时间差转换为秒数
    EXTRACT(EPOCH FROM (LEAD(时间戳) OVER (PARTITION BY 坐席 ORDER BY 时间戳) - 时间戳)) AS "持续时长（秒）"
FROM agent_status_changes
-- 过滤无结束时间的最新状态记录（如需保留可删除此条件）
WHERE LEAD(时间戳) OVER (PARTITION BY 坐席 ORDER BY 时间戳) IS NOT NULL
ORDER BY 坐席, 开始时间;

代码解释 #

1. PARTITION BY 坐席：按坐席分组，确保仅获取当前坐席的后续状态变更时间
2. LEAD(时间戳) OVER (...)：窗口函数，提取同一坐席分组内按时间排序后的下一行时间戳，作为当前状态的结束时间
3. EXTRACT(EPOCH FROM ...)：将时间差转换为秒级数值，得到状态持续时长
4. WHERE 子句：过滤每个坐席的最后一条状态记录（无后续变更则无结束时间），若需保留未结束的最新状态，可移除该条件

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