OLD | NEW |
1 | 1 |
2 | 2 |
3 | 3 |
4 #First I filter all the idpaths belong to each pair of datacentres, for instanc
e,for #idNodes 1 and 3(Ireland and Oregon), all idPaths are in idTraceDirection=
2/9.And #I divide timestamp_ns to 1000000000 for better calculation further. | 4 #First I filter all the idpaths belong to each pair of datacentres, for instance
,for #idNodes 1 and 3(Ireland and Oregon), all idPaths are in idTraceDirection=2
/9.And #I divide timestamp_ns to 1000000000 for better calculation further. |
5 CREATE TEMPORARY TABLE· | 5 CREATE TEMPORARY TABLE· |
6 IF NOT EXISTS table1 AS | 6 IF NOT EXISTS table1 AS |
7 (SELECT idPath,FROM_UNIXTIME(MIN(timestamp_ns) div 1000000000) as startime FROM
Measurements | 7 (SELECT idPath,FROM_UNIXTIME(MIN(timestamp_ns) div 1000000000) as startime FROM
Measurements |
8 WHERE idPath IN (SELECT idPath from Paths where idTraceDirection in (1,5))· | 8 WHERE idPath IN (SELECT idPath from Paths where idTraceDirection in (1,5))· |
9 GROUP BY idpath HAVING COUNT(*)>0); | 9 GROUP BY idpath HAVING COUNT(*)>0); |
10 # select the min(startime) of the new table above,to find the very beginning of
new #paths appear. | 10 # select the min(startime) of the new table above,to find the very beginning of
new #paths appear. |
11 | 11 |
12 select min (startime) from table1; | 12 select min (startime) from table1; |
13 | 13 |
14 #For each hour increase, the startime(timestamp_ns div 1000000000) increase #360
0.In that case I make a loop to measure how many paths appear per hour. | 14 #For each hour increase, the startime(timestamp_ns div 1000000000) increase #360
0.In that case I make a loop to measure how many paths appear per hour. |
15 | 15 |
16 delimiter $$ | 16 delimiter $$ |
17 drop procedure if exists wk; | 17 drop procedure if exists wk; |
18 create procedure wk() | 18 create procedure wk() |
19 begin | 19 begin |
20 declare var int; | 20 declare var int; |
21 set var = 0; | 21 set var = 0; |
22 while var < 60 do | 22 while var < 60 do |
23 select count(idPath) from i where startime between 1499537000 + 3600*var and 1
499537000 + 3600*(var+1); | 23 select count(idPath) from i where startime between 1499537000 + 3600*var and 1
499537000 + 3600*(var+1); |
24 set var = var +1; | 24 set var = var +1; |
25 end while; | 25 end while; |
26 end $$ | 26 end $$ |
27 delimiter ; | 27 delimiter ; |
28 call wk(); | 28 call wk(); |
OLD | NEW |