提取设备连续故障时间区间的SQL实现技术问询

直接上能跑的无中间表方案，靠窗口函数就能搞定：

SELECT
    Device_ID,
    MIN(Timestamp) AS Fail_Begin,
    MAX(Timestamp) AS Fail_End
FROM (
    SELECT
        Device_ID,
        Timestamp,
        -- 生成分组标识：同一设备内，全局行号减去故障状态下的行号，连续故障会得到相同值
        ROW_NUMBER() OVER (PARTITION BY Device_ID ORDER BY Timestamp)
        - ROW_NUMBER() OVER (PARTITION BY Device_ID, Status ORDER BY Timestamp) AS Group_ID
    FROM Device_Status
    WHERE Status = 'Failed' -- 只筛选故障记录
) AS Temp
GROUP BY Device_ID, Group_ID
ORDER BY Device_ID, Fail_Begin;

关键逻辑拆解 #

• 内层子查询的两个ROW_NUMBER()：
  第一个是按设备ID分组、时间排序的全局行号，不管状态，每个设备的记录按时间从1开始依次编号。
  第二个是按设备ID+状态分组、时间排序的行号，只给故障状态的记录单独编号。
• 两者的差值Group_ID就是自动生成的连续故障分组标识：连续的故障记录中，两个行号的增长同步，差值保持不变；一旦状态切换（比如故障转正常再转故障），差值会跳变，自动把不同的连续故障段分开。
• 外层按设备ID和Group_ID分组，取每组的最小/最大时间，就是这段连续故障的起止时间——单条故障记录的话，min和max是同一个值，正好符合需求。

兼容性说明 #

这个写法是标准SQL，MySQL 8.0+、PostgreSQL、SQL Server、Oracle等主流数据库都支持。如果是老版本MySQL（5.x及以下）不支持窗口函数，可通过变量实现，但现在主流环境基本都兼容窗口函数方案。

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