luohe_back/各时间段出入场信息.txt

-- 按小时统计入场
select 
time_id,
ifnull(park_num,0) as park_num
from 
luo_time_data b
left join
(select
		 hour(in_time) in_hour,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_inrecord a 
ON t.park_id = a.park_id
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1) c
on b.time_id=c.in_hour

-- 按小时统计出场
select 
time_id,
ifnull(park_num,0) as park_num
from 
luo_time_data b
left join
(select
		 hour(out_time) out_hour,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_outrecord a 
ON t.park_id = a.park_id 
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1) c
on b.time_id=c.out_hour


-- 按日统计入场
select
		 day(in_time) in_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_inrecord a 
ON t.park_id = a.park_id 
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1


-- 按日统计出场
select
		 day(out_time) in_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_outrecord a 
ON t.park_id = a.park_id 
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1


-- 按周统计入场
select
		 WEEKDAY(in_time)+1 as week_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_inrecord a 
ON t.park_id = a.park_id 
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1


-- 按周统计出场
select
		 WEEKDAY(out_time)+1 as week_day,
		 case when count(parking_no) is null then 0 else count(parking_no) end as park_num
from 
	luo_park_baseinfo t
LEFT JOIN luo_parking_outrecord a 
ON t.park_id = a.park_id 
WHERE
	t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%'
group by 1
order by 1



-- 按年统计入场
SELECT YEAR
	( in_time ) AS week_day,
CASE
		
		WHEN count( parking_no ) IS NULL THEN
		0 ELSE count( parking_no ) 
	END AS park_num 
FROM
	luo_park_baseinfo t
	LEFT JOIN luo_parking_inrecord a ON t.park_id = a.park_id 
	AND t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%' 
GROUP BY 1 
ORDER BY 1

-- 按年统计出场
SELECT YEAR
	( out_time ) AS week_day,
CASE
		
		WHEN count( parking_no ) IS NULL THEN
		0 ELSE count( parking_no ) 
	END AS park_num 
FROM
	luo_park_baseinfo t
	LEFT JOIN luo_parking_outrecord a ON t.park_id = a.park_id 
	AND t.area_id = '411102' 
	AND t.keys_areas = '商圈' 
	AND t.street_name LIKE '%人民%' 
GROUP BY 1 
ORDER BY 1