> 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.