# uddsketch() and percentile_agg() functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.## Introduction

Estimate the value at a given percentile, or the percentile rank of a given
value, using the UddSketch algorithm. This estimation is more memory- and
CPU-efficient than an exact calculation using PostgreSQL's `percentile_cont`

and
`percentile_disc`

functions.

`uddsketch`

is one of two advanced percentile approximation aggregates provided
in TimescaleDB Toolkit. It produces stable estimates within a guaranteed
relative error.

The other advanced percentile approximation aggregate is `tdigest`

,
which is more accurate at extreme quantiles, but is somewhat dependent on input
order.

If you aren't sure which aggregate to use, try the default percentile estimation
method, `percentile_agg`

. It uses the `uddsketch`

algorithm
with some sensible defaults.

For more information about percentile approximation algorithms, see the algorithms overview.

###### Related hyperfunction groups

### Aggregate

- uddsketch
- Aggregate data in a
`uddsketch`

for further calculation of percentile estimates

### Alternate aggregate

- percentile_agg
- Aggregate data in a uddsketch, using some reasonable default values, for further calculation of percentile estimates

### Accessor

- approx_percentile
- Estimate the value at a given percentile from a
`uddsketch`

- approx_percentile_array
- Estimate the values for an array of given percentiles from a
`uddsketch`

- approx_percentile_rank
- Estimate the percentile of a given value from a
`uddsketch`

- error
- Get the maximum relative error for a
`uddsketch`

- mean
- Calculate the exact mean from values in a
`uddsketch`

- num_vals
- Get the number of values contained in a
`uddsketch`

### Rollup

- rollup
- Roll up multiple
`uddsketch`

es

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.

## Required arguments

Name | Type | Description |
---|---|---|

`size` | `INTEGER` | Maximum number of buckets in the `uddsketch` . Providing a larger value here makes it more likely that the aggregate is able to maintain the desired error, but potentially increases the memory usage. |

`max_error` | `DOUBLE PRECISION` | The desired maximum relative error of the sketch. The true error may exceed this if too few buckets are provided for the data distribution. You can get the true error using the `error` function. |

`value` | `DOUBLE PRECISION` | The column to aggregate for further calculation. |

## Returns

Column | Type | Description |
---|---|---|

`uddsketch` | `UddSketch` | A percentile estimator object created to calculate percentiles using the `uddsketch` algorithm |

## 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;

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.

## Required arguments

Name | Type | Description |
---|---|---|

`value` | `DOUBLE PRECISION` | Column of values to aggregate for percentile calculation |

## Returns

Column | Type | Description |
---|---|---|

`percentile_agg` | `UddSketch` | A percentile estimator object created to calculate percentiles using the `UddSketch` algorithm |

## 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_hourlyWITH (timescaledb.continuous)AS SELECTtime_bucket('1 h'::interval, ts) as bucket,percentile_agg(value) as pct_aggFROM fooGROUP BY 1;

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

Estimate the approximate value at a percentile from a `uddsketch`

aggregate.

## Required arguments

Name | Type | Description |
---|---|---|

`percentile` | `DOUBLE PRECISION` | The percentile to compute. Must be within the range `[0.0, 1.0]` . |

`sketch` | `UddSketch` | The `uddsketch` aggregate. |

## Returns

Column | Type | Description |
---|---|---|

`approx_percentile` | `DOUBLE PRECISION` | The estimated value at the requested percentile. |

## Examples

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

SELECTapprox_percentile(0.01, uddsketch(data))FROM generate_series(0, 100) data;

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.

## Required arguments

Name | Type | Description |
---|---|---|

`percentiles` | `DOUBLE PRECISION[]` | Array of percentiles to compute. Must be within the range `[0.0, 1.0]` . |

`sketch` | `UddSketch` | The `uddsketch` aggregate. |

## Returns

Column | Type | Description |
---|---|---|

`approx_percentile_array` | `DOUBLE PRECISION[]` | The estimated values at the requested percentiles. |

## Examples

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

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

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.

## Required arguments

Name | Type | Description |
---|---|---|

`value` | `DOUBLE PRECISION` | The value to estimate the percentile of. |

`sketch` | `UddSketch` | The `uddsketch` aggregate. |

## Returns

Column | Type | Description |
---|---|---|

`approx_percentile_rank` | `DOUBLE PRECISION` | The estimated percentile associated with the provided value. |

## Examples

Estimate the percentile rank of the value `99`

, given a sample containing the numbers from 0 to 100:

SELECTapprox_percentile_rank(99, uddsketch(data))FROM generate_series(0, 100) data;

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))`

.

## Required arguments

Name | Type | Description |
---|---|---|

`sketch` | `UddSketch` | The `uddsketch` to determine the error of. |

## Returns

Column | Type | Description |
---|---|---|

`error` | `DOUBLE PRECISION` | The maximum relative error of any percentile estimate. |

## Examples

Calculate the maximum relative error when estimating percentiles using `uddsketch`

:

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

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.

## Required arguments

Name | Type | Description |
---|---|---|

`sketch` | `UddSketch` | The `uddsketch` to extract the mean from. |

## Returns

Column | Type | Description |
---|---|---|

`mean` | `DOUBLE PRECISION` | The mean of the values in the `uddsketch` . |

## Examples

Calculate the mean of the integers from 0 to 100:

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

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.

## Required arguments

Name | Type | Description |
---|---|---|

`sketch` | `UddSketch` | The `uddsketch` to extract the number of values from. |

## Returns

Column | Type | Description |
---|---|---|

`num_vals` | `DOUBLE PRECISION` | The number of values in the `uddsketch` . |

## Examples

Count the number of integers from 0 to 100:

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

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 `uddsketch`

es from 15-minute buckets into daily buckets.

## Required arguments

Name | Type | Description |
---|---|---|

`sketch` | `UddSketch` | The `uddsketch` aggregates to roll up. |

## Returns

Column | Type | Description |
---|---|---|

`rollup` | `UddSketch` | A new `uddsketch` aggregate created by combining the input `uddsketch` aggregates. |

Create an hourly continuous aggregate that contains a percentile aggregate:

CREATE MATERIALIZED VIEW foo_hourlyWITH (timescaledb.continuous)AS SELECTtime_bucket('1 h'::interval, ts) as bucket,percentile_agg(value) as pct_aggFROM fooGROUP BY 1;

You can use accessors to query directly from the continuous aggregate for hourly data. You can also roll the hourly data up into daily buckets, then calculate approximate percentiles:

SELECTtime_bucket('1 day'::interval, bucket) as bucket,approx_percentile(0.95, rollup(pct_agg)) as p95,approx_percentile(0.99, rollup(pct_agg)) as p99FROM foo_hourlyGROUP BY 1;

Create an hourly continuous aggregate that contains a percentile aggregate:

CREATE MATERIALIZED VIEW foo_hourlyWITH (timescaledb.continuous)AS SELECTtime_bucket('1 h'::interval, ts) as bucket,uddsketch(value) as uddsketchFROM fooGROUP BY 1;

You can use accessors to query directly from the continuous aggregate for hourly data. You can also roll the hourly data up into daily buckets, then calculate approximate percentiles:

SELECTtime_bucket('1 day'::interval, bucket) as bucket,approx_percentile(0.95, rollup(uddsketch)) as p95,approx_percentile(0.99, rollup(uddsketch)) as p99FROM foo_hourlyGROUP BY 1;

Keywords

Found an issue on this page?

Report an issue!