添加数据统计存储过程

wangrong

2021-11-24 516993b06f31e352e0363f0f441550c894bb7d71

添加数据统计存储过程

 deploy/sql/0201-proc_stat_taxi_data ÿСʱ³µÔØÉϱ¨Íêµ÷ÓÃ.sql

New file
@@ -0,0 +1,338 @@
/*
1、找到每辆taxi æ¯åˆ†é’Ÿçš„最后位置，插入t_d_taxi_position表；
2、生成 gid_100 if gid_100 is null;
3、判断是否满足小时统计条件，统计各类hour数据；
4、判断是否满足天统计条件，统计各类day数据；

stat_texi_bd   texi数据统计
call pro_stat_taxi_data('2021-07-13 23:00:00');
call pro_stat_taxi_data('2021-07-17 03:00:00');
call pro_stat_taxi_data('2021-10-18 03:00:00');
select * from t_d_grid100_hour where DATE(datatime)=Date('2021-07-15 23:00:00') and HOUR(datatime)=HOUR('2021-07-15 23:00:00') limit 1;

create table t_re_region_roadsection(id int primary key auto_increment) as
SELECT g.road_id AS "road_id", r.RegionCode AS "RegionCode","admin" as Creation_By, NULL as Creation_Date
 FROM t_roadsection g, d_region r
 WHERE st_intersects(g.shape, r.shape);

*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_stat_taxi_data`;
CREATE PROCEDURE `proc_stat_taxi_data`(in time_start varchar(40))
BEGIN



-- 1、找到每辆taxi æ¯åˆ†é’Ÿçš„最后位置，插入t_d_taxi_position表；  ä¸åš

-- 2、生成 gid_100 if gid_100 is null;

-- 3、判断是否满足小时统计条件，统计各类hour数据；

-- 4、判断是否满足天统计条件，统计各类day数据；



        declare datatime_ datetime ;

        DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
        DECLARE hour_end_ datetime DEFAULT now();			
        DECLARE day_start_ datetime DEFAULT now();

        DECLARE reset_hour_start_ datetime DEFAULT now();

        DECLARE reset_hour_end_ datetime DEFAULT now();

        DECLARE day_end_ datetime DEFAULT now();	
        DECLARE has_data_ int DEFAULT 0;

        DECLARE has_day_data_ int DEFAULT 0;			
		
        set hour_start_ = time_start;



        set hour_start_ = concat(date(hour_start_),' ',LPAD(HOUR(hour_start_),2,0),':00:00');

        set hour_end_ = DATE_ADD(hour_start_,INTERVAL 1 HOUR);

        set day_end_ = concat(date(hour_start_),' 00:00:00');

        set day_start_ = DATE_SUB(day_end_,INTERVAL 24 HOUR);

        set reset_hour_start_ = day_start_;

        set reset_hour_end_ =  DATE_ADD(day_start_,INTERVAL 1 HOUR);

		
        select 1 into has_data_ from t_d_grid100_hour where DATE(posttime)=Date(hour_start_) and HOUR(posttime)=HOUR(hour_start_) limit 1;

        select 1 into has_day_data_ from t_d_grid100_day where date(posttime)=date(day_start_) limit 1;



        #select hour_start_,hour_end_,day_start_,day_end_,has_data_,has_day_data_ ;

#select * from t_d_grid100_day   delete from t_d_grid100_day where posttime='2021-07-16 00:00:00'



        # è½¦è¾†æœ€æ–°ä½ç½®

        #select LON_BAIDU,LAT_BAIDU,posttime,MN from (select * from taxi_realtimedata order by posttime desc limit 100000) A group by MN;

		
        # æ›´æ–°gid_100字段  select * from taxi_realtimedata order by posttime desc limit 100;

        update taxi_realtimedata set gid_100=CONCAT(LEFT(LON,7),'_',LEFT(LAT,6)) where gid_100 is null and posttime>=hour_start_ and posttime<hour_end_;

		


        # ç»Ÿè®¡ t_d_grid100_hour   select * from t_d_grid100_hour limit 10;

        IF has_data_=0 and now()>hour_end_ THEN

            insert into t_d_grid100_hour (grid_id,posttime,PM25,PM10,CO,NO2,O3,NO,NOX,point_count) 
                select d.gid_100,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>=hour_start_ and posttime<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,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>=hour_start_ and posttime<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,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>=hour_start_ and posttime<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,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>=hour_start_ and posttime<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,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>=hour_start_ and posttime<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,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>=hour_start_ and posttime<hour_end_ and re.regioncode is not null 
                    group by re.regioncode;

	
        END IF;





        # æ¯æ—¥3点  ç»Ÿè®¡å‰ä¸€å¤©çš„taxi数据

        IF has_day_data_=0 and hour(time_start)>=3 THEN



            # æ¸…理当天每小时统计数据

            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;



            # ç»Ÿè®¡ 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 IF;			


			
END
//
DELIMITER ; 


	
	

 deploy/sql/0202-proc_reset_taxi_data_È˹¤ÉóºËºóµ÷ÓÃ.sql

New file
@@ -0,0 +1,220 @@
/*
  é‡æ–°ç»Ÿè®¡å¤©æ±‡æ€»æ•°æ®
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 ; 


	
	

 deploy/sql/0203-proc_stat_jiance_data_hour ÿСʱ΢վÊý¾ÝÉϱ¨Íêµ÷ÓÃ.sql

New file
@@ -0,0 +1,130 @@
/*
stat_weizhan_bd   å¾®ç«™æ•°æ®ç»Ÿè®¡
call proc_stat_jiance_data_hour ('2021-10-13 10:00:00');
call proc_stat_jiance_data_day    ('2021-10-11 03:00:00');

pro_stat_jiance_data_hour    1、根据road_bd_hourdata å’Œ site_bd_hourdata æ•°æ®è¡¨ï¼Œç”Ÿæˆ t_d_jiance_hour å’Œ t_d_weizhan_hour
pro_stat_jiance_data_day    1、根据road_bd_daydata_cal å’Œ site_bd_daydata_cal æ•°æ®è¡¨ï¼Œç”Ÿæˆ t_d_jiance_day å’Œ t_d_weizhan_day

        # æŒ‡æ ‡å¯¹åº”关系
        ### å¾®ç«™&监测点 
        select * from road_bd_device order by id desc limit 100;
        select * from road_bd_contam;
        select * from road_bd_device_contam ;
        select * from road_bd_hourdata limit 100;
        select * from t_d_jiance_hour;
        select * from t_d_weizhan_hour;
        select r.PostTime,r.MN,r.pID,r.AvgValue,d.LNG as d_LNG,d.LAT as d_LAT,concat(TRUNCATE(d.LNG,3),'_',TRUNCATE(d.LAT,3)) as gid_100,bdc.pName,r.AvgValue 
                from road_bd_hourdata r right join 
                    (select MN,LNG,LAT,ID from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d on r.MN=d.MN and r.MN is not null 
                    left join road_bd_contam bdc on bdc.pCode=r.pID 
                where r.PostTime='2021-08-01 00:00:00' and locate(bdc.pName,'PM2.5,PM10,CO浓度值,NO浓度值,NO2浓度值,O3')
                order by r.PostTime asc,r.MN asc, r.pID asc; 
        # ç›‘测点
        select * from t_d_jiance_hour limit 100;
		
        select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,
                MAX(case r.pID when '01' then  AvgValue end) as tsp, 
                MAX(case r.pID when '01A' then  AvgValue end) as PM25,
                MAX(case r.pID when '01B' then  AvgValue end) as PM10,
                MAX(case r.pID when 'CO' then  AvgValue end) as CO,
                MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,
                MAX(case r.pID when 'NO2' then  AvgValue end) as NO2
                from road_bd_hourdata r right join 
                    (select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d
                    on r.MN=d.MN and r.MN is not null 
                where r.PostTime='2021-08-01 00:00:00' and locate(r.pID,'01,01A,01B,CO,NO,NO2') 
                group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                order by r.PostTime asc,r.MN asc, r.pID asc; 
        delete from road_bd_hourdata where PostTime is not null;
				
        select * from t_d_jiance_hour order by id desc limit 100;		
        select * from t_d_weizhan_hour order by id desc limit 100;		
		
        round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),2) as NOX 
*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_stat_jiance_data_hour`;
CREATE PROCEDURE `proc_stat_jiance_data_hour`(in start_time_ varchar(40))
BEGIN



        DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
        DECLARE hour_end_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);

        DECLARE has_weizhan_data_ int DEFAULT 0;

        DECLARE has_site_data_ int DEFAULT 0;



        #DECLARE start_time_ datetime DEFAULT now();	
        if (SELECT DATE_FORMAT(start_time_,'%Y-%m-%d %H:%i:%s')) IS NULL then 
            set start_time_ = now();

        end if;

        select DATE_FORMAT(start_time_,'%Y-%m-%d %H:00:00') into start_time_;

        set hour_start_ = start_time_;

        set hour_end_ = DATE_ADD(hour_start_,INTERVAL 1 HOUR);	
		
        select 1 into has_weizhan_data_ from t_d_weizhan_hour where PostTime=hour_start_ limit 1;

        select 1 into has_site_data_ from t_d_jiance_hour where PostTime=hour_start_ limit 1;

		
		
        IF has_weizhan_data_=0 and now()>start_time_ THEN

                #微站数据行转列

                insert into t_d_weizhan_hour (PostTime,MN,SiteName,lon,lat,PM25,PM10,O3,CO,`NO`,NO2,SO2) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            #MAX(case r.pID when '01' then  AvgValue end) as tsp, 
                            MAX(case r.pID when 'PM25' then  AvgValue end) as PM25,

                            MAX(case r.pID when 'PM10' then  AvgValue end) as PM10,

                            MAX(case r.pID when 'O3' then  AvgValue end) as O3,

                            MAX(case r.pID when 'CO' then  AvgValue end) as CO,

                            MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,

                            MAX(case r.pID when 'NO2' then  AvgValue end) as NO2,

                            MAX(case r.pID when 'SO2' then  AvgValue end) as SO2							
                            from road_bd_hourdata r right join 
                                #(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and devtype=4 and mn like 'SXYD0%') d

                                on r.MN=d.MN and r.MN is not null 
                            where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01,PM25,PM10,O3,CO,NO,NO2,SO2') 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
                # æ›´æ–°NOX指标

                update t_d_weizhan_hour set NOX=round((NO*2.05/1.34+NO2),2) where NOX is NULL;

        END IF;

--         select * from t_d_jiance_hour limit 100;

--         select * from site_bd_hourdata  limit 100;

--         select * from site_bd_device limit 100;

        IF has_site_data_=0 and now()>start_time_ THEN

                # ç›‘测点数据行转列

                insert into t_d_jiance_hour (PostTime,MN,SiteName,lon,lat,tsp) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            MAX(case r.pID when '01' then  AvgValue end) as tsp 
                            from road_bd_hourdata r right join 
                                #(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and ID in (select max(ID) from road_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and devtype=4) d

                                on r.MN=d.MN and r.MN is not null 
                            where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01') 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
                # ç›‘测点数据行转列

                insert into t_d_jiance_hour (PostTime,MN,SiteName,lon,lat,tsp) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            MAX(case r.pID when '01' then  AvgValue end) as tsp 
                            from site_bd_hourdata r right join 
                                #(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where ID in (select max(ID) from site_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from site_bd_device bd where devtype=4) d

                                on r.MN=d.MN and r.MN is not null 
                            where r.PostTime>=hour_start_ and r.PostTime<hour_end_ and locate(r.pID,'01') 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
        END IF;



END
//
DELIMITER ; 


	
	


	
	

 deploy/sql/0204-proc_stat_jiance_data_day ÿÈÕ΢վÊý¾ÝÉϱ¨Íêµ÷ÓÃ.sql

New file
@@ -0,0 +1,118 @@
/*
stat_weizhan_bd   å¾®ç«™æ•°æ®ç»Ÿè®¡
call pro_stat_jiance_data_hour ('2021-10-12 23:00:00');
call proc_stat_jiance_data_day    ('2021-07-16 03:00:00');
call proc_stat_jiance_data_day    ('2021-07-15 23:00:00');

pro_stat_jiance_data_hour    1、根据road_bd_hourdata å’Œ site_bd_hourdata æ•°æ®è¡¨ï¼Œç”Ÿæˆ t_d_jiance_hour å’Œ t_d_weizhan_hour
pro_stat_jiance_data_day    1、根据road_bd_daydata_cal å’Œ site_bd_daydata_cal æ•°æ®è¡¨ï¼Œç”Ÿæˆ t_d_jiance_day å’Œ t_d_weizhan_day

select * from t_d_weizhan_day;
select * from t_d_jiance_day;
select * from site_bd_daydata_cal;
select * from road_bd_daydata_cal;

        round((round(avg(NO),0)*2.05/1.34+round(avg(NO2),0)),2) as NOX 
*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `proc_stat_jiance_data_day`;
CREATE PROCEDURE `proc_stat_jiance_data_day`(in start_time_ varchar(40))
BEGIN

        DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
        DECLARE has_weizhan_data_ int DEFAULT 0;

        DECLARE has_site_data_ int DEFAULT 0;

        DECLARE reset_hour_start_ datetime DEFAULT now();

        DECLARE reset_hour_end_ datetime DEFAULT now();



        #DECLARE start_time_ datetime DEFAULT now();	
        if (SELECT DATE_FORMAT(start_time_,'%Y-%m-%d %H:%i:%s')) IS NULL then 
            set start_time_ = now();

        end if;

        set hour_start_ = start_time_;

		
        select DATE_FORMAT(start_time_,'%Y-%m-%d 00:00:00') into start_time_;

        set start_time_ = DATE_SUB(start_time_,INTERVAL 24 HOUR);

        set reset_hour_start_ = start_time_;

        set reset_hour_end_ =  DATE_ADD(reset_hour_start_,INTERVAL 24 HOUR);

		
        select 1 into has_weizhan_data_ from t_d_weizhan_day where PostTime=start_time_ limit 1;

        select 1 into has_site_data_ from t_d_jiance_day where PostTime=start_time_ limit 1;

		
        select start_time_,reset_hour_start_,reset_hour_end_,has_weizhan_data_,has_site_data_;

		
        IF has_weizhan_data_=0 and hour(hour_start_)>=3  THEN

                #清除当日小时数据，并重新写入

                delete from t_d_weizhan_hour where posttime>=reset_hour_start_ and posttime<reset_hour_end_;

                delete from t_d_jiance_hour where posttime>=reset_hour_start_ and posttime<reset_hour_end_;

                #重写小时数据

                select reset_hour_start_,reset_hour_end_,reset_hour_start_<reset_hour_end_ ;

                WHILE reset_hour_start_<reset_hour_end_ DO

                    call proc_stat_jiance_data_hour(reset_hour_start_);

                    set reset_hour_start_ = DATE_ADD(reset_hour_start_,INTERVAL 1 HOUR);

                END WHILE;

                select reset_hour_start_,reset_hour_end_,reset_hour_start_<reset_hour_end_ ;

                #微站数据行转列

                insert into t_d_weizhan_day (PostTime,MN,SiteName,lon,lat,PM25,PM10,O3,CO,`NO`,NO2,SO2) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            #MAX(case r.pID when '01' then  AvgValue end) as tsp, 
                            MAX(case r.pID when 'PM25' then  AvgValue end) as PM25,

                            MAX(case r.pID when 'PM10' then  AvgValue end) as PM10,

                            MAX(case r.pID when 'O3' then  AvgValue end) as O3,

                            MAX(case r.pID when 'CO' then  AvgValue end) as CO,

                            MAX(case r.pID when 'NO' then  AvgValue end) as `NO`,

                            MAX(case r.pID when 'NO2' then  AvgValue end) as NO2,

                            MAX(case r.pID when 'SO2' then  AvgValue end) as SO2		
                            from road_bd_daydata_cal r right join 
                                #(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and ID in (select max(ID) from road_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType=5 and devtype=4 and mn like 'SXYD0%') d

                                on r.MN=d.MN and r.MN is not null 
                            where r.PostTime=start_time_ and locate(r.pID,'01,PM25,PM10,O3,CO,NO,NO2,SO2') 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
                # æ›´æ–°NOX指标

                update t_d_weizhan_day set NOX=round((NO*2.05/1.34+NO2),2) where NOX is NULL and PostTime=start_time_;

		
        END IF;

		
--         select * from t_d_jiance_hour limit 100;

--         select * from site_bd_hourdata  limit 100;

--         select * from site_bd_device limit 100;

        IF has_site_data_=0 and hour(hour_start_)>=3  THEN

                # ç›‘测点数据行转列

                insert into t_d_jiance_day (PostTime,MN,SiteName,lon,lat,tsp) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            MAX(case r.pID when '01' then  AvgValue end) as tsp 
                            from road_bd_daydata_cal r right join 
                                #(select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and ID in (select max(ID) from road_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from road_bd_device bd where ProjectType<>5 and devtype=4) d

                                on r.MN=d.MN and r.MN is not null 
                            where r.posttime>=start_time_ and r.posttime<reset_hour_end_ and locate(r.pID,'01')=1 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
                # ç›‘测点数据行转列

                insert into t_d_jiance_day (PostTime,MN,SiteName,lon,lat,tsp) 
                    select r.PostTime,r.MN,d.SiteName,d.LNG as d_LNG,d.LAT as d_LAT,

                            MAX(case r.pID when '01' then  AvgValue end) as tsp 
                            from site_bd_daydata_cal r right join 
                                #(select MN,LNG,LAT,ID,SiteName from site_bd_device bd where ID in (select max(ID) from site_bd_device group by MN) ) d

                                (select MN,LNG,LAT,ID,SiteName from site_bd_device bd where devtype=4) d

                                on r.MN=d.MN and r.MN is not null 
                            where r.posttime>=start_time_ and r.posttime<reset_hour_end_ and locate(r.pID,'01')=1 
                            group by r.PostTime,r.MN,d.SiteName,d.LNG,d.LAT 
                            order by r.PostTime asc,r.MN asc, r.pID asc; 
        END IF;

		


END
//
DELIMITER ; 


	
	


	
	

 deploy/sql/0205-pro_stat_guokong_data_day ¹ú¿ØÊý¾ÝÉϱ¨Íêµ÷ÓÃ.sql

New file
@@ -0,0 +1,74 @@
/*
1、根据过去24小时的书，生成国控点天数据
pro_stat_guokong_data_day å›½æŽ§ç‚¹æ•°æ®ç»Ÿè®¡
call pro_stat_guokong_data_day('2021-10-09 23:00:00');
call pro_stat_guokong_data_day('2021-10-09 03:00:00');

select * from t_d_guokong_hour;
select * from t_d_guokong_day;

delete from t_d_guokong_day;

create table t_d_guokong_day as select * from t_d_guokong_hour where 1<>1;

*/
-- -------------------------------------

