Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Examples of ordered set aggregates in Postgres

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}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.