Note that with this formula you must cover all telephones which are on-line. The numbers do not show all telephones. You mixed up something.
Emin Gabrielyan There is something wrong about the numbers. The total of the two columns must be equal to the number of customers, if you respected the algorithm. Are you sure that the following condition is respected, and that you did not mix A with B?
Start < B and Stop > A (meaning any intersection)
Emin Gabrielyan This is fantastically clear! Bravo! Emin
Emin Gabrielyan
Salut,
Ci-dessous les statistiques MySQL (DB3):
mysql> CALL StatReg('91.121.142.9','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START | STOP | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 | 3 | 27 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 | 2 | 26 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 | 4 | 26 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 | 1 | 27 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 | 4 | 26 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 | 1 | 26 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 | 2 | 27 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 | 1 | 26 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 | 4 | 26 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 | 1 | 26 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 | 4 | 26 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 | 2 | 26 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 | 4 | 25 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 | 3 | 25 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 | 5 | 26 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 | 1 | 26 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 | 5 | 26 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 | 1 | 27 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 | 3 | 26 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 | 2 | 26 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 | 4 | 26 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 | 0 | 27 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 | 5 | 26 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 | 2 | 27 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 | 2 | 27 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 | 2 | 27 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 | 4 | 26 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 | 1 | 26 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 | 2 | 27 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 | 1 | 26 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 | 2 | 26 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 | 2 | 26 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 | 0 | 0 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 | 2 | 0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 | 2 | 1 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 | 1 | 1 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 | 3 | 0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 | 5 | 0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.34 sec)
Query OK, 38 rows affected, 2 warnings (1.34 sec)
mysql> CALL StatReg('91.121.147.45','2011-06-16 00:00:00',NOW(),30);
+---------------------+---------------------+-----------+-----------+
| START | STOP | COUNT = 1 | COUNT > 1 |
+---------------------+---------------------+-----------+-----------+
| 2011-06-16 00:00:00 | 2011-06-16 00:30:00 | 6 | 6 |
| 2011-06-16 00:30:00 | 2011-06-16 01:00:00 | 6 | 6 |
| 2011-06-16 01:00:00 | 2011-06-16 01:30:00 | 6 | 6 |
| 2011-06-16 01:30:00 | 2011-06-16 02:00:00 | 6 | 6 |
| 2011-06-16 02:00:00 | 2011-06-16 02:30:00 | 6 | 6 |
| 2011-06-16 02:30:00 | 2011-06-16 03:00:00 | 5 | 7 |
| 2011-06-16 03:00:00 | 2011-06-16 03:30:00 | 6 | 6 |
| 2011-06-16 03:30:00 | 2011-06-16 04:00:00 | 5 | 7 |
| 2011-06-16 04:00:00 | 2011-06-16 04:30:00 | 6 | 6 |
| 2011-06-16 04:30:00 | 2011-06-16 05:00:00 | 6 | 6 |
| 2011-06-16 05:00:00 | 2011-06-16 05:30:00 | 6 | 6 |
| 2011-06-16 05:30:00 | 2011-06-16 06:00:00 | 6 | 6 |
| 2011-06-16 06:00:00 | 2011-06-16 06:30:00 | 6 | 6 |
| 2011-06-16 06:30:00 | 2011-06-16 07:00:00 | 6 | 6 |
| 2011-06-16 07:00:00 | 2011-06-16 07:30:00 | 5 | 7 |
| 2011-06-16 07:30:00 | 2011-06-16 08:00:00 | 6 | 6 |
| 2011-06-16 08:00:00 | 2011-06-16 08:30:00 | 5 | 7 |
| 2011-06-16 08:30:00 | 2011-06-16 09:00:00 | 5 | 7 |
| 2011-06-16 09:00:00 | 2011-06-16 09:30:00 | 4 | 7 |
| 2011-06-16 09:30:00 | 2011-06-16 10:00:00 | 6 | 6 |
| 2011-06-16 10:00:00 | 2011-06-16 10:30:00 | 6 | 6 |
| 2011-06-16 10:30:00 | 2011-06-16 11:00:00 | 7 | 6 |
| 2011-06-16 11:00:00 | 2011-06-16 11:30:00 | 6 | 5 |
| 2011-06-16 11:30:00 | 2011-06-16 12:00:00 | 6 | 6 |
| 2011-06-16 12:00:00 | 2011-06-16 12:30:00 | 5 | 6 |
| 2011-06-16 12:30:00 | 2011-06-16 13:00:00 | 5 | 7 |
| 2011-06-16 13:00:00 | 2011-06-16 13:30:00 | 5 | 6 |
| 2011-06-16 13:30:00 | 2011-06-16 14:00:00 | 7 | 6 |
| 2011-06-16 14:00:00 | 2011-06-16 14:30:00 | 6 | 6 |
| 2011-06-16 14:30:00 | 2011-06-16 15:00:00 | 6 | 6 |
| 2011-06-16 15:00:00 | 2011-06-16 15:30:00 | 6 | 6 |
| 2011-06-16 15:30:00 | 2011-06-16 16:00:00 | 8 | 6 |
| 2011-06-16 16:00:00 | 2011-06-16 16:30:00 | 8 | 2 |
| 2011-06-16 16:30:00 | 2011-06-16 17:00:00 | 1 | 0 |
| 2011-06-16 17:00:00 | 2011-06-16 17:30:00 | 1 | 0 |
| 2011-06-16 17:30:00 | 2011-06-16 18:00:00 | 2 | 0 |
| 2011-06-16 18:00:00 | 2011-06-16 18:30:00 | 3 | 0 |
| 2011-06-16 18:30:00 | 2011-06-16 19:00:00 | 3 | 0 |
+---------------------+---------------------+-----------+-----------+
38 rows in set (1.28 sec)
Query OK, 38 rows affected, 2 warnings (1.28 sec)
-- Nombre de client par serveur
mysql> select domain,count(*) from location2 group by domain;
+----------------+----------+
| domain | count(*) |
+----------------+----------+
| 91.121.142.9 | 185 | -> astrad6
| 91.121.147.45 | 115 | -> astrad7
| 91.121.143.56 | 265 |
| 91.121.16.79 | 160 |
| 91.121.178.108 | 182 |
+----------------+----------+
5 rows in set (0.00 sec)
|
On 2011-06-15 10:52, Task-By Emin Gabrielyan wrote:
Assume that we have the following location history table which is growing as a result of triggers of location table:
Start
Stop
Domain
Account
IP
Port
Count
Assume we have a period from A to B.
For each account
Count records
Where
Start < B and Stop > A (meaning any intersection)
If count = 1
Then the account does not loose its port
Else the telephone looses the connection
A php script or an excel file must display hour by hour the percentage of accounts loosing their port.
Emin Gabrielyan
Le Jun 14, 2011 à 1:05, Emin Gabrielyan <emin.gabrielyan@switzernet.com> a écrit :
Need a clear quantity of telephones with lost ports on all servers. Write a script that can compute it automatically.
Envoyé de mon iPad
|