rollup()

Toolkit

TimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale Cloud. Click to learn more.

rollup(
    ss StatsSummary1D
) RETURNS StatsSummary1D

rollup(
    ss StatsSummary2D
) RETURNS StatsSummary2D

This combines multiple outputs from the stats_agg() function function, it works with both one and two dimensional statistical aggregates. This is especially useful for re-aggregation in a continuous aggregate. For example, bucketing by a largertime_bucket(), or re-grouping on other dimensions included in an aggregation.

For use in window function see the rolling. rollup works in window function contexts, but rolling can be more efficient.

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

Required arguments

Returns

Sample usage

Re-aggregate an hourly continuous aggregate into daily buckets, 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
    time_bucket('1 day'::interval, bucket) as bucket,
    average(rollup(stats)),
    stddev(rollup(stats))
FROM foo_hourly
GROUP BY 1;