24 lines
516 B
MySQL
24 lines
516 B
MySQL
|
-- 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='{}'
|
||
|
|
);
|