uddsketch() and percentile_agg() functions

uddsketch(
    size INTEGER,
    max_error DOUBLE PRECISION,
    value DOUBLE PRECISION
) RETURNS UddSketch

This is the first step for calculating approximate percentiles with the uddsketch algorithm. Use uddsketch to create an intermediate aggregate from your raw data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

If you aren't sure what values to set for size and max_error, try using the alternate aggregate function, percentile_agg(). percentile_agg also creates a UddSketch, but it sets some sensible default values for size and max_error that should work for many use cases.

Examples

Given a table called samples, with a column called data, build a uddsketch using the data column. Use a maximum of 100 buckets and a relative error of 0.01:

SELECT uddsketch(100, 0.01, data) FROM samples;

Copy

percentile_agg(
  value DOUBLE PRECISION
) RETURNS UddSketch

This is an alternate first step for calculating approximate percentiles. It provides some added convenience by using some sensible defaults to create a UddSketch. Internally, it calls uddsketch with 200 buckets and a maximum error rate of 0.001.

Use percentile_agg to create an intermediate aggregate from your raw data. This intermediate form can then be used by one or more accessors in this group to compute final results.

Optionally, multiple such intermediate aggregate objects can be combined using rollup() before an accessor is applied.

Examples

Create a continuous aggregate that stores percentile aggregate objects. These objects can later be used with 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;

Copy

approx_percentile(
  percentile DOUBLE PRECISION,
  uddsketch  UddSketch
) RETURNS DOUBLE PRECISION

Estimate the approximate value at a percentile from a uddsketch aggregate.

Examples

Estimate the value at the first percentile, given a sample containing the numbers from 0 to 100:

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

Copy

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

approx_percentile_array(
  percentiles DOUBLE PRECISION[],
  uddsketch  UddSketch
) RETURNS DOUBLE PRECISION[]

Estimate the approximate values of an array of percentiles from a uddsketch aggregate.

Examples

Estimate the value at the 90th, 50th, and 20th percentiles, given a sample containing the numbers from 0 to 100:

SELECT
  approx_percentile_array(array[0.9,0.5,0.2], uddsketch(100,0.005,data))
FROM generate_series(0, 100) data;

Copy

approx_percentile_array
-------------------
 {90.0,50.0,20.0}

approx_percentile_rank(
  value DOUBLE PRECISION,
  sketch UddSketch
) RETURNS DOUBLE PRECISION

Estimate the percentile at which a given value would be located.

Examples

Estimate the percentile rank of the value 99, given a sample containing the numbers from 0 to 100:

SELECT
  approx_percentile_rank(99, uddsketch(data))
FROM generate_series(0, 100) data;

Copy

approx_percentile_rank
----------------------------
        0.9851485148514851

error(
  sketch UddSketch
) RETURNS DOUBLE PRECISION

Get the maximum relative error of a uddsketch. The correct (non-estimated) percentile falls within the range defined by approx_percentile(sketch) +/- (approx_percentile(sketch) * error(sketch)).

Examples

Calculate the maximum relative error when estimating percentiles using uddsketch:

SELECT error(uddsketch(data))
FROM generate_series(0, 100) data;

Copy

error
-------
0.001

mean(
  sketch UddSketch
) RETURNS DOUBLE PRECISION

Calculate the exact mean of the values in a uddsketch. Unlike percentile calculations, the mean calculation is exact. This accessor allows you to calculate the mean alongside percentiles, without needing to create two separate aggregates from the same raw data.

Examples

Calculate the mean of the integers from 0 to 100:

SELECT mean(uddsketch(data))
FROM generate_series(0, 100) data;

Copy

mean
------
50

num_vals(
  sketch UddSketch
) RETURNS DOUBLE PRECISION

Get the number of values contained in a uddsketch. This accessor allows you to calculate a count alongside percentiles, without needing to create two separate aggregates from the same raw data.

Examples

Count the number of integers from 0 to 100:

SELECT num_vals(uddsketch(data))
FROM generate_series(0, 100) data;

Copy

num_vals
-----------
    101

rollup(
  sketch UddSketch
) RETURNS UddSketch

Combine multiple intermediate uddsketch aggregates, produced by uddsketch, into a single intermediate uddsketch aggregate. For example, you can use rollup to combine uddsketches from 15-minute buckets into daily buckets.