Rolling window function's alternative support

[aayush0310]

It would be nice if we can use rolling window functions on continuous aggregate.
If not rolling functions directly , there can be a workaround which can provide the similar functionality.
Just to give you more context I was trying to run a query
the query is -

create view public.test As
      -- with (timescaledb.continuous) As
       select time_bucket('3600',"timestamp") as bucket,
             partition_id,
             avg(hr) as avg_hr,
             avg(avg_rest_hr) as avg_rest
             from (
                   select "timestamp" ,
                   partition_id ,
                   hr,
                   avg(hr) over(partition by partition_id rows between 6 preceding and current row) as avg_rest_hr
                   from vitals_measurement_data vmd
             ) d
       group by bucket , partition_id

this works fine if I don't use continuous aggregates,
but as soon as I uncomment timescaledb.continuous , the query fails.

Some additional context or reference
@davidkohn88 mentioned in the slack community
to get a true average with that you'll have to use sum() and count() in order to do that properly.

[davidkohn88]

Hey @aayush0310

We're beginning some work on this, you can see some of our thoughts here: https://github.com/timescale/timescale-analytics/blob/stats-agg/docs/rolling_average_api_working.md

I'm not 100% sure I understand what you're trying to accomplish with that query, can you explain exactly what the data looks like and what you're trying to accomplish? is the data already an hourly aggregate and you're going for the 6 hours preceding as a rolling number?

[JLockerman]

we added stats agg to solve this. It wants some more testing esp fuzzing against postgres's stats functions before stabilization.