生成Unique ID以计算各ID对应连续状态的持续时长

针对同一ID存在多段重复状态的统计问题，核心是通过识别状态变化的分界点为每个连续状态段生成唯一ID，再基于该ID计算分段总时长。以下是具体实现步骤和SQL代码：

核心思路 #

1. 按id分组、timestamp排序，用LAG()函数获取上一条记录的status
2. 对比当前记录与上一条的status，若不同则标记为新分段起点
3. 对标记值做累计求和，生成每个连续状态段的Unique ID
4. 按id和Unique ID分组，计算该段的总时长Duration per Status
5. 将分组统计结果关联回原始数据，得到完整结果表

完整SQL示例 #

假设你的原始表名为status_logs，已包含id、status、timestamp、end_timestamp、Duration字段：

WITH status_segments AS (
    SELECT
        *,
        -- 标记状态变化的分界点：当前状态与上一行不同则为1，否则为0
        CASE 
            WHEN LAG(status) OVER (PARTITION BY id ORDER BY timestamp) != status 
            THEN 1 
            ELSE 0 
        END AS segment_start,
        -- 累计求和生成Unique ID
        SUM(
            CASE 
                WHEN LAG(status) OVER (PARTITION BY id ORDER BY timestamp) != status 
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY id ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + 1 AS unique_id
    FROM status_logs
),
segment_duration AS (
    SELECT
        unique_id,
        SUM(Duration) AS duration_per_status
    FROM status_segments
    GROUP BY unique_id
)
SELECT
    s.id,
    s.status,
    s.timestamp,
    s.end_timestamp,
    s.Duration,
    s.unique_id,
    sd.duration_per_status
FROM status_segments s
JOIN segment_duration sd ON s.unique_id = sd.unique_id
ORDER BY s.id, s.timestamp;

关键函数解释 #

• LAG(status) OVER (PARTITION BY id ORDER BY timestamp)：获取同一ID下上一条记录的状态，用于判断是否进入新的连续段
• SUM(...) OVER (PARTITION BY id ORDER BY timestamp ...)：对状态变化标记做累计求和，生成唯一的分段ID
• SUM(Duration) GROUP BY unique_id：计算每个连续状态段的总时长

最终结果表 #

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