1.模拟包发送数据开始
update device_info set device_info.REMARK='pressure' LIMIT 50000;
update device_info set device_info.REMARK='pressure' WHERE device_info.REMARK='1';
2查看报的数据有多少设备是设备表中的
SELECT COUNT(1) from kajia_device_position_info kdpi INNER JOIN device_info di
ON kdpi.DEVICE_ID=di.DEVICE_ID WHERE kdpi.POS_TIME >'2017-12-27 19:00:00';
3查看是否30s发送一次
SELECT dph.vin,dph.POS_TIME,dph.CREATE_TIME,dph.RECIVED_TIME,dph.UPDATE_TIME from device_position_his1711 dph where vin='H087947' ORDER BY POS_TIME DESC;
4查看在某一秒内插入数据库的条数
SELECT COUNT(*),UPDATE_TIME
from kajia_device_position_info GROUP BY UPDATE_TIME ORDER BY UPDATE_TIME DESC ;
SELECT update_time,count(*)
from device_position_his1711 group by update_time having count(*)>100 ORDER BY UPDATE_TIME DESC;
SELECT vin ,count(*)
from device_position_his1711 group by vin ;
查看在某一秒内插入数据库的条数
SELECT update_time,count(*)
from kajia_device_position_info dpi group by update_time ;
查看网关接收时间与上报相差大于10分钟的
SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph
where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>600 and pos_time >'2017-11-22 00;00:00';
SELECT count(*) from device_position_his1711 dph
where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>600 and pos_time >'2017-11-22 19;10:00';
从数据库到网关的 大于
SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph
where TIME_TO_SEC(TIMEDIFF (dph.UPDATE_TIME,dph.RECIVED_TIME))>60 and pos_time >'2017-11-22 00;00:00';
查看某车上报的时间点
SELECT vin,POS_TIME,CREATE_TIME,RECIVED_TIME,UPDATE_TIME from device_position_his1711 dph
where SIM='18278009798' ORDER BY POS_TIME DESC;
查看有多少设备在上报过位置
SELECT count(*)from kajia_device_position_info
where pos_time >'2017-12-14 22:00:00';
查看到网关，到DSE超过1秒的数据量
SELECT count(*) from device_position_his1712 dph
where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>1 and pos_time >'2017-12-15 11:00:00';
SELECT count(*) from device_position_his1712 dph
where TIME_TO_SEC(TIMEDIFF (dph.CREATE_TIME,dph.RECIVED_TIME))>1 and pos_time >'2017-12-15 11:00:00';
SELECT count(*) from device_position_his1712 dph
where pos_time >'2017-12-15 11:00:00';
一小时查看
SELECT count(*) from device_position_his1712 dph
where TIME_TO_SEC(TIMEDIFF (dph.CREATE_TIME,dph.RECIVED_TIME))>1 and pos_time >'2017-12-21 07:50:00'
and pos_time <'2017-12-21 08:50:00';
每隔一小时小时查看数量
SELECT count(*) from device_position_his1712 dph
where TIME_TO_SEC(TIMEDIFF (dph.RECIVED_TIME,dph.POS_TIME))>1 and pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';
SELECT count(*) from device_position_his1712 dph
where TIME_TO_SEC(TIMEDIFF (dph.UPDATE_TIME,dph.RECIVED_TIME))>1 and pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';
SELECT count(*) from device_position_his1712 dph
where pos_time BETWEEN '2017-12-04 19:50:00' and '2017-12-04 20:10:00';
查出不同设备发送过的报警数多少条
SELECT COUNT(DISTINCT DEVICE_ID) from kajia_vehicle_alarm_msg where pos_time >= "2017-12-04 00:00:00";
select oa.lpno,oa.user_id,oa.order_time,oa.order_no,ui.push_id,datediff(oa.order_time,now())
days,oa.remind_count from study_base.kajia_order_apply oa
inner join
study_base.user_info ui on oa.user_id=ui.user_id where
ui.status=1 and
oa.status=2 and ifnull(ui.push_id,'')!=''
and datediff(oa.order_time,now())<=5 and datediff(oa.order_time,now())>=0
and ifnull(oa.remind_count,0)<2
