rollup()

Toolkit

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

rollup(
    sketch uddsketch
) RETURNS UddSketch

rollup(
    digest tdigest
) RETURNS tdigest

This combines multiple outputs from the percentile_agg() function, or either uddsketch() or tdigest()). This is especially useful for re-aggregation in a continuous aggregate. For example, bucketing by a larger time_bucket(), or re-grouping on other dimensions included in an aggregation.

• For more information about percentile approximation algorithms, see advanced aggregation methods.
• For more information about percentile approximation functions, see the hyperfunctions documentation.

Required arguments

Returns

Because the percentile_agg() function uses the UddSketch algorithm, rollup returns the UddSketch data structure for use in further calls.

When you use the percentile_agg or UddSketch aggregates, the rollup function does not introduce additional errors compared to calculating the estimator directly, however, using rollup with tdigest can introduce additional errors compared to calculating the estimator directly on the underlying data.

Sample usage

Re-aggregate an hourly continuous aggregate into daily buckets, the usage with uddsketch & tdigest is exactly the same:

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

SELECT
    time_bucket('1 day'::interval, bucket) as bucket,
    approx_percentile(0.95, rollup(pct_agg)) as p95,
    approx_percentile(0.99, rollup(pct_agg)) as p99
FROM foo_hourly
GROUP BY 1;