IBM PureData System for Analytics, Version 7.1

Inverse distribution functions

The following are examples of inverse distribution functions. The percentile_cont and percentile_disc functions are not supported as window aggregates.

In this example, for each value of column "grp" find the salary at the 40th percentile. Use the percentile_cont function:

SELECT grp, percentile_cont(0.4) WITHIN GROUP (ORDER BY sal) AS 
fortieth FROM pctest GROUP BY grp;
 GRP | FORTIETH 
-----+----------
   1 |     3000
   2 |      980
   3 |     1300
(3 rows)

This example uses the percentile_disc function:

SELECT grp, percentile_disc(0.4) WITHIN GROUP (ORDER BY sal) AS 
fortieth FROM pctest GROUP BY grp;
 GRP | FORTIETH 
-----+----------
   1 |     3000
   2 |      950
   3 |     1250
(3 rows)

The percentile_disc function returns a value for the data set, while percentile_cont returns an interpolated value.

The following calculates the median sales for each region:

SELECT region, percentile_cont(0.5) WITHIN GROUP(ORDER BY amt) FROM 
sales_tbl GROUP BY region;
 REGION   | PERCENTILE_CONT 
-----------+-----------------
Central   |            1850 
Northeast |            2700
Northwest |            2200
Southwest |            3250
(4 rows)