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 меньше. Наконец, если мы хотим получить только те города, имена которых начинаются на букву "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.