目录卡口转换率1、查出每个地区下每个路段下的车流量2、通过错位连接获取每辆车的行车记录3、获取行车过程中的车辆数4、获取每个卡口的总车辆数5、求出卡口之间的转换率卡口转换率将数据导入hive，通过SparkSql编写sql,实现不同业务的数据计算实现，主要讲述车辆卡口转换率，卡口转化率：主要计算不同卡口下车辆之间的流向，求出之间的转换率。1、查出每个地区下每个路段下的车流量selectcar,monitor_id,action_time,ROW_NUMBER()OVER(PARTITIONbycarORDERbyaction_time)asn1FROMtraffic.hive_flow_action此结果做为表1，方便后面错位连接使用2、通过错位连接获取每辆车的行车记录通过表1的结果，与自身进行错位链接，并以车牌为分区，拼接经过卡口的过程(selectt1.car,t1.monitor_id,concat(t1.monitor_id,"->",t2.monitor_id)aswayfrom(selectcar,monitor_id,action_time,ROW_NUMBER()
卡口转换率
将数据导入hive，通过SparkSql编写sql,实现不同业务的数据计算实现，主要讲述车辆卡口转换率，卡口转化率：主要计算不同卡口下车辆之间的流向，求出之间的转换率。
1、查出每个地区下每个路段下的车流量
select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by carORDER by action_time) as n1FROM traffic.hive_flow_action
此结果做为表1，方便后面错位连接使用
2、通过错位连接获取每辆车的行车记录
通过表1的结果，与自身进行错位链接，并以车牌为分区，拼接经过卡口的过程
(select t1.car, t1.monitor_id, concat(t1.monitor_id, "->", t2.monitor_id) as wayfrom ( select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by car ORDER by action_time) as n1 FROM traffic.hive_flow_action) t1left 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-1where t2.action_time is not null)
获取到每辆车的一个行车记录，经过的卡口
3、获取行车过程中的车辆数
获取卡口1~卡口2，…等的车辆数有哪些,即拿上面的行车记录字段进行分区在进行统计
(select s1.way, COUNT(1) sumCarfrom --行车过程(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)s1group by way)
4、获取每个卡口的总车辆数
获取每个卡口最初的车辆数，方便后面拿行车轨迹车辆数/总车辆数，得出卡口之间的转换率
select monitor_id , COUNT(1) sumallfrom traffic.hive_flow_actiongroup by monitor_id
5、求出卡口之间的转换率
select s2.way, s2.sumCar / s3.sumall zhlfrom ( 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)s2left 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
到此这篇关于使用SQL实现车流量的计算的示例代码的文章就介绍到这了,更多相关SQL 车流量内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
声明：本文内容来源自网络，文字、图片等素材版权属于原作者，平台转载素材出于传递更多信息，文章内容仅供参考与学习，切勿作为商业目的使用。如果侵害了您的合法权益，请您及时与我们联系，我们会在第一时间进行处理！我们尊重版权，也致力于保护版权，站搜网感谢您的分享！
