Quantile

Warning

This is an old implementation of quantile. Replacement of this function is available as built-in PERCENTILE_CONT in databases

Contents

• Function Syntax
• Examples
• Related Topics

This module computes the specified quantile value. It reads the name of the table, the specific column, and computes the quantile value based on the fraction specified as the third argument.

For an implementation of quantile using sketches, check out the cmsketch_centile() aggregate in the CountMin (Cormode-Muthukrishnan) module.

Function Syntax

There are two implementations of quantile available depending on the size of the table.

quantile() is best used for small tables (i.e., less than 5000 rows, with 1-2 columns in total).

quantile( table_name,
          col_name,
          quantile
        )

For larger tables, consider using quantile_big() instead.

quantile_big( table_name,
              col_name,
              quantile
            )

Examples

1. Prepare some input.

   CREATE TABLE tab1 AS SELECT generate_series(1, 1000) AS col1;
   

2. Run the quantile() function.

   SELECT quantile( 'tab1',
                    'col1',
                    .3
                  );
   

   Result:

      quantile
    -------------
    301.48046875
   (1 row)
   

Note that this module is not available in HAWQ. GPDB 4.2+ and HAWQ 1.2+ support the SQL Standard percentile_cont() inverse distribution function which should be used preferentially to this implementation. It is also planned for support in Postgres 9.4. This implementation will be retired once the functionality is available in Postgres.

Related Topics

File quantile.sql_in documenting the SQL function.

Module CountMin (Cormode-Muthukrishnan) for an approximate quantile implementation.