I am currently having difficulty formulating this into an sql query:

I would like to average the data of a column here twa for a duration of 10 minutes starting from the last value of the table i.e. data included here: last date-10minutes<=date<=last date

I tried to start a first query but it does not show the right answer:

SELECT AVG(twa), horaire FROM OF50 WHERE ((SELECT horaire FROM of50 ORDER BY horaire DESC LIMIT 1)-INTERVAL '1 minutes'>horaire) ORDER BY horaire;

Regards,

CodePudding user response：

Maybe this will do.

with t as (select max(horaire) maxhoraire from of50)
select AVG(of50.twa)
from of50, t 
where of50.horaire between t.maxhoraire - interval '1 minute' and t.maxhoraire;

or even this may do, given that the last value can not be 'younger' then now and at least one event happened during the last minute, though it is not exactly the same and says 'the average over the last 1 minute'

select AVG(twa)
from of50 
where horaire >= now() - interval '1 minute';