DELIMITER // 
DROP PROCEDURE IF EXISTS `pro_stat_guokong_data_day`;
CREATE PROCEDURE `pro_stat_guokong_data_day`(in time_start varchar(40))
BEGIN



-- 1、找到每辆taxi æ¯åˆ†é’Ÿçš„最后位置，插入t_d_taxi_position表；  ä¸åš

-- 2、生成 gid_100 if gid_100 is null;

-- 3、判断是否满足小时统计条件，统计各类hour数据；

-- 4、判断是否满足天统计条件，统计各类day数据；



        declare PostTime_ datetime ;

        DECLARE hour_start_ datetime DEFAULT DATE_SUB(now(),INTERVAL 1 HOUR);	
        DECLARE hour_end_ datetime DEFAULT now();			
        DECLARE day_start_ datetime DEFAULT now();	
        DECLARE day_end_ datetime DEFAULT now();				
        DECLARE has_data_ int DEFAULT 0;		
        DECLARE has_day_data_ int DEFAULT 0;			
		
        set hour_start_ = time_start;



        set hour_start_ = concat(date(hour_start_),' ',LPAD(HOUR(hour_start_),2,0),':00:00');

        set hour_end_ = DATE_ADD(hour_start_,INTERVAL 1 HOUR);

        set day_end_ = concat(date(hour_start_),' 00:00:00');

        set day_start_ = DATE_SUB(day_end_,INTERVAL 24 HOUR);

		
        select 1 into has_day_data_ from t_d_guokong_day where date(TimePoint)=date(day_start_) limit 1;



        #select hour_start_,hour_end_,day_start_,day_end_,has_data_,has_day_data_ ;



        # æ¯æ—¥3点  ç»Ÿè®¡å‰ä¸€å¤©çš„国控点数据

        IF has_day_data_=0 and hour(time_start)>=3 THEN

            insert into t_d_guokong_day (SiteID,TimePoint,Area,PositionName,Latitude,Longitude,

                    PM25,PM10,CO,NO2,SO2,O31,O38,PValue,Quality) 
                select d.SiteID,day_start_,d.Area,d.PositionName,d.Latitude,d.Longitude,

                    round(avg(PM25),0) as PM25,round(AVG(PM10),0) as PM10,round(avg(CO),2) as CO,round(avg(NO2),2) as NO2,

                    round(avg(SO2),0) as SO2,round(avg(O31),0) as O31,round(avg(O38),0) as O38,

                    round(avg(PValue),0) as PValue,

                    case when round(avg(AQI),0)<=50  then '优' 
                            when round(avg(AQI),0)<=100 and round(avg(AQI),0)>50 then '良' 
                            when round(avg(AQI),0)<=150 and round(avg(AQI),0)>100 then '轻度污染'

                            when round(avg(AQI),0)<=200 and round(avg(AQI),0)>150 then '中度污染'

                            when round(avg(AQI),0)<=300 and round(avg(AQI),0)>200 then '重度污染'

                            when round(avg(AQI),0)>300 then '严重污染' end as Quality 
                    from t_d_guokong_hour d 
                    where date(TimePoint)=date(day_start_) #date(TimePoint)=date('2021-09-08 00:00:00') 
                    group by d.SiteID,day_start_,d.Area,d.PositionName,d.Latitude,d.Longitude;



	
        END IF;			


			
END
//
DELIMITER ;