卡口转换率
将数据导入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
|
|
