Categories:

Aggregate functions (Percentile Estimation) , Window function syntax and usage

APPROX_PERCENTILE_ESTIMATE

Returns the desired approximated percentile value for the specified t-Digest state.

A t-Digest state produced by APPROX_PERCENTILE_ACCUMULATE and APPROX_PERCENTILE_COMBINE can be used to compute a percentile estimate using this function.

As such, APPROX_PERCENTILE_ESTIMATE(APPROX_PERCENTILE_ACCUMULATE(…)) is equivalent to APPROX_PERCENTILE(…).

See also:

APPROX_PERCENTILE , APPROX_PERCENTILE_ACCUMULATE , APPROX_PERCENTILE_COMBINE

Syntax

APPROX_PERCENTILE_ESTIMATE( <state> , <percentile> )
Copy

Arguments

state

An expression that contains state information generated by a call to APPROX_PERCENTILE_ACCUMULATE or APPROX_PERCENTILE_COMBINE.

percentile

A constant real value greater than or equal to 0.0 and less than 1.0. This indicates the percentile from 0 to 99.999… (e.g. the value 0.65 indicates the 65th percentile).

Example

Consider a scenario where you need to approximate multiple percentile values from a given set of numbers. This can be done by creating the state and then using APPROX_PERCENTILE_ESTIMATE to calculate all the percentiles:

  1. First, store the state:

    CREATE OR REPLACE TABLE resultstate AS (SELECT APPROX_PERCENTILE_ACCUMULATE(c1) s FROM testtable);
    Copy

  2. Then, query the state for multiple percentiles:

    SELECT APPROX_PERCENTILE_ESTIMATE(s , 0.01),
       APPROX_PERCENTILE_ESTIMATE(s , 0.15),
       APPROX_PERCENTILE_ESTIMATE(s , 0.845)
FROM testtable;
    Copy

For a more extensive example, see the Examples section in APPROX_PERCENTILE_ACCUMULATE.

Language: English