Skip to content

Instantly share code, notes, and snippets.

@righi
Created July 20, 2014 02:27
Show Gist options
  • Save righi/799c27f50fc41a2af80f to your computer and use it in GitHub Desktop.
Save righi/799c27f50fc41a2af80f to your computer and use it in GitHub Desktop.
Postgres Aggregate Median
-- Source: https://wiki.postgresql.org/wiki/Aggregate_Median
CREATE FUNCTION _final_median(anyarray) RETURNS float8 AS $$
WITH q AS
(
SELECT val
FROM unnest($1) val
WHERE VAL IS NOT NULL
ORDER BY 1
),
cnt AS
(
SELECT COUNT(*) AS c FROM q
)
SELECT AVG(val)::float8
FROM
(
SELECT val FROM q
LIMIT 2 - MOD((SELECT c FROM cnt), 2)
OFFSET GREATEST(CEIL((SELECT c FROM cnt) / 2.0) - 1,0)
) q2;
$$ LANGUAGE sql IMMUTABLE;
CREATE AGGREGATE median(anyelement) (
SFUNC=array_append,
STYPE=anyarray,
FINALFUNC=_final_median,
INITCOND='{}'
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment