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} |