-- POSTGRESQL
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 MAQUINA,
COUNT(QTD) AS "QUANTIDADE",
SUM(QTD) AS "TOTAL",
MIN(QTD) AS "MINIMO",
AVG(QTD) AS "MEDIA",
MAX(QTD) AS "MAXIMO",
MAX(QTD) - MIN(QTD) AS "AMPLIT. TOTAL",
VAR_POP(QTD) AS "VARIANCIA",
STDDEV_POP(QTD) AS "DESVIO PADRÃO",
MEDIAN(QTD) AS "MEDIANA",
(STDDEV_POP(QTD) / AVG(QTD)) * 100 AS "COEFICIENTE VARIACAO",
MODE() WITHIN GROUP (ORDER BY QTD) AS "MODA"
FROM MAQUINA
GROUP BY MAQUINA
ORDER BY MARQUINA;