PostgreSQL中如何合并客户同组的连续相同状态时间区间？

我使用PostgreSQL开发，现有一张存储客户及其不同组状态信息的表，需要将事件结构的数据转换为时间段形式展示，输出字段包括client_id、moment_start、moment_end、status_group及布尔类型的status。

尝试用lead()函数实现时，同一客户同一组的相同状态被拆分为多条记录，需求是按client_id和status_group分组，仅当状态发生变化时划分时间段（实际使用timestamp(6)类型而非date）。

原表 #

期望结果表 #

已尝试的SQL语句 #

SELECT
client_id,
moment AS moment_start,
LEAD(moment) OVER (PARTITION BY client_id, status_group ORDER BY moment) AS moment_end,
status_group,
status
FROM table
ORDER BY client_id, moment_start

执行后得到的表 #

解决方案 #

核心思路是先将连续相同状态的记录归为同一分组，再基于分组计算时间段：

1. 使用LAG()函数对比当前行与上一行的status，当状态变化时生成新的分组标识；
2. 按client_id、status_group和分组标识聚合，取每组的最早moment作为moment_start；
3. 再次使用LEAD()函数，基于client_id和status_group分区，获取下一个分组的起始时间作为当前分组的结束时间。

完整SQL语句如下：

WITH grouped_status AS (
    SELECT
        client_id,
        moment,
        status_group,
        status,
        -- 生成分组标识：当当前行status与上一行不同时，分组号+1
        SUM(CASE WHEN status != LAG(status) OVER (PARTITION BY client_id, status_group ORDER BY moment) THEN 1 ELSE 0 END) 
            OVER (PARTITION BY client_id, status_group ORDER BY moment) AS group_id
    FROM your_table_name -- 替换为实际表名
),
status_periods AS (
    SELECT
        client_id,
        MIN(moment) AS moment_start,
        status_group,
        status,
        group_id
    FROM grouped_status
    GROUP BY client_id, status_group, status, group_id
    ORDER BY client_id, status_group, moment_start
)
SELECT
    client_id,
    moment_start,
    LEAD(moment_start) OVER (PARTITION BY client_id, status_group ORDER BY moment_start) AS moment_end,
    status_group,
    status
FROM status_periods
ORDER BY client_id, status_group, moment_start;

语句说明 #

• 第一个CTE grouped_status：给每个client_id+status_group内的连续相同状态记录分配同一个group_id；
• 第二个CTE status_periods：按分组聚合，得到每个状态段的起始时间；
• 最终查询：用LEAD()获取下一个状态段的起始时间作为当前段的结束时间，最后一条记录的moment_end为NULL，符合需求。

内容的提问来源于stack exchange，提问作者Maksim P.