Untitled

mail@pastecode.io avatar
unknown
pgsql
a year ago
931 B
3
Indexable
-- 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;