如何使用窗口函数计算各type分组内start到finish的时间差

你可以直接通过按type分区的聚合窗口函数，一次查询完成计算，不需要多结果集关联，具体实现如下：

通用SQL实现（兼容绝大多数支持窗口函数的数据库） #

SELECT DISTINCT
    type,
    MIN(CASE WHEN event = 'start' THEN time END) OVER (PARTITION BY type) AS start_time,
    MAX(CASE WHEN event = 'result' THEN time END) OVER (PARTITION BY type) AS finish_time,
    -- 计算时长，不同数据库可替换为对应的时间差函数，如DATEDIFF、TIMESTAMPDIFF等
    MAX(CASE WHEN event = 'result' THEN time END) OVER (PARTITION BY type) 
    - MIN(CASE WHEN event = 'start' THEN time END) OVER (PARTITION BY type) AS duration
FROM 你的表名

逻辑说明 #

• 用PARTITION BY type将数据按type分组，窗口函数在每个分组内独立计算
• 通过CASE WHEN筛选出对应事件的时间，再配合聚合窗口函数取值：
  • 每个type仅对应1条start事件，用MIN/MAX都可以取出唯一的start时间
  • 每个type下的最大result时间直接通过MAX()窗口函数获取，无需额外分组聚合
• 末尾加DISTINCT去重，避免每个type返回多条重复的计算结果

如果你用的是支持FILTER语法的数据库（比如PostgreSQL），可以简化写法：

SELECT DISTINCT
    type,
    MIN(time) FILTER (WHERE event = 'start') OVER (PARTITION BY type) AS start_time,
    MAX(time) FILTER (WHERE event = 'result') OVER (PARTITION BY type) AS finish_time,
    MAX(time) FILTER (WHERE event = 'result') OVER (PARTITION BY type) 
    - MIN(time) FILTER (WHERE event = 'start') OVER (PARTITION BY type) AS duration
FROM 你的表名

这个方案只需要扫描一次原表，相比两次子查询关联的写法性能更好，逻辑也更简洁。

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