CODEWITHSUNDEEP
mysql
Peter Kazazes
Peter Kazazes

Reputation: 3628

Calculate Average Every x Rows with MySQL Query

I have a MySQL table structured like so:

timestamp | messageVals
-----------------------
1325825203| 46.5
1325825306| 48
1325825404| 43.75
1325825835| 49.625
1325826003| 49.625
1325826055| 47.125
1325794709| 42.875

And so on and so fourth, it has just over 2,000 total rows. I'd like to calculate the average of a set of twenty at a time, and would assumbbly add another column for the average. So every twentieth row would have a value in the avg column that would be an average of the twenty preceding values, which would then reset and do the average of the next twenty.

          timestamp | messageVals | avg
          --------------------------------
          1325825203| 46.5        |
          1325825306| 48          |
          1325825404| 43.75       |
          1325825835| 49.625      |
          1325826003| 49.625      |
          1325826055| 47.125      |
(20th)    1325794709| 42.875      | 47.15

What's the most easy to understand query that will fill the third column with the averages?

Upvotes: 0

Views: 1638

Answers (1)

Ike Walker
Ike Walker

Reputation: 65587

It's not clear to me whether you actually want to persist the average values in your table or just calculate them in a SELECT query.

Assuming it's the latter, you could do something like this:

set @rownum := 0;
set @sum := 0;

select ts,messageVals,the_avg
from (
  select ts,messageVals,
  @rownum := (@rownum + 1) as rownum, 
  @sum := IF(@rownum mod 20 = 1,0 + messageVals,@sum + messageVals) as running_sum,
  IF(@rownum mod 20 = 0,@sum / 20,NULL) as the_avg
  from so9571582
  order by ts
) s;

Upvotes: 2

Related Questions