在SQL中怎么用窗口函数计算每个设备连续运行的最大时长-SQL-PHP中文网

“连续运行”指相邻记录时间差≤允许间隔（如5分钟）即视为同一段，超阈值则中断并新起一段；需用lag()识别断点，再通过条件累加生成分组id，最后按段聚合求最大时长。

在sql中怎么用窗口函数计算每个设备连续运行的最大时长

什么是“连续运行”？先确认时间断点怎么定义

连续运行不是指设备从开机到关机的总时长，而是指在时间序列中，相邻两条记录的时间差 ≤ 允许间隔（比如 5 分钟），就视为同一段连续运行。一旦时间差 > 该阈值，就算中断，新起一段。

关键在于：SQL 没有“自动识别连续区间”的函数，必须靠 LAG() 或 LEAD() 找出断点，再用“分组编号”思路打标签。

常见错误是直接对 start_time 做 MAX() - MIN()，结果把所有记录当一段算，完全忽略中间停机。

用 `LAG()` + 差值标记中断点

先按设备、时间排序，用 LAG() 拿到上一条记录的结束时间（或开始时间），计算当前记录与上条之间的时间差：

VISBOOM

一款AI图像与设计工具，主要用于AI虚拟试衣间，时尚照相馆，适合需要提升相关任务效率的用户。

下载

SELECT
  device_id,
  start_time,
  end_time,
  LAG(end_time) OVER (PARTITION BY device_id ORDER BY start_time) AS prev_end_time,
  start_time - LAG(end_time) OVER (PARTITION BY device_id ORDER BY start_time) AS gap
FROM device_logs;

gap 为 NULL 表示首条记录（自然是一段起点）
gap > INTERVAL '5 minutes'（PostgreSQL）或 gap > 300（秒，MySQL/SQLite）就认为中断

用条件累加生成“连续段 ID”

不能直接 GROUP BY device_id，要给每段连续运行分配唯一段号。常用技巧是：用布尔值转整数再做窗口累加：

SELECT
  device_id,
  start_time,
  end_time,
  SUM(CASE WHEN gap > INTERVAL '5 minutes' OR gap IS NULL THEN 1 ELSE 0 END)
    OVER (PARTITION BY device_id ORDER BY start_time) AS run_segment_id
FROM (
  SELECT
    device_id,
    start_time,
    end_time,
    start_time - LAG(end_time) OVER (PARTITION BY device_id ORDER BY start_time) AS gap
  FROM device_logs
) t;

SUM(...) OVER 是关键：每次遇到中断（或首行），就 +1，其余延续前值
这样同一段连续运行的所有行共享相同 run_segment_id

最后按段聚合，取最大时长

有了 run_segment_id，就可以正常分组求每段时长，再取 MAX()：

SELECT
  device_id,
  MAX(segment_duration) AS max_consecutive_runtime
FROM (
  SELECT
    device_id,
    run_segment_id,
    MAX(end_time) - MIN(start_time) AS segment_duration
  FROM (
    -- 上面生成 run_segment_id 的子查询
  ) segmented
  GROUP BY device_id, run_segment_id
) per_segment
GROUP BY device_id;