Aggregate Median

From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
Line 22: Line 22:
 
CREATE AGGREGATE median(numeric) (
 
CREATE AGGREGATE median(numeric) (
 
   SFUNC=array_append,
 
   SFUNC=array_append,
   STYPE=_numeric[],
+
   STYPE=numeric[],
 
   FINALFUNC=_final_median,
 
   FINALFUNC=_final_median,
 
   INITCOND='{}'
 
   INITCOND='{}'

Revision as of 19:51, 9 December 2009

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