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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
| /*
| 1、找到每辆taxi 每分钟的最后位置，插入t_d_taxi_position表；
| 2、生成 gid_100 if gid_100 is null;
| 3、判断是否满足小时统计条件，统计各类hour数据；
| 4、判断是否满足天统计条件，统计各类day数据；
|
| 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、找到每辆taxi 每分钟的最后位置，插入t_d_taxi_position表； 不做
| -- 2、生成 gid_100 if gid_100 is null;
| -- 3、判断是否满足小时统计条件，统计各类hour数据；
| -- 4、判断是否满足天统计条件，统计各类day数据；
|
| 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 ;
|
|
|
|
|
|
