luohe_back/berth_recommend.txt

-- 各区域停车缺口
select street_name,
  parking_gap_rate
from
(select 
t2.street_name,
ifnull(round(count(case when t2.occupancy_rate>90 then park_id end)/count(t2.park_id),2),0)*100 as 
parking_gap_rate
from
(SELECT
 t.street_name,
 t.park_id,
 ifnull(a.total_parking_number,0) as total_parking_number,
 ifnull(a.remain_parking_number,0) as remain_parking_number,
 ifnull(round(remain_parking_number*100/total_parking_number,2),0) as occupancy_rate
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 )
	where t.street_name !=''
	and remain_parking_number>=0) t2
	group by 1) t3
	where parking_gap_rate>0
	
	-- 各区域路内泊位
select area_name,
  parking_gap_rate
from
(select 
t2.area_name,
ifnull(round(count(case when t2.occupancy_rate>90 then park_id end)/count(t2.park_id),2),0)*100 as 
parking_gap_rate
from
(SELECT
 t.area_name,
 t.park_id,
 ifnull(a.total_parking_number,0) as total_parking_number,
 ifnull(a.remain_parking_number,0) as remain_parking_number,
 ifnull(round(remain_parking_number*100/total_parking_number,2),0) as occupancy_rate
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 )
	where remain_parking_number>=0) t2
	group by 1) t3