1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
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 ;
|
|
|
|
|
|
