rollup()

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

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

Returns

ColumnTypeDescription
rollupStatsSummary1D/StatsSummary2DA StatsSummary object which may be passed to further APIs

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;

Found an issue on this page?

Report an issue!

Related Content