Untitled
unknown
pgsql
a year ago
931 B
2
Indexable
Never
-- 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;