如何从仅存启停事件时间的表中统计含跨天场景的每日运行时长

基础背景 #

我们需要基于仅记录启停事件的明细表，统计自然日维度的设备总运行时长，表结构与示例数据如下：

事件定义规则：

• event = 0：设备停止事件
• event = 1：设备启动事件

统计核心难点 #

常规计算是匹配成对的启动、停止事件，取两者的时间差累加得到运行时长，但实际计算需要额外覆盖三类场景：

1. 跨天状态承接：如果前一日的最后一条事件是启动状态、没有对应的停止事件，那么当日零点到当日第一条相关事件的连续运行区间，必须计入当日总运行时长（题目示例中该部分跨天承接的运行段时长为9小时）
2. 跨天区间拆分：不能把一个跨越自然日零点的长运行区间，直接全部归到某一个自然日的统计里，要按实际覆盖的日期拆分时长
3. 异常数据兼容：要避免连续启动、连续停止这类无效重复事件导致的计算错位

具体实现逻辑 #

• 第一步：把全量事件按时间正序排序，通过窗口函数拿到每一条事件对应的下一条事件时间戳，同时回溯上一条事件的状态，判断当前[当前事件时间, 下一条事件时间]这个区间内，设备是处于运行还是停止状态。
  判断逻辑以上一条事件的event值为准：如果上一条事件是1（启动），这个区间内设备全程处于运行状态；如果上一条事件是0（停止），这个区间内设备处于停止状态，不计入运行时长。
• 第二步：把所有标记为“运行状态”的时间区间，按覆盖的自然日做切割拆分。每个独立的运行区间记为[运行开始时间, 运行结束时间]，对它覆盖的每个自然日，计算当日内的有效运行段：取max(运行开始时间, 当日零点) 到 min(运行结束时间, 当日23:59:59.999)的时间差，作为该日的运行时长贡献值。
• 第三步：特殊边界兜底
  1. 前一日遗留运行状态：如果当日第一条事件之前的设备状态是运行，对应运行区间的起点直接设为当日零点，正好匹配跨天承接的统计要求
  2. 当日未收尾的运行状态：如果当日最后一条事件是启动状态、没有对应停止事件，这个运行区间的终点直接设为当日23:59:59.999，避免把未发生的时间计入时长
  3. 重复事件过滤：如果相邻两条事件的event值完全相同（比如连续两个启动、连续两个停止），直接过滤掉无效的重复事件，避免区间计算错位

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