How calculate mysql variance and standard deviation within timeframe

Question

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

Answer 1

Try with this

select 
requests_per_sec.ip, 
avg(requests_per_sec.n) as Avg_Req_Per_Sec,
std(requests_per_sec.n) as Std_Req_Per_Sec
from
 (select ip, count(ip) as n, datetime 
  from requests 
  group by datetime, ip) requests_per_sec 
group by requests_per_sec.ip

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

select 
requests_per_sec.ip, 
avg(requests_per_sec.n) as Avg_Req_Per_Sec,
std(requests_per_sec.n) as Std_Req_Per_Sec
from
 (select ip, count(ip) as n, datetime 
  from requests 
  group by UNIX_TIMESTAMP(datetime) div 5, ip) requests_per_sec 
group by requests_per_sec.ip