24 lines
516 B
SQL
24 lines
516 B
SQL
-- from here: https://wiki.postgresql.org/wiki/Aggregate_Median
|
|
CREATE OR REPLACE FUNCTION _final_median(numeric[])
|
|
RETURNS numeric
|
|
AS
|
|
$body$
|
|
SELECT AVG(val)
|
|
FROM (
|
|
SELECT val
|
|
FROM unnest($1) val
|
|
ORDER BY 1
|
|
LIMIT 2 - MOD(array_upper($1, 1), 2)
|
|
OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
|
|
) sub;
|
|
$body$
|
|
LANGUAGE sql ;
|
|
-- IMMUTABLE not accepted by pg migrate
|
|
|
|
CREATE AGGREGATE median(numeric) (
|
|
SFUNC=array_append,
|
|
STYPE=numeric[],
|
|
FINALFUNC=_final_median,
|
|
INITCOND='{}'
|
|
);
|