/*
  重新统计天汇总数据
1、清理指定日期的每小时数据；
2、重新统计该日期的每小时数据;
3、清理该日统计数据；
4、重新统计该日统计数据；

如：重新统计2021-07-13日数据,则传入 '2021-07-13'
call proc_reset_taxi_data('2021-07-13');
*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_reset_taxi_data`;
CREATE PROCEDURE `proc_reset_taxi_data`(in time_day varchar(40))
BEGIN

		DECLARE day_start_ datetime DEFAULT concat(time_day,' 00:00:00');	
		DECLARE reset_hour_start_ datetime DEFAULT concat(time_day,' 00:00:00');
		DECLARE reset_hour_end_ datetime DEFAULT DATE_ADD(reset_hour_start_,INTERVAL 1 HOUR);
		DECLARE day_end_ datetime DEFAULT DATE_ADD(reset_hour_start_,INTERVAL 1 DAY);
		

		select day_start_,day_end_,reset_hour_start_,reset_hour_end_;
		# 更新gid_100字段  select * from taxi_realtimedata order by posttime desc limit 100;
		update taxi_realtimedata set gid_100=CONCAT(LEFT(LON_BAIDU,7),'_',LEFT(LAT_BAIDU,6)) where gid_100 is null and posttime>=day_start_ and posttime<day_end_;


			# 清理当天每小时统计数据
			delete from t_d_grid100_hour where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid1000_hour where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid3000_hour where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid5000_hour where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_roadsection_hour where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_region_hour where posttime>=day_start_ and posttime<day_end_;
			
			#重写小时数据
			WHILE reset_hour_start_<day_end_ DO
				insert into t_d_grid100_hour (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX,point_count) 
					select d.gid_100,reset_hour_start_,
						round(avg(case when dustflag is null then PM25 end),0) as PM25,
						round(AVG(case when dustflag is null then PM10 end),0) as PM10,
						round(avg(case when coflag is null then CO end),2) as CO,
						round(avg(case when no2flag is null then NO2 end),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(case when noflag is null then NO end),0) as NO,
						round((round(avg(case when noflag is null then NO end),0)*2.05/1.34+round(avg(case when no2flag is null then NO2 end),0)),0) as NOX,
						count(*) as point_count
						from taxi_realtimedata d 
						where posttime>=reset_hour_start_ and posttime<reset_hour_end_ 
						group by d.gid_100;
				# 统计1000   select * from t_d_grid1000_hour limit 100;	
				insert into t_d_grid1000_hour  (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX)  
					select concat(left(grid_id,6),'_',left(right(grid_id,6),5)) as grid_id,reset_hour_start_, 
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
						from t_d_grid100_hour d 
						where posttime>=reset_hour_start_ and posttime<reset_hour_end_ 
						group by concat(left(grid_id,6),'_',left(right(grid_id,6),5));
				# 统计3000   select * from t_d_grid3000_hour limit 100;	
				insert into t_d_grid3000_hour (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 
					select concat(round((left(grid_id,7)*1000 - left(grid_id,7)*1000%25)*0.001,3),'_',round((right(grid_id,6)*1000-right(grid_id,6)*1000%25)*0.001,3))as grid_id,reset_hour_start_,
							round(avg(PM25),0) as PM25,
							round(AVG(PM10),0) as PM10,
							round(avg(CO),2) as CO,
							round(avg(NO2),0) as NO2,
							round(avg(O3),0) as O3,
							round(avg(NO),0) as NO,
							round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
							from t_d_grid100_hour d 
							where posttime>=reset_hour_start_ and posttime<reset_hour_end_ 
						group by concat(round((left(grid_id,7)*1000 - left(grid_id,7)*1000%25)*0.001,3),'_',round((right(grid_id,6)*1000-right(grid_id,6)*1000%25)*0.001,3));	
				# 统计5000   select * from t_d_grid5000_hour limit 100;	
				insert into t_d_grid5000_hour (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 
					select concat(round((left(grid_id,6)*100 - left(grid_id,6)*100%5)*0.01,2),'_',round((right(grid_id,5)*100-right(grid_id,5)*100%5)*0.01,2))as grid_id,reset_hour_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
						from t_d_grid1000_hour d 
						where posttime>=reset_hour_start_ and posttime<reset_hour_end_ 
						group by concat(round((left(grid_id,6)*100 - left(grid_id,6)*100%5)*0.01,2),'_',round((right(grid_id,5)*100-right(grid_id,5)*100%5)*0.01,2));	
				# select * from t_re_roadsection_grid100;   select * from t_d_roadsection_hour;
				insert into t_d_roadsection_hour (road_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX,point_count) 			
					select re.road_id,reset_hour_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX,
						SUM(d.point_count) as point_count
						from t_d_grid100_hour d left join t_re_roadsection_grid100 re on re.grid_id=d.grid_id 
						where posttime>=reset_hour_start_ and posttime<reset_hour_end_  and re.road_id is not null 
						group by re.road_id;	
						
				#写入select * from t_re_region_roadsection;   select * from t_d_region_hour;
				insert into t_d_region_hour (regioncode,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 	
					select re.regioncode,reset_hour_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
						from t_d_roadsection_hour d left join t_re_region_roadsection re on re.road_id=d.road_id  
						where posttime>=reset_hour_start_ and posttime<reset_hour_end_  and re.regioncode is not null 
						group by re.regioncode;
				set reset_hour_start_ = DATE_ADD(reset_hour_start_,INTERVAL 1 HOUR);
				set reset_hour_end_ = DATE_ADD(reset_hour_start_,INTERVAL 1 HOUR);
			END WHILE;


			delete from t_d_grid100_day where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid1000_day where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid3000_day where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_grid5000_day where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_roadsection_day where posttime>=day_start_ and posttime<day_end_;
			delete from t_d_region_day where posttime>=day_start_ and posttime<day_end_;

			# 处理天统计数据-----------------------------------------------------------
			# 统计 t_d_grid100_hour   select * from t_d_grid100_hour limit 10;
			insert into t_d_grid100_day (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX,point_count) 
				select grid_id,day_start_,
										round(avg(PM25),0) as PM25,
										round(AVG(PM10),0) as PM10,
										round(avg(CO),2) as CO,
										round(avg(NO2),0) as NO2,
										round(avg(O3),0) as O3,
										round(avg(NO),0) as NO,
										round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX,
										SUM(point_count) as point_count
					from t_d_grid100_hour 
					where posttime>=day_start_ and posttime<day_end_ 
					group by grid_id;
			# 统计1000   select * from t_d_grid1000_hour limit 100;	
			insert into t_d_grid1000_day  (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX)  
				select concat(left(grid_id,6),'_',left(right(grid_id,6),5)) as grid_id,day_start_, 
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
					from t_d_grid100_day d 
					where posttime>=day_start_ and posttime<day_end_  
					group by concat(left(grid_id,6),'_',left(right(grid_id,6),5));
			# 统计3000   select * from t_d_grid3000_hour limit 100;	
			insert into t_d_grid3000_day (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 
				select concat(round((left(grid_id,7)*1000 - left(grid_id,7)*1000%25)*0.001,3),'_',round((right(grid_id,6)*1000-right(grid_id,6)*1000%25)*0.001,3))as grid_id,day_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
					from t_d_grid100_day d 
					where posttime>=day_start_ and posttime<day_end_ 
					group by concat(round((left(grid_id,7)*1000 - left(grid_id,7)*1000%25)*0.001,3),'_',round((right(grid_id,6)*1000-right(grid_id,6)*1000%25)*0.001,3));		
			# 统计5000   select * from t_d_grid5000_hour limit 100;	
			insert into t_d_grid5000_day (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 
				select concat(round((left(grid_id,6)*100 - left(grid_id,6)*100%5)*0.01,2),'_',round((right(grid_id,5)*100-right(grid_id,5)*100%5)*0.01,2))as grid_id,day_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
					from t_d_grid1000_day d 
					where posttime>=day_start_ and posttime<day_end_  
					group by concat(round((left(grid_id,6)*100 - left(grid_id,6)*100%5)*0.01,2),'_',round((right(grid_id,5)*100-right(grid_id,5)*100%5)*0.01,2));	
			# select * from t_re_roadsection_grid100;   select * from t_d_roadsection_hour;
			insert into t_d_roadsection_day (road_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX,point_count) 			
				select re.road_id,day_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX,
						SUM(d.point_count) as point_count
					from t_d_grid100_day d left join t_re_roadsection_grid100 re on re.grid_id=d.grid_id 
					where posttime>=day_start_ and posttime<day_end_  and re.road_id is not null 
					group by re.road_id;	
		
			#写入select * from t_re_region_roadsection;   select * from t_d_region_hour;
			insert into t_d_region_day (regioncode,posttime,PM25,PM10,CO,NO2,O3,NO,NOX) 	
				select re.regioncode,day_start_,
						round(avg(PM25),0) as PM25,
						round(AVG(PM10),0) as PM10,
						round(avg(CO),2) as CO,
						round(avg(NO2),0) as NO2,
						round(avg(O3),0) as O3,
						round(avg(NO),0) as NO,
						round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),0) as NOX 
					from t_d_roadsection_day d left join t_re_region_roadsection re on re.road_id=d.road_id  
					where posttime>=day_start_ and posttime<day_end_ and re.regioncode is not null 
					group by re.regioncode;
			
END
//
DELIMITER ;