SQL按值变化分组：连续相同值的最小时间及时长统计问题

这是个很常见的连续相同值分组统计场景，你用LEAD()没成功是因为它只能单条获取下一行数据，没法把连续的相同值归为同一个逻辑组。下面给你两种通用方案，适配绝大多数主流SQL数据库：

方法一：用窗口函数实现（推荐，适用于支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server等） #

核心思路是给每一组连续相同的value分配一个唯一分组ID，再基于分组ID做聚合计算：

SELECT 
    value,
    MIN(timestamp) AS min_timestamp,
    -- 计算持续时长（分钟差，和你期望的结果一致）
    TIMESTAMPDIFF(MINUTE, MIN(timestamp), MAX(timestamp)) AS duration
FROM (
    SELECT 
        value,
        timestamp,
        -- 全局行号 - 按value分组的行号 = 连续相同值的分组ID
        ROW_NUMBER() OVER (ORDER BY timestamp) 
        - ROW_NUMBER() OVER (PARTITION BY value ORDER BY timestamp) AS group_id
    FROM your_table_name -- 替换成你的实际表名
) t
GROUP BY value, group_id
ORDER BY min_timestamp;

逻辑说明： #

内层查询里的group_id会自动把连续相同的value分到同一组——因为连续相同值的全局行号和分组行号的差值是固定的。外层聚合后，就能得到每个连续组的最早时间，以及该值持续的分钟数（和你给出的期望结果完全匹配）。

如果你的duration指的是「连续记录数-1」（比如3条0对应duration=2），也可以把duration改成COUNT(*) - 1，结果数值是一样的。

方法二：用变量实现（适用于不支持窗口函数的旧版MySQL） #

如果你的数据库版本较低（比如MySQL 5.x），可以用用户变量来生成分组ID：

SELECT 
    value,
    MIN(timestamp) AS min_timestamp,
    TIMESTAMPDIFF(MINUTE, MIN(timestamp), MAX(timestamp)) AS duration
FROM (
    SELECT 
        value,
        timestamp,
        -- 当当前value和上一行相同时，分组ID不变；否则分组ID+1
        @group_id := IF(@prev_value = value, @group_id, @group_id + 1) AS group_id,
        @prev_value := value -- 更新上一行value的变量
    FROM your_table_name, -- 替换成你的实际表名
         (SELECT @prev_value := NULL, @group_id := 0) vars -- 初始化变量
    ORDER BY timestamp -- 必须按时间排序，保证连续判断正确
) t
GROUP BY value, group_id
ORDER BY min_timestamp;

验证结果 #

运行以上任意一种SQL，都会得到你想要的输出：

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