luohe_back/park_bahavior.txt

-- 停车时长分析	
SELECT
 case when a.out_time-t.in_time <= 7200 then 0
      when a.out_time-t.in_time > 7200 and a.out_time-t.in_time <= 14400 then 2
      when a.out_time-t.in_time > 14400 and a.out_time-t.in_time <= 28800 then 4
			when a.out_time-t.in_time > 28800 and a.out_time-t.in_time <= 43200 then 8
			when a.out_time-t.in_time > 43200 then 12
end as hour_flag,
count(t.order_no) as order_num
FROM
	luo_parking_inrecord t
	INNER JOIN luo_parking_outrecord a 
	ON t.order_no = a.order_no
	group by 1
	

-- 停车时段分析
select 
a.hour_group,
a.park_in_num, -- 入场数量
b.park_out_num -- 出场数量
from 
(select
case when hour(in_time)>=0 and hour(in_time)<3 then 3
     when hour(in_time)>=3 and hour(in_time)<6 then 6
		 when hour(in_time)>=6 and hour(in_time)<9 then 9
		 when hour(in_time)>=9 and hour(in_time)<12 then 12
		 when hour(in_time)>=12 and hour(in_time)<15 then 15
		 when hour(in_time)>=15 and hour(in_time)<18 then 18
		 when hour(in_time)>=18 and hour(in_time)<21 then 21
		 when hour(in_time)>=21 and hour(in_time)<24 then 24 end hour_group,
		 count(parking_no) as park_in_num
from 
luo_parking_inrecord t
group by 1
order by 1) a
left join
(select
case when hour(out_time)>=0 and hour(out_time)<3 then 3
     when hour(out_time)>=3 and hour(out_time)<6 then 6
		 when hour(out_time)>=6 and hour(out_time)<9 then 9
		 when hour(out_time)>=9 and hour(out_time)<12 then 12
		 when hour(out_time)>=12 and hour(out_time)<15 then 15
		 when hour(out_time)>=15 and hour(out_time)<18 then 18
		 when hour(out_time)>=18 and hour(out_time)<21 then 21
		 when hour(out_time)>=21 and hour(out_time)<24 then 24 end hour_group,
		 count(parking_no) as park_out_num
from 
luo_parking_outrecord t
group by 1) b 
on a.hour_group=b.hour_group	



-- 临时停车分析
SELECT
CASE
	WHEN a.out_time - t.in_time <= 86400 THEN '短期' 
  else '长期' END AS temporary_parking_flag,
		count( t.order_no ) AS order_num 
	FROM luo_parking_inrecord t
	INNER JOIN luo_parking_outrecord a 
	ON t.order_no = a.order_no
group by 1


-- 夜间停车分析
SELECT
CASE
	WHEN hour(in_time)>=6 and hour(in_time)<18 THEN '白天' 
  else '夜间' END AS temporary_parking_flag,
		count( t.order_no ) AS order_num 
	FROM luo_parking_inrecord t
	INNER JOIN luo_parking_outrecord a 
	ON t.order_no = a.order_no
group by 1