SM9 SM9 - 4 months ago 19
SQL Question

Get average difference between all unique rows

I have a bantracker which logs to MySQL each ban, I would like to get the average difference between each unix time so I can estimate based on the data when the next ban will happen.

My thinking would be Last entry + average? Maybe I am completely wrong and somebody has a better idea.

The Table structure looks like this: enter image description here

Each apikey is unique.

Any help / ideas are much appreciated I don't mind if it's a MySQL query or PHP code.

Thanks!

Answer

You're trying to calculate a moving average:

function moving_average( $array ) {

  $z = sizeof( $array );

  for ( $i = 1; $i < $z; $i++ ) {
    $result[] = $array[ $i ] - $array[ $i-1 ];
  }

  return array_sum( $result ) / count( $result );

}

Hat tip to: Calculating the average increase from array values in PHP

Comments