Quantile

Collaboration diagram for Quantile:

Warning

This MADlib method is still in early stage development. There may be some issues that will be addressed in a future version. Interface and implementation is subject to change.

About:

This function 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.

Implementation Notes:

There are two implementations of quantile available depending on the size of the table. quantile is best used for small tables (e.g. less than 5000 rows, with 1-2 columns in total). For larger tables, consider using quantile_big instead.

Usage:

SELECT * FROM quantile( 'table_name', 'col_name', quantile);

SELECT * FROM quantile_big( 'table_name', 'col_name', quantile);

Examples:

1. Prepare some input:

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

2. Run the quantile() function:
   

   sql> SELECT quantile( 'tab1', 'col1', .3);
   
      quantile   
   --------------
    301.48046875
   (1 row)
   

See Also

File quantile.sql_in documenting the SQL function.

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