s53cja
Last Updated: February 25, 2016
·
7.457K
· gefei

Calculating Median in PostgreSQL

Media (not AVG) is not directly supported in PostgreSQL, but, fortunately, can be easily calculated using the following:

SELECT CASE WHEN c % 2 = 0 AND c > 1 THEN (a[1]+a[2])/2 ELSE a[1] END
FROM
(
 SELECT ARRAY(SELECT my_col FROM my_table ORDER BY my_cal OFFSET (c-1)/2 LIMIT 2) AS a, c
   FROM (SELECT count(*) AS c FROM my_table where my_col is not null) AS count
 OFFSET 0
)
AS midrows;

Based on a nice tip found on http://okbob.blogspot.de/2009/11/aggregate-function-median-in-postgresql.html

In the following is another idea, found on http://blog.carbonfive.com/2013/01/17/postgresql-aggregates-medians-and-a-brief-command-reference/, which uses the ntile function to calculate a approximate median. it works fine if the number of rows is uneven, but not if the number of rows is even

SELECT
  MAX(column) as "Median of Column"
FROM
  (
    SELECT
      column,
      ntile(2) OVER (ORDER BY column) AS bucket
    FROM
      table
  ) as t
WHERE bucket = 1
GROUP BY bucket;