Examples of ordered set aggregates in Postgres.
SELECT round(avg(pie)::numeric, 2),
percentile_cont(array[0.25, 0.5, 0.75, 0.95]) WITHIN GROUP (ORDER BY pie) AS percentiles
FROM player_stats_advanced
WHERE permode = 'pergame';
| round |
percentiles |
| 0.08 |
{0.061,0.084,0.107,0.145} |
SELECT season,
round(avg(pie)::numeric, 2),
percentile_cont(array[0.25, 0.5, 0.75, 0.95]) WITHIN GROUP (ORDER BY pie) AS percentiles
FROM player_stats_advanced
WHERE permode = 'pergame'
GROUP BY 1;
| season |
round |
percentiles |
| 1996-97 |
0.08 |
{0.06,0.083,0.106,0.146} |
| 1997-98 |
0.08 |
{0.0595,0.084,0.108,0.1441} |
| 1998-99 |
0.08 |
{0.05275,0.085,0.109,0.156} |
| 1999-00 |
0.08 |
{0.061,0.084,0.1065,0.1431} |
| 2000-01 |
0.08 |
{0.059,0.081,0.105,0.148} |
| 2001-02 |
0.08 |
{0.06275,0.085,0.104,0.1401} |
| 2002-03 |
0.08 |
{0.06075,0.084,0.106,0.14365} |
| 2003-04 |
0.08 |
{0.061,0.085,0.10675,0.137} |
| 2004-05 |
0.08 |
{0.058,0.084,0.105,0.14585} |
| 2005-06 |
0.08 |
{0.059,0.081,0.103,0.152} |
| 2006-07 |
0.08 |
{0.05925,0.0835,0.103,0.145} |
| 2007-08 |
0.08 |
{0.06,0.083,0.1055,0.1465} |
| 2008-09 |
0.09 |
{0.063,0.085,0.109,0.1468} |
| 2009-10 |
0.09 |
{0.065,0.086,0.105,0.147} |
| 2010-11 |
0.08 |
{0.063,0.084,0.107,0.1415} |
| 2011-12 |
0.08 |
{0.063,0.087,0.108,0.1452} |
| 2012-13 |
0.08 |
{0.062,0.085,0.108,0.14265} |
| 2013-14 |
0.08 |
{0.063,0.086,0.105,0.141} |
| 2014-15 |
0.08 |
{0.064,0.087,0.1095,0.1455} |
| 2015-16 |
0.09 |
{0.068,0.087,0.109,0.1434} |
