/*
|
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 ;
|
|
|
|
|
|
|
|
|
