Aggregate Median

From PostgreSQL wiki

Revision as of 19:51, 9 December 2009 by Mastermind (Talk | contribs)

Jump to: navigation, search

Snippets

Aggregate Median

Works with PostgreSQL

8.4

Written in

SQL

Depends on

Nothing

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

Personal tools