怎么使用SQL实现车流量的计算

这篇文章主要为大家展示了"怎么使用SQL实现车流量的计算"，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下"怎么使用SQL实现车流量的计算"这篇文章吧。

卡口转换率

将数据导入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实现车流量的计算"这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！