PostgreSQL 9.4 has an amazing feature hidden underneath a complicated syntax. Here's a neat way to use it.
Expectation shattered:
SELECT median(i) FROM generate_series(1,10) AS s(i);
ERROR: function median(integer) does not exist
LINE 1: SELECT median(i) FROM generate_series(1,10) AS s(i);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
Hope glimmers:
SELECT percentile_cont(0.5) WITHIN GROUP(ORDER BY i) FROM generate_series(1,10) AS s(i);
percentile_cont
-----------------
5.5
(1 row)
Carefully perusing the docs shows that WITHIN GROUP allows returning arrays, i.e. you can get all those quantiles at once, but yet again:
SELECT percentile_cont([0.25,0.5,0.75]) WITHIN GROUP(ORDER BY i) AS median FROM generate_series(1,11) AS s(i);
ERROR: syntax error at or near "["
LINE 1: SELECT percentile_cont([0.25,0.5,0.75]) WITHIN GROUP(ORDER B...
^
This is ludicrous. I clearly meant to put an array of doubles there, but PostgreSQL just can't see it! I guess a little hackage is needed.
SELECT percentile_cont(array(SELECT j/10.0 FROM generate_series(1,10) j)) WITHIN GROUP(ORDER BY i) AS median FROM generate_series(1,11) AS s(i);
median
-------------------------
{2,3,4,5,6,7,8,9,10,11}
(1 row)
Yay!
