连续在线定义为相邻心跳间隔≤阈值的连通段，需用lag()计算时间差并标记段起点，再通过累计求和生成段id聚合时长，同时处理单记录兜底、时区统一及断点补全。
连续在线状态怎么定义才不会漏掉断点
设备连续在线，本质是时间序列中「相邻记录的间隔 ≤ 心跳阈值」构成的连通段。关键不是看单条记录，而是比较timestamp和下一条的timestamp——如果差值超过比如 300 秒（5 分钟），就认为中间断开了。
常见错误是直接用MIN(timestamp)和MAX(timestamp)算总跨度，这会把中间掉线几小时也当成“连续”，完全失真。
- 心跳阈值必须和业务一致：IoT 设备通常设为 300，监控系统可能设为 60
- 数据必须按设备 ID + 时间排序后才能用
LAG()或LEAD()做差值计算 - 注意时区：所有
timestamp字段应统一转为 UTC 或本地时区再比对，否则跨时区设备会出现虚假断点
用窗口函数标记每个连续段的起始点
核心思路是：对每条记录，判断它是否是某段连续在线的起点——即「上一条记录缺失，或与上一条间隔 > 阈值」。用LAG()拿到前一行时间，再用CASE WHEN打标记。
示例（PostgreSQL/MySQL 8.0+）：
SELECT
device_id,
timestamp,
CASE
WHEN LAG(timestamp) OVER (PARTITION BY device_id ORDER BY timestamp) IS NULL
OR EXTRACT(EPOCH FROM (timestamp - LAG(timestamp) OVER (PARTITION BY device_id ORDER BY timestamp))) > 300
THEN 1
ELSE 0
END AS is_segment_start
FROM device_heartbeat;
这个is_segment_start列就是后续分组的锚点：所有连续段内，只有第一行是 1，其余为 0。
用累计求和生成连续段 ID 并聚合时长
SUM(is_segment_start) OVER (PARTITION BY device_id ORDER BY timestamp)能给每段连续在线分配唯一编号。之后按device_id和这个编号GROUP BY，就能算出每段的MIN(timestamp)和MAX(timestamp)。
- 别忘了用
EXTRACT(EPOCH FROM ...)或UNIX_TIMESTAMP()把时间差转成秒，再除以 60 得分钟 - 某些数据库（如 SQLite）不支持窗口函数嵌套，需先用 CTE 把
is_segment_start算出来再累加 - 如果某段只有一条记录，
MAX - MIN为 0，但实际在线时长应至少等于心跳周期（比如 5 分钟），可加GREATEST(300, EXTRACT(...))兜底
离线设备补全逻辑会让结果更可靠
真实场景中，设备掉线后不会发心跳，所以原始表里天然缺失“断开时刻”。如果只依赖现有记录，最后一段在线时长会永远停留在“当前时间”，无法闭合。
稳妥做法是：在查询时主动加入一个“假定断开时间”——比如取NOW() - INTERVAL '5 minutes'作为当前活跃窗口上限，把所有timestamp >= NOW() - 300的记录视为仍在线，其余段正常计算。
更严格的方案是引入设备状态表，用status = 'offline'事件显式标记断开时间，避免靠推断。
连续在线计算真正难的不是 SQL 写法，而是对“什么是连续”的业务定义是否覆盖了掉电、网络闪断、时钟漂移这些边缘情况。一旦定义模糊，再漂亮的窗口函数也输出垃圾结果。
