sql 计算驾驶超时时间问题-优快云博客

本文介绍了一种通过SQL查询检测车辆是否发生超时驾驶的方法。超时驾驶被定义为连续驾驶4小时但累计休息时间不足20分钟的情况。文章详细解释了如何创建数据表并插入示例数据，以及如何使用SQL查询确定是否存在超时驾驶行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原题见：http://bbs.51cto.com/viewthread.php?tid=810424&page=1
超时的定义：连接行驶4个小时休息时间少于20分钟算超时驾驶（这个20分钟是在4个小时中停车时间的累加，还有个条件是停车时间小于5分钟也算他在行驶不算休息）
现在要得到的结果是超时的次数，以及车牌，开始时间，结束时间，连续行驶时间
create table TB_VDRDATA
(
  vehicleid varchar(16),--------车牌
  carspeed int,---------车速
  date datetime,----------时间
  bcms float------------保持秒数
)
由于提供的测试数据有问题，重新提供如下：

insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 50 , '2011-01-13 11:19:59.000' , 3 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 63 , '2011-01-13 11:20:02.000' , 5 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 60 , '2011-01-13 11:20:07.000' , 7 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 61 , '2011-01-13 11:20:14.000' , 5 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 62 , '2011-01-13 11:20:19.000' , 9 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 63 , '2011-01-13 11:20:28.000' , 5 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:20:33.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:22:13.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:23:53.000' , 80 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 20 , '2011-01-13 11:25:13.000' , 7 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 30 , '2011-01-13 11:25:20.000' , 5 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 40 , '2011-01-13 11:25:25.000' , 5 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:25:30.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:27:10.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:28:50.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 0 , '2011-01-13 11:30:30.000' , 100 )
insert TB_VDRDATA(vehicleid,carspeed,date,bcms) values( '陕ADF191' , 9 , '2011-01-13 11:32:10.000' , 5 )

问题处理思路
1.将所有连续休息的开始时间求出：时间都是连续记录的，任何一个开始时间前面要不没有记录，要不就是速度>0的记录
2.将所有连续休息的结束时间求出：这个和上面类似，任何一个结束时间后面要不没有记录，要不就是速度>0的记录
3.合并1，2生成的表，形成一个完成的开始时间到结束时间表，这里可以用日期差来得到休息时间。
4,将时间差小于5分钟的记录找出来，用来标示原来表中的这部分记录（最好加个字段）
5.类似123步骤，可以得到驾驶时间表（开始时间，结束时间），注意将第四步产生的记录也视为驾驶时间，这样得到的记录时间差大于4个小时的就是超时驾驶了。

具体的SQL：

--本计算主要是表示一个计算过程，没有对查询做过多的优化。
--连续休息时间计算
with b1 as
(
select vehicleid,carspeed,date as bdate,bcms
from TB_VDRDATA f
where carspeed=0 and date not in (select DATEADD(ss,bcms,date) from TB_VDRDATA where carspeed=0)
),e1 as
(
select vehicleid,carspeed,DATEADD(ss,bcms,date) as edate,bcms
from TB_VDRDATA f
where carspeed=0 and DATEADD(ss,bcms,date) not in (select date from TB_VDRDATA where carspeed=0)
),c1 as--连续休息时间单位秒剔除小于5分钟300秒
(
select vehicleid,carspeed,bdate,bcms,
(select MIN(edate) from e1 where edate>b1.bdate) edate,DATEDIFF(ss,bdate,isnull((select MIN(edate) from e1 where edate>b1.bdate),bdate)) as ss
from b1
where DATEDIFF(ss,bdate,isnull((select MIN(edate) from e1 where edate>b1.bdate),bdate))<300
),c2 as --连续休息小于20分钟（1200秒）（已经剔除300秒内）
(
select * from c1 where ss<1200
),
--连续驾驶时间计算
--计算过程中对于休息时间在20分钟内的也算作一次连续驾驶
b as
(
select f.vehicleid,f.carspeed,f.date as bdate,f.bcms
from TB_VDRDATA f
where f.carspeed>0 and f.date not in
(select DATEADD(ss,tb.bcms,date) from TB_VDRDATA tb,c2 c1
where tb.vehicleid=c1.vehicleid and (tb.carspeed>0
or (tb.date >=c1.bdate and tb.date<=c1.edate)))
),e as
(
select f.vehicleid,f.carspeed,DATEADD(ss,f.bcms,f.date) as edate,f.bcms
from TB_VDRDATA f
where f.carspeed>0 and DATEADD(ss,f.bcms,f.date) not in
(select tb.date from TB_VDRDATA tb,c2 c1
where tb.vehicleid=c1.vehicleid and (tb.carspeed>0
or (tb.date >=c1.bdate and tb.date<=c1.edate)))
),c as--连续驾驶时间
(
select vehicleid,carspeed,bdate,bcms,
isnull((select MIN(edate) from e where edate>b.bdate),bdate) as edate,DATEDIFF(ss,bdate,isnull((select MIN(edate) from e where edate>b.bdate),bdate)) as ss
from b
)
--连续驾驶时间大于4小时（60*60*4=14400秒）,并且休息时间少于20分钟的记录
--即连续行驶时间大于4小时中包含休息时间多余5分钟而小于20分钟的记录
--考虑到测试数据没有这么多的时间跨度，这个阀值我们假定为300秒
select c.vehicleid,c.bdate,c.edate,c.ss
from c,c2
where c.vehicleid=c2.vehicleid and c.ss>300 and c.bdate<c2.bdate and c.edate>c2.edate

转载于:https://blog.51cto.com/oswica/486996