luohe_back/parking_area.txt

-- 区域饱和度
select 
area_name,
ifnull(round(occupancy_parking_number*100/total_parking_number,2),0)
from
(SELECT
 area_id,
 area_name,
 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) t1
	
-- 饱和度时段分析
select hour_flag,
			 ifnull(round(sum(occupancy_parking_number)*100/sum(total_parking_number),2),0)
from (SELECT
	park_id,
	HOUR(CREATE_TIME) as HOUR_flag,
	max(total_parking_number) total_parking_number,
	min(remain_parking_number) remain_parking_number,
	max(total_parking_number)-min(remain_parking_number) as occupancy_parking_number
FROM
	luo_parking_berths 
	where remain_parking_number>=0
	group by 1,2) a
	group by 1