Hello having a table like:
DateTime Ip
2016-12-21 17:00:01 | 127.0.0.1
2016-12-21 17:00:01 | 127.0.0.1
2016-12-21 17:00:03 | 127.0.0.1
2016-12-21 17:00:05 | 127.0.0.2
2016-12-21 17:00:06 | 127.0.0.2
2016-12-21 17:00:06 | 127.0.0.1
2016-12-21 17:00:07 | 127.0.0.2
2016-12-21 17:00:08 | 127.0.0.2
2016-12-21 17:00:08 | 127.0.0.1
2016-12-21 17:00:08 | 127.0.0.1
currently to calculate request per second of an ip in a certain period of time eg 5s I do:
SELECT Ip, total/diff_in_secs as Rps FROM (
SELECT Ip, count(*) as total, MAX(DateTime), MIN(DateTime), TIMESTAMPDIFF(SECOND, MIN(DateTime), MAX(DateTime)) as diff_in_secs
FROM requests WHERE DateTime >= '2016-12-21 17:00:01' AND DateTime <= '2016-12-21 17:00:05'
GROUP BY Ip
) as base
ORDER BY Rps Desc
I am trying to figure out a way to do variance and standard deviation of each ip during that period of time, from the rps any idea ? i am trying to apply the concepts in this answer with little success Calculate average, variance and standard deviation of two numbers in two different rows/columns with sql / PHP on specific dates
Thank you
Try with this
To reduce the size of the inner select you can also count requests for instance every 5 seconds using
UNIX_TIMESTAMP(datetime) div 5
like this