07.-Funciones agregadas

Como la mayoría de los otros productos de base de datos relacionales, PostgreSQL admite funciones agregadas. Una función agregada calcula un solo resultado de múltiples filas de entrada. Por ejemplo, hay agregados para calcular la count, sum, avg(promedio), max(máximo) y min(mínimo) sobre un conjunto de filas.

Como ejemplo, podemos encontrar la lectura más alta a bajo temperatura en cualquier lugar con:

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

Si queríamos saber en qué ciudad (o ciudades) se produjo esa lectura, podríamos intentar:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     Erróneo

pero esto no funcionará desde el agregado maxno se puede utilizar en el WHEREcláusula. (Esta restricción existe porque el WHEREla cláusula determina qué filas se incluirán en el cálculo agregado; por lo que obviamente debe ser evaluada antes de que se computen las funciones agregadas.) Sin embargo, como suele ocurrir, la consulta se puede reafirmar para lograr el resultado deseado, aquí mediante una subcoquería:

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
 San Francisco
(1 row)

Esto está bien porque la subcoquería es un cálculo independiente que calcula su propio agregado por separado de lo que está sucediendo en la consulta exterior.

Los agregados también son muy útiles en combinación con GROUP BYcláusulas. Por ejemplo, podemos obtener el número de lecturas y la temperatura máxima baja observada en cada ciudad con:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     2 |  46
(2 rows)

lo que nos da una fila de salida por ciudad. Cada resultado agregado se calcula sobre las filas de la mesa que coinciden con esa ciudad. Podemos filtrar estas filas agrupadas usando HAVING:

SELECT city, count(*), max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | count | max
---------+-------+-----
 Hayward |     1 |  37
(1 row)

que nos da los mismos resultados sólo para las ciudades que tienen todas temp_lovalores inferiores a 40. Por último, si sólo nos importan las ciudades cuyos nombres comienzan conS- Podríamos hacerlo:

SELECT city, count(*), max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'            -- (1)
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 San Francisco |     2 |  46
(1 row)

El operador LIKE trabaja con patrones y se explicará más adelante.

Es importante comprender la interacción entre agregados y SQLde WHEREy HAVINGcláusulas. La diferencia fundamental entre WHEREy HAVINGEsto es: WHEREselecciona filas de entrada antes de que se computen los grupos y agregados (por lo tanto, controla qué filas van en el cómputo agregado), mientras que HAVINGselecciona filas de grupo después de grupos y agregados se calculan. Así, el WHEREla cláusula no debe contener funciones agregadas; no tiene sentido tratar de utilizar un agregado para determinar qué filas serán entradas a los agregados. Por otro lado, el HAVINGcláusula siempre contiene funciones agregadas. (Estrictamente hablando, se te permite escribir un HAVINGcláusula que no usa agregados, pero rara vez es útil. La misma condición podría ser utilizada de manera más eficiente en el WHEREetapa.)

En el ejemplo anterior, podemos aplicar la restricción del nombre de la ciudad en WHERE, ya que no necesita ningún agregado. Esto es más eficiente que añadir la restricción a HAVING, porque evitamos hacer la agrupación y los cálculos agregados para todas las filas que fallan el WHEREcompruebas.

Otra forma de seleccionar las filas que entran en un cómputo agregado es usar FILTER, que es una opción por agregado:

SELECT city, count(*) FILTER (WHERE temp_lo < 45), max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | count | max
---------------+-------+-----
 Hayward       |     1 |  37
 San Francisco |     1 |  46
(2 rows)

FILTERes como WHERE, excepto que elimina las filas sólo de la entrada de la función agregada particular a la que se une. Aquí, el countagregados sólo cuenta filas con temp_lodebajo de 45; pero el maxagregados se sigue aplicando a todas las filas, por lo que todavía encuentra la lectura de 46.