rolling()

rolling(
    ss StatsSummary1D
) RETURNS StatsSummary1D
rolling(
    ss StatsSummary2D
) RETURNS StatsSummary2D

This combines multiple outputs from the stats_agg() function function, it works with both one and two dimensional statistical aggregates. It is optimized for use in a window function context for computing tumbling window statistical aggregates.

This is especially useful for computing tumbling window aggregates from a continuous aggregate. It uses inverse transition and combine functions to do more efficient windowed aggregates, with the possibility that more floating point errors can occur in unusual scenarios.

It also works for re-aggregation in a non-window context, but the rollup function is more clear. The rollup function also work for windowed aggregates, less efficiently but without the risk of extra floating point error.

For more information about statistical aggregation functions, see the hyperfunctions documentation.

Required arguments

NameTypeDescription
stats_aggStatsSummary1D / StatsSummary2DThe already constructed data structure from a previous stats_agg call

Returns

ColumnTypeDescription
rollingStatsSummary1D/StatsSummary2DA StatsSummary object that can be passed to further APIs

Sample usage

Create a tumbling window daily aggregate from an hourly continuous aggregate, then use accessors:

CREATE MATERIALIZED VIEW foo_hourly
WITH (timescaledb.continuous)
AS SELECT
    time_bucket('1 h'::interval, ts) as bucket,
    stats_agg(value) as stats
FROM foo
GROUP BY 1;

SELECT
    bucket,
    average(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING)),
    stddev(rolling(stats) OVER (ORDER BY bucket RANGE '1 day' PRECEDING))
FROM foo_hourly;

Found an issue on this page?

Report an issue!

Related Content