Mysql日常实例 Demo_03

统计所有在线时间端的累计时长

有张车辆工作表，表结构如下

vehicle_id、video_status、type_time

车辆上安装了gps设备，设备一般30s上传一次数据报文， 报文中有动作类型字段 video_status, 1代表 车辆acc打开，也就是点火了，那么车辆就处于关闭状态，0代表 车辆acc关闭，也就是熄火。

现在想统计 每辆车当天 累计在线时长分别是多少

先贴代码

WITH StatusChanges AS (
    -- 标识每个状态变化的记录
    SELECT
        id,
		vehicle_id,
        case when video_status = 1 then '在线'
			 when video_status = 0 then '不在线'
		end as video_status,
        type_time,
		date_format(type_time,'%Y-%m-%d') as date,
        LAG( case when video_status = 1 then '在线'
				  when video_status = 0 then '不在线'
			 end ) 
		OVER (PARTITION BY vehicle_id ORDER BY type_time desc) AS prev_status
    FROM ty_vehicle_card_info_01 
	where type_time <CURRENT_TIME
	and   vehicle_id = 6 
	and date_format(type_time,'%Y-%m-%d') = '2024-10-23'

),
OnlinePeriods AS (
    -- 标识每个在线时间段的开始和结束
    SELECT
        id,
		vehicle_id,
        video_status,
		prev_status,
        type_time,
		date,
        SUM(CASE WHEN video_status = '在线' AND prev_status = '不在线' THEN 1 ELSE 0 END) OVER (PARTITION BY vehicle_id ORDER BY type_time desc) AS  online_group
    FROM StatusChanges
		 
),
OnlineIntervals AS (
    -- 计算每个在线时间段的持续时间
    SELECT
		vehicle_id,
		date,
        online_group,
        MIN(type_time) AS start_time,
        MAX(type_time) AS end_time
    FROM OnlinePeriods
    WHERE video_status = '在线'
    GROUP BY vehicle_id,date,online_group
)
-- 汇总所有在线时间段的总时长,
,
sum_cte as (
SELECT
    vehicle_id,
	date,
    SUM(TIMESTAMPDIFF(minute, start_time, end_time)) AS total_online_duration
FROM OnlineIntervals
GROUP BY vehicle_id, date 
ORDER BY vehicle_id, date desc
)
select *  
from OnlineIntervals;

原理

这个sql里面有两个比较重要的知识点，一个是窗口函数，一个是两个窗口的两个实例 lag over和sum over，窗口函数中的over相当于group by , over里面的partition by 字段其实就相当于group by字段的意思，

这里的 LAG 函数会获取上一行的 video_status 值，并在当前行创建一个新的列 prev_status，用于保存上一行的状态
sum就不多说了，只不过需要注意的是 窗口函数的计算是逐行机型的，每一行都会累积之前符合条件的值，和普通的聚合函数有点区别！！！

现在让我们一步步来解析sql具体发生了啥，以及最后是怎么统计出所有在线时间段有哪些，并且最终如何计算总的在线时间段时长的。

1. StatusChanges
   首先 这个用于获取所有记录的上一条记录的video_status，记做prev_status，这里为了查看方便我就稍做了处理，在lag里面做了case when 的处理。得到的结果如下
   
   由于数据有点多，这里截不全，最后我会把查到的真实数据文件发出来，方便理解。

2. OnlinePeriods
   
   这里也是截取了部分数据，我截取了当在线状态发生变化的那个时候，记录的几个字段发生的变化，可以看到这个时候online_group发生了变化，因为online_group的逻辑是

SUM(CASE WHEN video_status = '在线' AND prev_status = '不在线' THEN 1 ELSE 0 END) OVER (PARTITION BY vehicle_id ORDER BY type_time desc) AS  online_group

当前状态是在线并且前一条状态是不在线 那么才累加1，由于前面的online_group值是0，所以第一次状态由不在线切换到在线的时候，online_group从0变为1 ，而后面由于没有满足条件，所以后续值一直都是1 ，直到下一个 满足sum条件的记录出现。
4. OnlineIntervals
注意在这个sql中 添加了WHERE video_status = '在线' 的条件过滤， 这样就把所有在线的记录晒出来了，因为不添加where条件的话，当遇到不满足sum条件的记录的时候，online_group的值仍然和上面一条满足条件的online_group相同，这样后面根据online_group分组就会出现问题，添加了where条件之后 然后由于又按照online_group分组了，那么不同在线时段也就被区分开来了，
由于数据较多，中间的过程数据我这里不能贴出来，附件中我用不同颜色区分出了不同online_group分组，方便理解，我截一下最后的结果数据

红色标注的就是不同在线时段的分组号

5. sum_cte
   不同在线时段已经筛选出来了，那么剩下的就是简单的求和了，这个我就不多说了