求助：特定状态时间差计算及非连续重复类别分组实现问题

第一步：创建连续类别的虚拟分组（Result_Group） #

不用递归CTE，用LAG()窗口函数结合累加就能搞定核心的分组逻辑：

1. 用LAG(category) OVER (ORDER BY event_time)获取上一行的类别
2. 判断当前行类别与上一行是否不同，不同则记为1，否则记为0
3. 对这个标记值做累加（SUM() OVER (ORDER BY event_time)），得到的就是连续相同类别的分组ID

示例SQL（兼容PostgreSQL、MySQL 8.0+、SQL Server）：

WITH grouped_data AS (
    SELECT
        id,
        category,
        status,
        event_time,
        -- 生成分组ID：每次类别变化时累加1
        SUM(CASE WHEN category = LAG(category) OVER (ORDER BY event_time) THEN 0 ELSE 1 END) 
            OVER (ORDER BY event_time) AS Result_Group
    FROM your_table
)
SELECT * FROM grouped_data;

第二步：计算特定状态间的时间差 #

场景1：同一分组内特定状态的时间间隔（如start到end） #

在分组结果基础上，提取对应状态的时间后计算差值：

WITH grouped_data AS (
    SELECT
        id,
        category,
        status,
        event_time,
        SUM(CASE WHEN category = LAG(category) OVER (ORDER BY event_time) THEN 0 ELSE 1 END) 
            OVER (ORDER BY event_time) AS Result_Group
    FROM your_table
),
status_times AS (
    SELECT
        Result_Group,
        category,
        MAX(CASE WHEN status = 'start' THEN event_time END) AS start_time,
        MAX(CASE WHEN status = 'end' THEN event_time END) AS end_time
    FROM grouped_data
    GROUP BY Result_Group, category
)
SELECT
    Result_Group,
    category,
    -- 时间差函数根据数据库调整：PostgreSQL用AGE，MySQL用TIMESTAMPDIFF
    AGE(end_time, start_time) AS duration
FROM status_times
WHERE start_time IS NOT NULL AND end_time IS NOT NULL;

场景2：同一分组内相邻状态的时间差 #

直接在分组结果中用LAG()提取上一个状态的时间：

WITH grouped_data AS (
    SELECT
        id,
        category,
        status,
        event_time,
        SUM(CASE WHEN category = LAG(category) OVER (ORDER BY event_time) THEN 0 ELSE 1 END) 
            OVER (ORDER BY event_time) AS Result_Group
    FROM your_table
)
SELECT
    *,
    event_time - LAG(event_time) OVER (PARTITION BY Result_Group ORDER BY event_time) AS time_since_last_status
FROM grouped_data;

关键说明 #

• 核心逻辑是按时间顺序对比相邻行的类别，确保非连续的同一类别被分为不同分组，完全匹配需求
• 窗口函数比递归CTE性能更优，数据量较大时优势明显
• 时间差计算可根据实际需求调整：比如取分组首尾时间、多状态间隔等，只需修改聚合或窗口函数逻辑

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