Skip to content

Instantly share code, notes, and snippets.

@glamp glamp/pg_median.sql
Last active Dec 16, 2015

Embed
What would you like to do?
--median (http://wiki.postgresql.org/wiki/Aggregate_Median)
CREATE OR REPLACE 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='{}'
);
--median and avg scores of NCAA football games
SELECT
median(score)
,avg(score)
FROM
game_results;
-- median | avg
----------+---------------------
-- 27 | 28.6536160957041871
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.