Агрегатные функции в PostgreSQL

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

Возьмем таблицу:

CREATE TABLE person(
`id` INT(10) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`age` INT(4) NOT NULL,
PRIMARY KEY (`id`)
);

В качестве примера, мы можем найти самую старшую персону с помощью запроса:

SELECT max(age) FROM person;

 max
-----
  72
(1 row)

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

SELECT name FROM person WHERE age = max(age);  НЕПРАВИЛЬНО

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

SELECT name FROM person
    WHERE age = (SELECT max(age) FROM person);

     name
---------------
 Павел
(1 row)

Это нормально, потому что подзапрос является независимым вычислением, которое вычисляет свой собственный агрегат отдельно от того, что происходит во внешнем запросе.

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

SELECT name, max(age)
    FROM person
    GROUP BY name;

     name      | max
---------------+-----
 Михаил        |  37
 Павел         |  72
(2 rows)

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

SELECT name, max(age)
    FROM person
    GROUP BY name
    HAVING max(age) < 40;

     name      | max
---------------+-----
 Михаил        |  37
(1 rows)

что дает нам те же результаты только для имен, у которых все значения age ниже 40. Наконец, если мы заботимся только об именах, названия которых начинаются с "П", мы могли бы сделать:

SELECT name, max(age)
    FROM person
    WHERE name LIKE 'П%'
    GROUP BY name
    HAVING max(age) < 40;

     name      | max
---------------+-----
 Павел         |  72
(1 rows)

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

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