根据上传时间进行统计,每隔一分钟上传一次数据,超过2分钟也就是120秒没有数据上传的话就不纳入计算范围,统计SQL如下:
select t.driverId,t.sumTime,t.countDate,t.orgId,t.name,t.telephone,t.vehicleNo,t.vehicleModel
from (
select
temp.driver_id driverId,
CAST(sum(temp.sum_time) as signed) as sumTime,
max(temp.time) countDate,
max(id),max(org_id) orgId,
max(name) name,
max(telephone) telephone,
max(vehicle_No) vehicleNo,
max(vehicle_model) vehicleModel
from(
select m.*, @end:=time as endtime,
case when m.driver_id=@driver_id then (@sum:=unix_timestamp(@end)-unix_timestamp(@begin)) else 0 end as sum_time,
@driver_id:=m.driver_id as temp_driver,
@begin:=time as begin_time
from (select * from driver_onlinetime_count
where time BETWEEN DATE_FORMAT(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d 00:00:00') and DATE_FORMAT(DATE_SUB(now(),INTERVAL 1 day),'%Y-%m-%d 23:59:59')
order by driver_id,time)m,
(select @driver_id:=0,@begin:=0,@end:=0,@sum:=0)n
)temp
where temp.sum_time <= 120
group by driver_id
) t