CODEWITHSUNDEEP
mysql
ChanChow
ChanChow

Reputation: 1366

How to calculate an computed average in MySQL?

I need to calculate average of rows depending on the time stamps and group by field1. For example, I have a table with

Field1      Field2      TImeStamp
  X           X1    11/19/2012 12:21:32
  X           X2    11/19/2012 12:22:32
  X           X3    11/19/2012 12:24:32
  Y           Y1    11/19/2012 12:21:32
  Y           Y2    11/19/2012 12:22:32
  Y           Y3    11/19/2012 12:23:32
  Y           Y4    11/19/2012 12:24:32

I have this kind of table with timestamp being inserted with current time along with the other fields. Now I would like to compute average based on time stamp. For example, I would like to have average of the latest + average of the other during a particular duration. In above case I would like to have :

Fields   Averaged function
  X        (X3+(X1+X2)/2)/2
  Y        (Y4+(Y1+Y2+Y3)/3)/2

For above I have to compute average of (now + average(now-1 minutes to now-5 minutes)). How can I do that?

Upvotes: 2

Views: 200

Answers (1)

Barmar
Barmar

Reputation: 782130

I think this will do it:

select field1, if(avgf2 = 0,maxf2,(maxf2+avgf2)/2) avgfun
from (select m.field1 field1,
             max(if(timestamp = maxts, field2, null)) maxf2,
             coalesce(avg(if(timestamp = maxts, null, field2)),0) avgf2
      from mytable m
      join (select field1, max(timestamp) maxts
            from mytable
            where timestamp between date_sub(now(), interval 5 minute) and now()
            group by field1) mm
      using (field1)
      where timestamp between date_sub(now(), interval 5 minute) and now()
      group by field1) x

The key is that aggregate functions like MAX and AVG ignore NULL.

To only check a particular time range, insert WHERE timestamp BETWEEN <begin> AND <end> into both subqueries.

Upvotes: 1

Related Questions