PostgreSQL中如何计算消息各处理阶段间的平均耗时

问题说明 #

已经能算出消息从start到end的全生命周期平均耗时，但现在需要计算多个阶段之间的平均耗时（比如start→validation、validation→end，后续还要加parsing、transforming等阶段），自己修改的SQL报错：operator does not exist: interval & interval。

原可用SQL（计算start到end）：

select AVG(e2.timestamp - e.timestamp) avg_gap
from event e
join event e2 on (e.message_id = e2.message_id)
where e.event_stage= 'start' and e.timestamp > '2022-10-01T00:00:08.000001Z' and e.timestamp < '2022-10-31T23:59:59.999999Z'
and e2.event_stage= 'end' and e2.timestamp > '2022-10-01T00:00:08.000001Z' and e2.timestamp < '2022-10-31T23:59:59.999999Z'

错误原因：你用了&位运算符，PostgreSQL里时间间隔（interval）类型不支持位运算，而且这种写法也没法同时输出两个阶段的平均耗时。

解决办法 #

办法1：固定阶段用多关联查询（适合阶段少的情况） #

如果当前只需要算start→validation和validation→end这两段，直接关联同一条消息的三个阶段记录，分别计算两段的平均耗时：

select
  AVG(e2.timestamp - e.timestamp) as avg_start_to_validation,
  AVG(e3.timestamp - e2.timestamp) as avg_validation_to_end
from event e
join event e2 on e.message_id = e2.message_id
join event e3 on e2.message_id = e3.message_id
where
  e.event_stage = 'start'
  and e2.event_stage = 'validation'
  and e3.event_stage = 'end'
  -- 统一过滤时间范围，不用重复写条件
  and e.timestamp between '2022-10-01T00:00:08.000001Z' and '2022-10-31T23:59:59.999999Z'
  and e2.timestamp between '2022-10-01T00:00:08.000001Z' and '2022-10-31T23:59:59.999999Z'
  and e3.timestamp between '2022-10-01T00:00:08.000001Z' and '2022-10-31T23:59:59.999999Z'

办法2：窗口函数通用方案（支持任意加阶段） #

如果后续要加parsing、transforming等更多阶段，推荐用LEAD()窗口函数——给每个消息的事件按时间排序后，自动找到下一个阶段的时间和名称，不用每次加阶段都改关联逻辑，扩展性强：

with event_stages as (
  select
    message_id,
    event_stage,
    timestamp,
    -- 拿到同一条消息的下一个阶段时间戳
    LEAD(timestamp) over (partition by message_id order by timestamp) as next_stage_time,
    -- 拿到同一条消息的下一个阶段名称
    LEAD(event_stage) over (partition by message_id order by timestamp) as next_stage_name
  from event
  where timestamp between '2022-10-01T00:00:08.000001Z' and '2022-10-31T23:59:59.999999Z'
  -- 这里可以直接加后续要统计的阶段，比如'parsing'、'transforming'
  and event_stage in ('start', 'validation', 'parsing', 'transforming', 'end')
)
select
  concat(event_stage, ' → ', next_stage_name) as stage_interval,
  AVG(next_stage_time - timestamp) as avg_processing_time
from event_stages
-- 过滤掉最后一个阶段（end没有下一个阶段）
where next_stage_name is not null
group by event_stage, next_stage_name
order by event_stage;

这个方案的好处：

• 新增阶段时，只需要在in子句里加阶段名称就行，核心逻辑不用改
• 自动按时间顺序关联阶段，避免手动关联可能出现的顺序错误
• 输出结果清晰展示每一段的平均耗时

表结构参考 #

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