# 使用SQL实现车流量的计算的示例代码

##### 目录
• 卡口转换率
• 1、查出每个地区下每编程客栈个路段下的车流量
• 2、通过错位连接获取每辆车的行车记录
• 3、获取行车过程中的车辆数
• 4、获取每个卡口的总车辆数
• 5、求出卡口之间的转换率

## 1、查出每个地区下每个路段下的车流量

```select
car,
monitor_id,
action_time,
ROW_NUMBER () OVER (PARTITION by car
ORDER by
action_time) as n1
FROM
traffic.hive_flow_action
```

## 2、通过错位连接获取每辆车的行车记录

```(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、获取行车过程中的车辆数

```(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 (
www.cppcns.com		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、获取每个卡口的总车辆数

```select
monitor_id ,
COUNT(1) sumall
from
traffic.hive_flow_action
group by
monitor_id
```

## 5、求出卡口之间的转换率

```select
s2.way,
s2.sumCar / s3.sumall zhl
from
(
select
s1.way,
COUNT(1) sumCar
from
--行车过程
(
seKAXhXlect
t1.car,
t1.monitor_id,
concat(t1.monitor_id,
"->",
t2.monitor_id) as way
from
(
shttp://www.cppcns.comelect
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
http://www.cppcns.com		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

```

到此这篇关于使用SQL实现车流量的计算的示例代码的文章就介绍到这了,更多相关SQL 车流量内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！