Re: Average every n records

[Peter Brawley]

> I have a simple database made of 2 columns: id, temperature.

That's a table not a database;

> converting the last 17280 records into a set of (for instance) 1440 . representing the average of temperatures recorded per minute,
> i.e. calculate the average temperature every 12 records?

At 12 rows/min you have 720 rows/hr, not 1440, but in what follows you can adjust params as need be. It's a 3-step ...

(i) assemble a rowset of the last 17280 rows. One way, assuming there are no missing ids ...

select id
from tbl
where id >= (select count(*) from tbl)-17280
order by id ;

Or if there may be missing ids ...

set @n = (select count(*) from tbl)-17280;
select id
from tbl
order by i limit @n,17280 ;

(ii) Compute range groups with the expression id div 1440. We'll group by that.

(iii) take the count and average in each group.

So we have ...

set @n = (select count(*) from tbl)-17280;
select i div 1440 as grp, count(*) as N, avg(temp) as avgtemp
from (
  select id, temp 
  from tbl
  order by i limit @n,17280 ;
) temps
group by grp
order by grp;

Edited 1 time(s). Last edit at 04/10/2018 11:19AM by Peter Brawley.