2.7. Агрегатные функции

Как большинство других серверов реляционных баз данных, PostgreSQL поддерживает агрегатные функции. Агрегатная функция вычисляет единственное значение, обрабатывая множество строк. Например, есть агрегатные функции, вычисляющие: count (количество), sum (сумму), avg (среднее), max (максимум) и min (минимум) для набора строк.

К примеру, мы можем найти самую высокую температуру из всех наблюдений:

SELECT max(temp_lo) FROM weather;

 max
-----
  46
(1 row)

Если мы хотим узнать, в каком городе (или городах) наблюдалась эта температура, можно попробовать:

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     НЕВЕРНО

но это не будет работать, так как агрегатную функцию max нельзя использовать в предложении WHERE. (Это ограничение объясняется тем, что предложение WHERE должно определить, для каких строк вычислять агрегатную функцию, так что оно, очевидно, должно вычисляться до агрегатных функций.) Однако, как часто бывает, запрос можно перезапустить и получить желаемый результат, применив подзапрос:

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

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

Теперь всё в порядке — подзапрос выполняется отдельно и результат агрегатной функции вычисляется вне зависимости от того, что происходит во внешнем запросе.

Агрегатные функции также очень полезны в сочетании с предложением GROUP BY. Например, мы можем получить максимум минимальной дневной температуры в разрезе городов:

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

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

Здесь мы получаем по одной строке для каждого города. Каждый агрегатный результат вычисляется по строкам таблицы, соответствующим отдельному городу. Мы можем отфильтровать сгруппированные строки с помощью предложения HAVING:

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

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

Мы получаем те же результаты, но только для тех городов, где все значения temp_lo меньше 40. Наконец, если нас интересуют только города, названия которых начинаются с "S", мы можем сделать:

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'(1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

Важно понимать, как соотносятся агрегатные функции и SQL-предложения WHERE и HAVING. Основное отличие WHERE от HAVING заключается в том, что WHERE сначала выбирает строки, а затем группирует их и вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как HAVING отбирает строки групп после группировки и вычисления агрегатных функций. Как следствие, предложение WHERE не должно содержать агрегатных функций; не имеет смысла использовать агрегатные функции для определения строк для вычисления агрегатных функций. Предложение HAVING, напротив, всегда содержит агрегатные функции. (Строго говоря, вы можете написать предложение HAVING, не используя агрегаты, но это редко бывает полезно. То же самое условие может работать более эффективно на стадии WHERE.)

В предыдущем примере мы могли бы применить фильтр по названию города в предложении WHERE, так как это не агрегатная функция. И это было бы более эффективно, чем ограничение HAVING, так как при этом не потребуется группировать и вычислять агрегаты для всех строк, не удовлетворяющих условию WHERE.