如何在SQL中按ID和状态变更计算行间时间差

需求说明 #

基于给定的表数据，需要计算同一ID下连续相同状态段的持续时间（即从该状态起始到下一次状态变更的时间差），而非普通的行间时间差。

测试数据 #

创建表及插入数据 #

CREATE TABLE Table1(idversion TEXT, id TEXT, status TEXT, dt DATETIME);

INSERT INTO Table1 VALUES 
("1_1", "1", "OK", '2020-05-14T01:00:00'),
("1_2", "1", "OK", '2020-05-14T01:00:10'),
("1_3", "1", "NOT OK", '2020-05-14T01:00:20'),
("2_1", "2", "OK", '2020-05-14T01:00:00'),
("2_2", "2", "NOT OK", '2020-05-14T01:00:10'),
("1_4", "1", "OK", '2020-05-14T01:00:30'),
("1_5", "1", "OK", '2020-05-14T01:00:40'),
("1_6", "1", "OK", '2020-05-14T01:00:50');

原始数据查询结果 #

执行SELECT * FROM Table1 ORDER BY idversion;得到：

现有方法局限 #

之前尝试的行间时间差SQL只能计算每一行与上一行的时间差，无法直接得到状态变更时的持续时间：

SELECT *,
  dt - LAG(dt) OVER (PARTITION BY id ORDER BY idversion) AS lag
FROM Table1
ORDER BY idversion;

预期输出 #

需要得到每个状态段的起始版本、ID、状态及该状态持续到下一次变更的时间差：

解决方案SQL #

WITH grouped AS (
    SELECT 
        *,
        -- 标记当前行是否为状态变更的起始点
        CASE WHEN LAG(status) OVER (PARTITION BY id ORDER BY idversion) != status THEN 1 ELSE 0 END AS is_change,
        -- 生成连续相同状态的分组ID
        SUM(CASE WHEN LAG(status) OVER (PARTITION BY id ORDER BY idversion) != status THEN 1 ELSE 0 END) 
            OVER (PARTITION BY id ORDER BY idversion) AS status_group
    FROM Table1
),
group_summary AS (
    SELECT 
        id,
        status_group,
        status,
        MIN(idversion) AS start_idversion,
        MIN(dt) AS start_dt,
        -- 获取下一个状态分组的起始时间
        LEAD(MIN(dt)) OVER (PARTITION BY id ORDER BY status_group) AS next_start_dt
    FROM grouped
    GROUP BY id, status_group, status
)
-- 筛选存在后续状态的分组，计算时间差（单位：秒）
SELECT 
    start_idversion AS idversion,
    id,
    status,
    TIMESTAMPDIFF(SECOND, start_dt, next_start_dt) AS time_diff
FROM group_summary
WHERE next_start_dt IS NOT NULL
ORDER BY id, status_group;

思路说明 #

1. 分组连续相同状态：通过窗口函数标记状态变更点，生成连续相同状态的分组ID，将同一ID下连续相同状态的行归为一组。
2. 计算状态段时间范围：对每个分组，获取其起始版本和起始时间，同时通过LEAD函数获取下一个状态分组的起始时间。
3. 计算时间差：筛选有后续状态的分组，用TIMESTAMPDIFF计算当前状态段的持续时间，得到预期结果。

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