最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
使用SQL实现车流量的计算代码示例
时间:2022-06-29 07:42:30 编辑:袖梨 来源:一聚教程网
本篇文章小编给大家分享一下使用SQL实现车流量的计算代码示例,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。
卡口转换率
将数据导入hive,通过SparkSql编写sql,实现不同业务的数据计算实现,主要讲述车辆卡口转换率,卡口转化率:主要计算不同卡口下车辆之间的流向,求出之间的转换率。
1、查出每个地区下每个路段下的车流量
select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action
此结果做为表1,方便后面错位连接使用
2、通过错位连接获取每辆车的行车记录
通过表1的结果,与自身进行错位链接,并以车牌为分区,拼接经过卡口的过程
(select t1.car, t1.monitor_id, concat(t1.monitor_id, "->", t2.monitor_id) as way from ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t1 left join ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t2 on t1.car = t2.car and t1.n1 = t2.n1-1 where t2.action_time is not null)
获取到每辆车的一个行车记录,经过的卡口
3、获取行车过程中的车辆数
获取卡口1~卡口2,…等的车辆数有哪些,即拿上面的行车记录字段进行分区在进行统计
(select s1.way, COUNT(1) sumCar from --行车过程 (select t1.car, t1.monitor_id, concat(t1.monitor_id, "->", t2.monitor_id) as way from ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t1 left join ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t2 on t1.car = t2.car and t1.n1 = t2.n1-1 where t2.action_time is not null)s1 group by way)
4、获取每个卡口的总车辆数
获取每个卡口最初的车辆数,方便后面拿行车轨迹车辆数/总车辆数,得出卡口之间的转换率
select monitor_id , COUNT(1) sumall from traffic.hive_flow_action group by monitor_id
5、求出卡口之间的转换率
select s2.way, s2.sumCar / s3.sumall zhl from ( select s1.way, COUNT(1) sumCar from --行车过程 ( select t1.car, t1.monitor_id, concat(t1.monitor_id, "->", t2.monitor_id) as way from ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t1 left join ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t2 on t1.car = t2.car and t1.n1 = t2.n1-1 where t2.action_time is not null)s1 group by way)s2 left join --每个卡口总车数 ( select monitor_id , COUNT(1) sumall from traffic.hive_flow_action group by monitor_id) s3 on split(s2.way, "->")[0]= s3.monitor_id