percentile_agg()

percentile_agg(
    value DOUBLE PRECISION
) RETURNS UddSketch

This is the default percentile aggregation function. It uses the UddSketch algorithm with 200 buckets and an initial maximum error of 0.001. This is appropriate for most common use cases of percentile approximation. For more advanced use of percentile approximation algorithms, see advanced usage. This creates a Uddsketch percentile estimator, it is usually used with the approx_percentile() accessor function to extract an approximate percentile, however it is in a form that can be re-aggregated using the rollup function and/or any of the accessor functions.

Required arguments

Returns

The percentile_agg function uses the UddSketch algorithm, so it returns the UddSketch data structure for use in further calls.

Sample usage

Get the approximate first percentile using the percentile_agg() plus the approx_percentile accessor function.

SELECT
    approx_percentile(0.01, percentile_agg(data))
FROM generate_series(0, 100) data;

approx_percentile
-------------------
             0.999

The percentile_agg function is often used to create continuous aggregates, after which you can use multiple accessors for retrospective analysis.

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;