Extensiones de SQL: Agregados

Los agregados en Postgres están expresados en términos de funciones de transición de estado. Es decir, un agregado puede estar definido en términos de un estado que es modificado cuando una instancia es procesada. Algunas funciones de estado miran un valor particular en la instancia cuando calculan el nuevo estado (sfunc1 en la sintaxis de create aggregate ) mientras que otras sólo se preocupan de su estado interno (sfunc2). Si definimos un agregado que utiliza solamente sfunc1, definimos un agregado que computa una función de los atributos de cada instancia. "Sum" es un ejemplo de este tipo de agregado. "Sum" comienza en cero y siempre añade el valor de la instancia actual a su total. Utilizaremos int4pl que está integrado en Postgres para realizar esta adición.

CREATE AGGREGATE complex_sum (
    sfunc1 = complex_add,
    basetype = complex,
    stype1 = complex,
    initcond1 = '(0,0)'
);

SELECT complex_sum(a) FROM test_complex;

         +------------+
         |complex_sum |
         +------------+
         |(34,53.9)   |
         +------------+

Si solamente definimos sfunc2, estamos especificando un agregado que computa una funcion que es independiente de los atributos de cada instancia. "Count" es el ejemplo más común de este tipo de agregado. . "Count" comienza a cero y añade uno a su total para cada instancia, ignorando el valor de instancia. Aquí, utilizamos la rutina integrada int4inc para hacer el trabajo por nosotros. Esta rutina incrementa (añade uno) su argumento.

CREATE AGGREGATE my_count (
    sfunc2 = int4inc, -- add one
    basetype = int4,
    stype2 = int4,
    initcond2 = '0'
);

SELECT my_count(*) as emp_count from EMP;

         +----------+
         |emp_count |
         +----------+
         |5         |
         +----------+

"Average" es un ejemplo de un agregado que requiere tanto una función para calcular la suma actual y una función para calcular el contador actual. Cuando todas las instancias han sido procesadas, la respuesta final para el agregado es la suma actual dividida por el contador actual. Utilizamos las rutinas int4pl y int4inc que utilizamos anteriormente así como también la rutina de división entera de Postgres , int4div, para calcular la división de la suma por el contador.

CREATE AGGREGATE my_average (
    sfunc1 = int4pl,     --  sum
    basetype = int4,
    stype1 = int4,
    sfunc2 = int4inc,    -- count
    stype2 = int4,
    finalfunc = int4div, -- division
    initcond1 = '0',
    initcond2 = '0'
);

SELECT my_average(salary) as emp_average FROM EMP;

         +------------+
         |emp_average |
         +------------+
         |1640        |
         +------------+