luohe_back/gap_analysis.txt

-- 停车缺口分析
select 
t2.area_name,
ifnull(round(count(case when t2.occupancy_rate>70 then park_id end)/count(t2.park_id),2),0)*100 as 
parking_gap_rate
from
(select 
area_name,
park_id,
ifnull(round(occupancy_parking_number*100/total_parking_number,2),0) as occupancy_rate
from
(SELECT
 area_id,
 area_name,
 t.park_id,
 CASE
	WHEN
		sum( remain_parking_number ) IS NULL THEN
			0 ELSE sum( remain_parking_number ) 
		END remain_parking_number,-- 空闲位
CASE
		WHEN sum( total_parking_number ) - sum( remain_parking_number ) IS NULL THEN
		0 ELSE sum( total_parking_number ) - sum( remain_parking_number ) 
	END occupancy_parking_number, -- 占用位
CASE
		WHEN	sum( total_parking_number ) IS NULL THEN
		0 ELSE sum( total_parking_number ) END as total_parking_number
FROM
	luo_park_baseinfo t
	LEFT JOIN luo_parking_berths a ON t.park_id = a.park_id 
	AND a.CREATE_TIME >= ( SELECT max( CREATE_TIME ) max_time FROM luo_parking_berths )
	group by 1,2,3) t1) t2
	group by 1
	
	
-- 停车缺口变化趋势
select 
t2.HOUR_flag,
ifnull(round(count(case when t2.occupancy_rate>99 then park_id end)/count(t2.park_id),2),0)*100 as 
parking_gap_rate
from
(select 
ifnull(HOUR_flag,0) HOUR_flag,
park_id,
ifnull(round(order_num*100/total_parking_number,2),0) as occupancy_rate
from
(SELECT
 t.park_id,
 a.total_parking_number,
 c.order_num,
 c.HOUR_flag
FROM
	luo_park_baseinfo t
	LEFT JOIN luo_parking_berths a ON t.park_id = a.park_id 
	AND a.CREATE_TIME >= ( SELECT max( CREATE_TIME ) max_time FROM luo_parking_berths )
	left join (select park_id,HOUR(in_time) as HOUR_flag, count(b.order_no) as order_num
	         from luo_parking_inrecord b 
					 where HOUR(in_time) is not null
					 group by 1,2) c
	on t.park_id = c.park_id) t1) t2
	group by 1