--停车饱和度
select week_day,
			 sum(occupancy_parking_number) as occupancy_parking_number,-- 占用
			 sum(remain_parking_number) as remain_parking_number-- 空闲
from (SELECT
	park_id,
	WEEKDAY(CREATE_TIME)+1 as week_day,
	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
	
-- 停车场周转率分析	
select week_day,
		 sum(parking_num)*100/sum(total_parking_number) as parking_turnover_rate
from
(SELECT
  WEEKDAY(t.in_time)+1 as week_day,
	t.park_id,
	b.total_parking_number,
	count( t.parking_no ) AS parking_num,
  count( t.parking_no )/b.total_parking_number
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())
GROUP BY 1,2,3) t1
GROUP BY 1

-- 停车场营收分析

select
		 case when WEEKDAY(c.pay_time)+1 is null then 1 else WEEKDAY(c.pay_time)+1 end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	left join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
group by 1

-- 特殊车辆进场占比  改为   车辆类型进场占比
select 
car_type,
count(order_no) as parking_num
from 
luo_parking_inrecord
group by 1

-- 车辆进出场趋势
select 
b.week_day,
round(park_in_num*100/(park_in_num+park_out_num),2) as inflow_rate,
round(park_out_num*100/(park_in_num+park_out_num),2) as outflow_rate
from
(select
		 WEEKDAY(in_time)+1 as week_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_in_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_inrecord a 
ON t.park_id = a.park_id 
where WEEKDAY(in_time) is not null
group by 1) b
left join 
(select
		 WEEKDAY(out_time)+1 as week_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_out_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_outrecord a 
ON t.park_id = a.park_id 
where WEEKDAY(out_time) is not null
group by 1) c
on b.week_day=c.week_day

-- 停车场营收对比分析
-- 双汇广场停车场 和 大润发地下停车场
select 
park_name,
pay_time,
amount
from
(select
     t.park_id,
		 t.park_name,
		 case when WEEKDAY(c.pay_time)+1 is null then 1 else WEEKDAY(c.pay_time)+1 end pay_time,
		 case when sum(amount) is null then 0 else round(sum(amount)/100,2) end as amount
FROM
	luo_park_baseinfo t
	inner join 
(select a.park_id,a.pay_time,b.amount
from luo_parking_chargerecord a
inner join luo_parking_chargerecord_subject b
on a.order_no=b.order_no) c
ON t.park_id = c.park_id 
group by 1,2,3) t1
where park_id in ('100106091','100105564')