怎么使用SQL實現(xiàn)車流量的計算

這篇文章主要為大家展示了“怎么使用SQL實現(xiàn)車流量的計算”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“怎么使用SQL實現(xiàn)車流量的計算”這篇文章吧。

卡口轉(zhuǎn)換率

將數(shù)據(jù)導(dǎo)入hive，通過SparkSql編寫sql,實現(xiàn)不同業(yè)務(wù)的數(shù)據(jù)計算實現(xiàn)，主要講述車輛卡口轉(zhuǎn)換率，卡口轉(zhuǎn)化率：主要計算不同卡口下車輛之間的流向，求出之間的轉(zhuǎn)換率。

1、查出每個地區(qū)下每個路段下的車流量

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

此結(jié)果做為表1，方便后面錯位連接使用

2、通過錯位連接獲取每輛車的行車記錄

通過表1的結(jié)果，與自身進行錯位鏈接，并以車牌為分區(qū)，拼接經(jīng)過卡口的過程

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

獲取到每輛車的一個行車記錄，經(jīng)過的卡口

3、獲取行車過程中的車輛數(shù)

獲取卡口1~卡口2，…等的車輛數(shù)有哪些,即拿上面的行車記錄字段進行分區(qū)在進行統(tǒng)計

(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、獲取每個卡口的總車輛數(shù)

獲取每個卡口最初的車輛數(shù)，方便后面拿行車軌跡車輛數(shù)/總車輛數(shù)，得出卡口之間的轉(zhuǎn)換率

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

5、求出卡口之間的轉(zhuǎn)換率

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
	--每個卡口總車數(shù)
(
	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實現(xiàn)車流量的計算”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！