Create average of values in last x hours for each xth row PostgreSQL

If you're on PG11+ then ranged window functions may help you:

SELECT  
  avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '3 hour' PRECEDING AND CURRENT ROW) as a 
FROM yourtable t;

If you have rows with a timestamp_col then for every row R this will calculate the average of the average_me for all rows between R's timestamp_col and a date 10 hours before it. You can move the window too:

SELECT  
  avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '3 hour' PRECEDING AND INTERVAL '2 hour' PRECEDING) as a 
FROM yourtable t;

This will calc, for a row R having a timestamp_col of 2000-01-01 12:00:00, the average of all rows whose timestamp_col is between 2000-01-01 9:00:00 and 2000-01-01 10:00:00

Update after my comment (untested):

SELECT x.* FROM(

 SELECT  
  CASE WHEN kind = avgpoint' THEN 
    avg(t.average_me) OVER(ORDER BY t.timestamp_col RANGE BETWEEN INTERVAL '2 hour' PRECEDING AND INTERVAL '1 hour' PRECEDING)
  END as a 
 FROM 
 (
  --your data
  SELECT 'datarow' as kind, average_me, timestamp_col 
  FROM yourtable;

  UNION ALL

  --your checkpoints, every 15 minutes from 10h ago to now
  SELECT 'avgpoint', null, g.v 
  FROM generate_series(
    now()-'10 hours'::interval, 
    now(),
    '15 minute'::interval
  ) as g(v)
 ) t
) x
WHERE x.kind = 'avgpoint'

It inserts a bunch of 15 minute intervals into the data stream, with a different kind(so it can be detected). For every 'avgpoint' kind row the AVG()OVER() looks back at the data between 2 hours and 1 hour ago and averages it. This maens that every 15 minutes you get the previous previous hour average: at noon, you get the avg from 10am to 11am. At 12:15pm you get 10:15 to 11:15 etc

What do you think about this :

with myFictiveTable as (
 select row_number() over () id ,* from (
    select generate_series(now()-'3 days'::interval,now(),'10 minutes'::interval) "timestamp"
           ,(random()*100)::int ftz_1
   ) a
  )
 select t0,t1,avg(ftz_1) from (
      select t0,COALESCE(lead(t0) over (ORDER BY t0),now()) t1 from (
    select generate_series(now()-'8 hours'::interval,now(),'2 hours'::interval) t0
     )  a
    ) mytimeLaps
   join myFictiveTable on ("timestamp">=t0 and "timestamp"<t1)
   group by t0,t1