求MySQL查询语句：计算指定时段内state=1的最短持续时长

核心思路 #

由于记录按1分钟生成，需先识别连续的state=1记录组，计算每组的持续时长后，筛选出指定时段内的最小时长。

实现SQL #

WITH consecutive_groups AS (
    SELECT
        datetime,
        state,
        -- 标记连续state=1的分组：当前行state=1且前一行state≠1时，分组ID累加
        SUM(CASE WHEN state = 1 AND LAG(state, 1, 0) OVER (ORDER BY datetime) != 1 THEN 1 ELSE 0 END) 
            OVER (ORDER BY datetime) AS group_id
    FROM your_table_name
    WHERE $__timeFilter(datetime) -- 应用Grafana全局时间过滤
),
duration_calculations AS (
    SELECT
        group_id,
        -- 计算每组state=1的持续时长（分钟数）
        TIMESTAMPDIFF(MINUTE, MIN(datetime), MAX(datetime)) AS duration_minutes
    FROM consecutive_groups
    WHERE state = 1
    GROUP BY group_id
)
SELECT MIN(duration_minutes) AS shortest_state1_duration
FROM duration_calculations;

代码说明 #

• consecutive_groups CTE：用LAG()窗口函数获取前一条记录的state值，通过SUM()生成分组ID，将连续的state=1记录归为同一组。
• duration_calculations CTE：按分组ID聚合，取每组的起止时间计算持续分钟数。
• 最终查询：从所有state=1的分组中提取最小持续时长。

适配示例场景 #

若你的表中第8-10行是连续的state=1记录，该SQL会将它们归为同一分组，计算出TIMESTAMPDIFF(MINUTE, 第8行datetime, 第10行datetime)=2，最终返回这个最小值。

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