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