Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
- RETURNS NUMERIC AS
- $$
- 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;
- $$
- LANGUAGE 'sql' IMMUTABLE;
- CREATE AGGREGATE median(NUMERIC) (
- SFUNC=array_append,
- STYPE=NUMERIC[],
- FINALFUNC=_final_median,
- INITCOND='{}'
- );
- SELECT
- MIN(score) AS min,
- median(result.score)::float AS median,
- --percentile_cont(0.5) WITHIN GROUP (ORDER BY score) AS median,
- MAX(score) AS max
- FROM
- result;
Add Comment
Please, Sign In to add comment