Aggregate Median
From PostgreSQL wiki
(Difference between revisions)
Mastermind (Talk | contribs) |
|||
| Line 22: | Line 22: | ||
CREATE AGGREGATE median(numeric) ( | CREATE AGGREGATE median(numeric) ( | ||
SFUNC=array_append, | SFUNC=array_append, | ||
| − | STYPE= | + | STYPE=numeric[], |
FINALFUNC=_final_median, | FINALFUNC=_final_median, | ||
INITCOND='{}' | INITCOND='{}' | ||
Revision as of 19:51, 9 December 2009
by Scott Bailey 'Artacus'
Unlike mode() and range(), this one will only work with PostgreSQL 8.4 and higher due to the dynamic limit and offset.
CREATE OR REPLACE FUNCTION _final_median(numeric[]) RETURNS numeric AS $$ SELECT AVG(val) FROM ( SELECT val FROM unnest($1) val ORDER BY 1 LIMIT 2 - MOD(array_upper($1, 1), 2) OFFSET CEIL(array_upper($1, 1) / 2.0) - 1 ) sub; $$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE median(numeric) ( SFUNC=array_append, STYPE=numeric[], FINALFUNC=_final_median, INITCOND='{}' );
See Also
Aggregate Mode Aggregate Median
