一、停歇分析

1.每日停息轨迹分析

val s9_every_day_stop_time=sql("select to_date(gps_time) as gps_t, count(*)*2/3600 as num_trajectory from trajectory where vme_id='S901100009' and speed<1 group by to_date(gps_time) order by to_date(gps_time) ")

//保存数据到本地
 s9_every_day_stop_time.repartition(1).write.format("csv").save("file:/home/hadoop/mydata/s9_every_day_stop_time")

2.运营时间运营里程分析

val s9_operation=  sql(" select a.gps_t,  day(a.gps_t) as day,a.num_trajectory, a.num_car, a.avg_speed,  a.num_trajectory*2/a.num_car/3600 as avg_day_operation_time,  a.avg_speed* a.num_trajectory*2/a.num_car/1000 as avg_day_operation_distance from (select distinct  to_date(gps_time) as gps_t, count(gps_time) as num_trajectory ,count(distinct vme_id) as num_car, avg(speed) avg_speed from trajectory where vme_id='S901100009'  group by to_date(gps_time) order by  gps_t) as a")
//保存数据到本地

 s9_operation.repartition(1).write.format("csv").save("file:/home/hadoop/mydata/s9_operation")

3.作业时间作业里程分析

val s9_work = sql("select a.gps_t,  day(a.gps_t) as day, a.num_trajectory, a.num_car, a.avg_speed,  a.num_trajectory*2/a.num_car/3600 as avg_day_work_time,  a.avg_speed* a.num_trajectory*2/a.num_car/1000 as avg_day_work_distance from (select distinct  to_date(gps_time) as gps_t, count(gps_time) as num_trajectory ,count(distinct vme_id) as num_car, avg(speed) avg_speed from trajectory where vme_id='S901100009'  and speed>1 and work_deep>300 group by to_date(gps_time) order by  gps_t) as a")

//保存数据到本地
 s9_work.repartition(1).write.format("csv").save("file:/home/hadoop/mydata/s9_work)