卡口转换率
将数据导入hive，通过SparkSql编写sql,实现不同业务的数据计算实现，主要讲述车辆卡口转换率，卡口转化率：主要计算不同卡口下车辆之间的流向，求出之间的转换率。
1、查出每个地区下每个路段下的车流量
|
1 2 3 4 5 6 7 8 9 |
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的结果，与自身进行错位链接，并以车牌为分区，拼接经过卡口的过程
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
(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，…等的车辆数有哪些,即拿上面的行车记录字段进行分区在进行统计
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
(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、获取每个卡口的总车辆数
获取每个卡口最初的车辆数，方便后面拿行车轨迹车辆数/总车辆数，得出卡口之间的转换率
|
1 2 3 4 5 6 7 |
select monitor_id , COUNT(1) sumall from traffic.hive_flow_action group by monitor_id |
5、求出卡口之间的转换率
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
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 |
