PostgreSQL按时间戳与名称查询连续同值记录的起止时间区间

这个需求属于经典的「连续相同值分组（岛屿问题）」，可以通过窗口函数嵌套实现，具体步骤如下：

实现思路 #

1. 按时间升序排序后，判断当前行的name和上一行是否一致，不一致则标记为分组边界
2. 对边界标记做滚动累加，生成连续相同name的唯一组标识
3. 按组标识和name聚合，取每组的最小、最大时间戳即可

完整查询语句 #

WITH step1 AS (
    -- 标记分组边界：当前name和上一行不同则标记为1
    SELECT 
        ts,
        name,
        CASE WHEN name = LAG(name) OVER (ORDER BY ts) THEN 0 ELSE 1 END AS is_new_group
    FROM process_steps -- 替换为你的实际表名
),
step2 AS (
    -- 滚动累加边界标记，生成组ID
    SELECT 
        ts,
        name,
        SUM(is_new_group) OVER (ORDER BY ts) AS group_id
    FROM step1
)
-- 按组聚合得到结果
SELECT 
    MIN(ts) AS min_data_timestamp,
    MAX(ts) AS max_data_timestamp,
    name
FROM step2
GROUP BY group_id, name
ORDER BY min_data_timestamp;

逻辑说明 #

• LAG(name) OVER (ORDER BY ts) 窗口函数会按时间升序取当前行的前一行name值，用于判断是否切换了流程步骤
• SUM(is_new_group) OVER (ORDER BY ts) 是滚动求和，每次遇到新的分组边界时累加值+1，所有连续相同name的行都会得到相同的group_id
• 最终按group_id和name分组聚合，不会合并不同时间段出现的相同name分组，完全匹配需求

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