Skip to content

Instantly share code, notes, and snippets.

@bassemawhoob
Last active May 1, 2023 08:42
Show Gist options
  • Save bassemawhoob/ed71e00e77f75a3240aaa7bdc45d966a to your computer and use it in GitHub Desktop.
Save bassemawhoob/ed71e00e77f75a3240aaa7bdc45d966a to your computer and use it in GitHub Desktop.
PostgreSQL Median Function
-- Source: https://wiki.postgresql.org/wiki/Aggregate_Median
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='{}'
);
-- Usage:
SELECT median(subtotal_cents) AS median_value FROM orders;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment