From 516993b06f31e352e0363f0f441550c894bb7d71 Mon Sep 17 00:00:00 2001
From: wangrong <wangrong@shsening.com>
Date: 星期三, 24 十一月 2021 21:17:39 +0800
Subject: [PATCH] 添加数据统计存储过程
---
deploy/sql/0201-proc_stat_taxi_data 每小时车载上报完调用.sql | 338 ++++++++++++++++++++++
deploy/sql/0202-proc_reset_taxi_data_人工审核后调用.sql | 220 ++++++++++++++
deploy/sql/0203-proc_stat_jiance_data_hour 每小时微站数据上报完调用.sql | 130 ++++++++
deploy/sql/0204-proc_stat_jiance_data_day 每日微站数据上报完调用.sql | 118 +++++++
deploy/sql/0205-pro_stat_guokong_data_day 国控数据上报完调用.sql | 74 ++++
5 files changed, 880 insertions(+), 0 deletions(-)
diff --git "a/deploy/sql/0201-proc_stat_taxi_data \346\257\217\345\260\217\346\227\266\350\275\246\350\275\275\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql" "b/deploy/sql/0201-proc_stat_taxi_data \346\257\217\345\260\217\346\227\266\350\275\246\350\275\275\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
new file mode 100644
index 0000000..4d6190d
--- /dev/null
+++ "b/deploy/sql/0201-proc_stat_taxi_data \346\257\217\345\260\217\346\227\266\350\275\246\350\275\275\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
@@ -0,0 +1,338 @@
+/*
+1銆佹壘鍒版瘡杈唗axi 姣忓垎閽熺殑鏈�鍚庝綅缃紝鎻掑叆t_d_taxi_position琛紱
+2銆佺敓鎴� gid_100 if gid_100 is null;
+3銆佸垽鏂槸鍚︽弧瓒冲皬鏃剁粺璁℃潯浠讹紝缁熻鍚勭被hour鏁版嵁锛�
+4銆佸垽鏂槸鍚︽弧瓒冲ぉ缁熻鏉′欢锛岀粺璁″悇绫籨ay鏁版嵁锛�
+
+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銆佹壘鍒版瘡杈唗axi 姣忓垎閽熺殑鏈�鍚庝綅缃紝鎻掑叆t_d_taxi_position琛紱 涓嶅仛
+-- 2銆佺敓鎴� gid_100 if gid_100 is null;
+-- 3銆佸垽鏂槸鍚︽弧瓒冲皬鏃剁粺璁℃潯浠讹紝缁熻鍚勭被hour鏁版嵁锛�
+-- 4銆佸垽鏂槸鍚︽弧瓒冲ぉ缁熻鏉′欢锛岀粺璁″悇绫籨ay鏁版嵁锛�
+
+ 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 ;
+
+
+
+
\ No newline at end of file
diff --git "a/deploy/sql/0202-proc_reset_taxi_data_\344\272\272\345\267\245\345\256\241\346\240\270\345\220\216\350\260\203\347\224\250.sql" "b/deploy/sql/0202-proc_reset_taxi_data_\344\272\272\345\267\245\345\256\241\346\240\270\345\220\216\350\260\203\347\224\250.sql"
new file mode 100644
index 0000000..9c9436b
--- /dev/null
+++ "b/deploy/sql/0202-proc_reset_taxi_data_\344\272\272\345\267\245\345\256\241\346\240\270\345\220\216\350\260\203\347\224\250.sql"
@@ -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 ;
+
+
+
+
\ No newline at end of file
diff --git "a/deploy/sql/0203-proc_stat_jiance_data_hour \346\257\217\345\260\217\346\227\266\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql" "b/deploy/sql/0203-proc_stat_jiance_data_hour \346\257\217\345\260\217\346\227\266\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
new file mode 100644
index 0000000..dce7323
--- /dev/null
+++ "b/deploy/sql/0203-proc_stat_jiance_data_hour \346\257\217\345\260\217\346\227\266\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
@@ -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銆佹牴鎹畆oad_bd_hourdata 鍜� site_bd_hourdata 鏁版嵁琛紝鐢熸垚 t_d_jiance_hour 鍜� t_d_weizhan_hour
+pro_stat_jiance_data_day 1銆佹牴鎹畆oad_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 ;
+
+
+
+
+
+
+
+
\ No newline at end of file
diff --git "a/deploy/sql/0204-proc_stat_jiance_data_day \346\257\217\346\227\245\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql" "b/deploy/sql/0204-proc_stat_jiance_data_day \346\257\217\346\227\245\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
new file mode 100644
index 0000000..e6ae387
--- /dev/null
+++ "b/deploy/sql/0204-proc_stat_jiance_data_day \346\257\217\346\227\245\345\276\256\347\253\231\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
@@ -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銆佹牴鎹畆oad_bd_hourdata 鍜� site_bd_hourdata 鏁版嵁琛紝鐢熸垚 t_d_jiance_hour 鍜� t_d_weizhan_hour
+pro_stat_jiance_data_day 1銆佹牴鎹畆oad_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 ;
+
+
+
+
+
+
+
+
\ No newline at end of file
diff --git "a/deploy/sql/0205-pro_stat_guokong_data_day \345\233\275\346\216\247\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql" "b/deploy/sql/0205-pro_stat_guokong_data_day \345\233\275\346\216\247\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
new file mode 100644
index 0000000..382e1eb
--- /dev/null
+++ "b/deploy/sql/0205-pro_stat_guokong_data_day \345\233\275\346\216\247\346\225\260\346\215\256\344\270\212\346\212\245\345\256\214\350\260\203\347\224\250.sql"
@@ -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銆佹壘鍒版瘡杈唗axi 姣忓垎閽熺殑鏈�鍚庝綅缃紝鎻掑叆t_d_taxi_position琛紱 涓嶅仛
+-- 2銆佺敓鎴� gid_100 if gid_100 is null;
+-- 3銆佸垽鏂槸鍚︽弧瓒冲皬鏃剁粺璁℃潯浠讹紝缁熻鍚勭被hour鏁版嵁锛�
+-- 4銆佸垽鏂槸鍚︽弧瓒冲ぉ缁熻鏉′欢锛岀粺璁″悇绫籨ay鏁版嵁锛�
+
+ 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 ;
+
+
+
+
\ No newline at end of file
--
Gitblit v1.9.1