luohe_back/berth_peration_nalysis.txt

-- 泊位运营分析
-- 停车数
SELECT
 park_name,
 CASE
	WHEN
		sum( remain_parking_number ) IS NULL THEN
			0 ELSE sum( remain_parking_number ) 
		END remain_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
	
	
-- 平均周转次数
select c.car_park_name,
		 round(avg(parking_turnover_rate),2) as parking_turnover_rate
from
luo_parking_information c inner join
(SELECT
	t.park_id,
	b.total_parking_number,
	count( t.parking_no ) AS parking_num,
	count( t.parking_no )/b.total_parking_number as parking_turnover_rate
FROM
	luo_parking_inrecord t
	inner join luo_parking_berths b on t.park_id = b.park_id and b.CREATE_TIME = (
	SELECT
		max( CREATE_TIME ) max_time 
	FROM
		luo_parking_berths 
WHERE
	create_time >= curdate())
	where in_time>=CURDATE()
GROUP BY
	t.park_id,
	b.total_parking_number) t1
	on c.id=t1.park_id
GROUP BY 1