### Aggregate function MEDIAN in PostgreSQL

Searching and calculating Median in databases was terrible. Still median isn't ANSI SQL aggregate function. There are two commons method how to calculate median of some column. First - very old, and very slow based on self join alchemy, second - new - based on analytic function. Now, I will be test some newer methods on one million rows large table:

postgres=# create table milrows(a real); CREATE TABLE Time: 7,975 ms postgres=# insert into milrows select random()*1000000 from generate_series(1,1000000); INSERT 0 1000000 Time: 6863,575 mssimple scan on this table takes 200ms

Frompostgres=# select avg(a) from milrows ; avg ────────────────── 499515.883033113 (1 row) Time: 200,176 msIn 8.4 we can use analytic functions. These functions uses TupleStore - internal store feature - it allows work with very large tables - limit is free space on disc.

## Analytic methods

--Joe Celko's method postgres=# SELECT avg(a)::float FROM (SELECT a, row_number() OVER (ORDER BY a asc) AS hi, count(*) OVER () + 1 - row_number() OVER (ORDER BY a) AS lo FROM milrows) qs WHERE hi IN (lo-1,lo,lo+1); avg ─────────────── 499188.546875 (1 row) Time: 4922,678 ms -- Andrew Gierth's method postgres=# select avg(a) from ( select a, row_number() over (order by a),count(*) over () from milrows ) s where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1) ; avg ─────────────── 499188.546875 (1 row) Time: 5021,001 ms -- modified Andrew's method (count(*) over () is slow) postgres=# select avg(a) from ( select a, row_number() over (order by a),(select count(*) from milrows) as count from milrows ) s where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1) ; avg ─────────────── 499188.546875 (1 row) Time: 3931,922 ms

## Array based methods

Next methods are based on using an arrays. These methods are fast, but limit for this methods is size of operation memory. For very very large tables could to take all application memory.--Regina's method -- it's not 100% correct http://www.postgresonline.com/journal/index.php?/archives/67-Build-Median-Aggregate-Function-in-SQL.html#extended CREATE OR REPLACE FUNCTION array_median(double precision[]) RETURNS double precision AS $$ SELECT CASE WHEN array_upper($1,1) = 0 THEN null ELSE asorted[ceiling(array_upper(asorted,1)/2.0)]::double precision END FROM (SELECT ARRAY(SELECT $1[n] FROM generate_series(1, array_upper($1, 1)) AS n WHERE $1[n] IS NOT NULL ORDER BY $1[n]) As asorted) As foo $$ LANGUAGE 'sql' IMMUTABLE; CREATE AGGREGATE median(double precision) ( SFUNC=array_append, STYPE=double precision[], FINALFUNC=array_median ); postgres=# select median(a) from milrows ; ^CCancel request sent ERROR: canceling statement due to user request -- killed 5 minutes !don't use array_append for bigger arrays (length > 10000) postgres=# --My method postgres=# create or replace function median(anyarray) returns double precision as $$ select ($1[array_upper($1,1)/2+1]::double precision + $1[(array_upper($1,1)+1) / 2]::double precision) / 2.0; $$ language sql immutable strict; CREATE FUNCTION Time: 1,557 ms Time: 2574,677 ms postgres=# select median(array(select a from milrows where a is not null order by a)); median ─────────────── 499188.546875 (1 row) Time: 2555,342 msThis week I added support for median aggregate to orafce package. You can download it from url http://pgfoundry.org/frs/download.php/2472/orafce-3.0.2-devel.tar.gz . Function median use some fetures 8.4 and needs 8.4 - it isn't supported on PostgreSQL 8.3 and older.

-- orafce 3.0.2 median (needs PostgreSQL 8.4 and higher) postgres=# select median(a::float8) from milrows; median ─────────────── 499188.546875 (1 row) Time: 687,577 msIt's very fast - if your table has about one million rows (1000000) you can use it (for this table size takes max. 15MB RAM (for one column)).