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.
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 )
CREATE TABLE tab1 AS SELECT generate_series(1, 1000) AS col1;
SELECT quantile( 'tab1', 'col1', .3 );Result:
quantile ------------- 301.48046875 (1 row)
Module CountMin (Cormode-Muthukrishnan) for an approximate quantile implementation.