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