Is There A Better Way To Calculate The Median (not Average)

June 06, 2024 Post a Comment

Suppose I have the following table definition: CREATE TABLE x (i serial primary key, value integer not null); I want to calculate the MEDIAN of value (not the AVG). The median is

Solution 1:

Yes, with PostgreSQL 9.4, you can use the newly introduced inverse distribution function PERCENTILE_CONT(), an ordered-set aggregate function that is specified in the SQL standard as well.

WITH t(value) AS (
  SELECT1UNIONALLSELECT2UNIONALLSELECT100 
)
SELECTpercentile_cont(0.5) WITHINGROUP (ORDERBYvalue)
FROM
  t;

This emulation of MEDIAN() via PERCENTILE_CONT() is also documented here.

Solution 2:

Indeed there IS an easier way. In Postgres you can define your own aggregate functions. I posted functions to do median as well as mode and range to the PostgreSQL snippets library a while back.

http://wiki.postgresql.org/wiki/Aggregate_Median

Solution 3:

A simpler query for that:

WITH y AS (
   SELECT value, row_number() OVER (ORDERBY value) AS rn
   FROM   x
   WHERE  value ISNOT NULL
   )
, c AS (SELECT count(*) AS ct FROM y) 
SELECTCASEWHEN c.ct%2 = 0THEN
          round((SELECT avg(value) FROM y WHERE y.rn IN (c.ct/2, c.ct/2+1)), 3)
       ELSE
                (SELECT     value  FROM y WHERE y.rn = (c.ct+1)/2)
       ENDAS median
FROM   c;

Major points

Ignores NULL values.
Core feature is the row_number() window function, which has been there since version 8.4
The final SELECT gets one row for uneven numbers and avg() of two rows for even numbers. Result is numeric, rounded to 3 decimal places.

Test shows, that the new version is 4x faster than (and yields correct results, unlike) the query in the question:

CREATE TEMP TABLE x (valueint);
INSERTINTO x SELECT generate_series(1,10000);
INSERTINTO x VALUES (NULL),(NULL),(NULL),(3);

Solution 4:

For googlers: there is also http://pgxn.org/dist/quantile Median can be calculated in one line after installation of this extension.

Solution 5:

Simple sql with native postgres functions only:

selectcasecount(*)%2when1then (array_agg(num orderby num))[count(*)/2+1]
        else ((array_agg(num orderby num))[count(*)/2]::double precision+ (array_agg(num orderby num))[count(*)/2+1])/2endas median
fromunnest(array[5,17,83,27,28]) num;

Sure you can add coalesce() or something if you want to handle nulls.

Lab Sql