Q: Creating average for specific timeframe I'm setting up a time series with each row = 1 hr.
The input data has sometimes multiple values per hour. This can vary.
Right now the specific code looks like this:
select
patientunitstayid
, generate_series(ceil(min(nursingchartoffset)/60.0), 
ceil(max(nursingchartoffset)/60.0)) as hr
, avg(case when nibp_systolic >= 1 and nibp_systolic <= 250 then 
nibp_systolic else null end) as nibp_systolic_avg
from nc            
group by patientunitstayid
order by patientunitstayid asc;

and generates this data:

It takes the average of the entire time series for each patient instead of taking it for each hour. How can I fix this?

A: I'm expecting something like this:
select nc.patientunitstayid, gs.hr, 
       avg(case when nc.nibp_systolic >= 1 and nc.nibp_systolic <= 250 
                then nibp_systolic 
           end) as nibp_systolic_avg
from (select nc.*,
             min(nursingchartoffset) over (partition by patientunitstayid) as min_nursingchartoffset,
             max(nursingchartoffset) over (partition by patientunitstayid) as max_nursingchartoffset
      from nc
     ) nc cross join lateral
     generate_series(ceil(min_nursingchartoffset/60.0), 
                     ceil(max_nursingchartoffset/60.0)
                    ) as gs(hr)
group by nc.patientunitstayid, hr
order by nc.patientunitstayid asc, hr asc;

That is, you need to be aggregating by hr.  I put this into the from clause, to highlight that this generates rows.  If you are using an older version of Postgres, then you might not have lateral joins.  If so, just use a subquery in the from clause.
EDIT:
You can also try:
from (select nc.*,
             generate_series(ceil(min(nursingchartoffset) over (partition by patientunitstayid) / 60.0), 
                             ceil(max(nursingchartoffset) over (partition by patientunitstayid)/ 60.0)
                            ) hr
      from nc
     ) nc

And adjust the references to hr in the outer query.
