Shulou(Shulou.com)06/01 Report--

This article mainly shows you "how to use SQL to achieve traffic flow calculation", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to use SQL to achieve traffic flow calculation" this article.

Bayonet conversion rate

Import the data into hive, write sql through SparkSql, realize the data calculation of different services, mainly talk about the vehicle bayonet conversion rate, bayonet conversion rate: mainly calculate the flow direction between vehicles under different bayonets, and calculate the conversion rate between them.

1. Find out the traffic flow select car, monitor_id, action_time, ROW_NUMBER () OVER (PARTITION by carORDER by action_time) as n1FROM traffic.hive_flow_action under each road section in each area.

This result is used as Table 1, which is convenient for misplaced connections.

2. Obtain the driving record of each car through misplaced connection.

Through the results of Table 1, carry on the misplaced link with yourself, and take the license plate as the partition, the process of splicing through the bayonet

(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)

Get a driving record of each car and pass through the bayonet.

3. Get the number of vehicles in the process of driving

Get bayonet 1 ~ bayonet 2, … What are the number of vehicles, etc., that is, the above driving record fields are divided into zones for statistics.

(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. Get the total number of vehicles at each bayonet

Obtain the initial number of vehicles at each bayonet, facilitate the number of vehicles on the trail / the total number of vehicles behind, and obtain the conversion rate between bayonets.

Select monitor_id, COUNT (1) sumallfrom traffic.hive_flow_actiongroup by monitor_id

5. Calculate the conversion rate between bayonets select s2.way, s2.sumCar / s3.sumall zhlfrom (select s1.way, COUNT (1) sumCar from-driving process (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-Total number of vehicles per bayonet (select monitor_id COUNT (1) sumall from traffic.hive_flow_action group by monitor_id) S3 on split (s2.way, "- >") [0] = s3.monitor_id

The above is all the contents of the article "how to use SQL to calculate traffic flow". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.