Skip to content

Instantly share code, notes, and snippets.

@glamp
Last active December 16, 2015 01:59
Show Gist options
  • Save glamp/5359244 to your computer and use it in GitHub Desktop.
Save glamp/5359244 to your computer and use it in GitHub Desktop.
--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