PostgreSQL中如何合并多代理重叠会话的时间间隔？

现有PostgreSQL日志表结构及数据如下：

id  dt                  session_id  agent_id state
7   2024-01-25 22:26:57 4           3148    0
7   2024-01-25 22:24:57 4           3148    1
6   2024-01-25 22:23:57 2           3148    0
5   2024-01-25 15:53:30 1           3148    0
4   2024-01-25 15:53:30 3           3148    0
3   2024-01-25 13:53:02 3           3148    1
2   2024-01-25 12:43:10 2           3148    1
1   2024-01-25 12:30:02 1           3148    1

其中state=1表示会话开始，state=0表示会话结束。

原使用lag()函数的方法仅能处理单会话场景，无法应对重叠会话的情况：

select agent_id, status, dt, lag(dt) over (partition by (agent_id) order by dt) as pre_status_dt  from 
(
    select *, 
      lag(status) over (partition by (agent_id) order by dt) as pre_status 
    from (
        select cs.dt, cs.user_id as agent_id, cs.status
        from "channel_subscribe" cs 
        where cs.channel = 14
    ) as t1
    where t1.dt >= '2024-01-01'
) as t2
where t2.status != t2.pre_status
order by dt asc

要合并重叠会话，可通过计算累计活跃会话数的方式实现，具体SQL如下：

WITH event_sequence AS (
    SELECT 
        agent_id,
        dt,
        state,
        -- 计算累计活跃会话数：开始+1，结束-1
        SUM(CASE WHEN state = 1 THEN 1 ELSE -1 END) 
            OVER (PARTITION BY agent_id ORDER BY dt) AS active_sessions,
        -- 获取上一条记录的活跃会话数，用于判断状态变化
        LAG(SUM(CASE WHEN state = 1 THEN 1 ELSE -1 END) 
            OVER (PARTITION BY agent_id ORDER BY dt)) 
            OVER (PARTITION BY agent_id ORDER BY dt) AS prev_active_sessions
    FROM channel_subscribe -- 替换为实际表名
    ORDER BY agent_id, dt
),
session_boundaries AS (
    SELECT 
        agent_id,
        dt,
        -- 标记时间段开始：从无活跃会话变为有活跃会话
        CASE WHEN prev_active_sessions = 0 AND active_sessions > 0 THEN 'start' END AS boundary_type
    FROM event_sequence
    UNION ALL
    SELECT 
        agent_id,
        dt,
        -- 标记时间段结束：从有活跃会话变为无活跃会话
        CASE WHEN prev_active_sessions > 0 AND active_sessions = 0 THEN 'end' END AS boundary_type
    FROM event_sequence
)
SELECT 
    agent_id,
    MAX(CASE WHEN boundary_type = 'start' THEN dt END) AS start_dt,
    MAX(CASE WHEN boundary_type = 'end' THEN dt END) AS end_dt
FROM (
    SELECT 
        agent_id,
        dt,
        boundary_type,
        -- 为每个连续的会话块分组
        COUNT(CASE WHEN boundary_type = 'start' THEN 1 END) 
            OVER (PARTITION BY agent_id ORDER BY dt) AS session_group
    FROM session_boundaries
    WHERE boundary_type IS NOT NULL
) AS grouped_boundaries
GROUP BY agent_id, session_group
ORDER BY agent_id, start_dt;

执行上述SQL后，将得到合并重叠会话后的结果：

agent_id  start_dt             end_dt
3148      2024-01-25 12:30:02  2024-01-25 22:23:57
3148      2024-01-25 22:24:57  2024-01-25 22:26:57

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