2.7. Агрегатные функции #
Как большинство других серверов реляционных баз данных, Postgres Pro поддерживает агрегатные функции. Агрегатная функция вычисляет единственное значение, обрабатывая множество строк. Например, есть агрегатные функции, вычисляющие: 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, count(*), max(temp_lo) FROM weather GROUP BY city;
city | count | max ---------------+-------+----- Hayward | 1 | 37 San Francisco | 2 | 46 (2 rows)
Здесь мы получаем по одной строке для каждого города. Каждый агрегатный результат вычисляется по строкам таблицы, соответствующим отдельному городу. Мы можем отфильтровать сгруппированные строки с помощью предложения 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)
Мы получаем те же результаты, но только для тех городов, где все значения temp_lo
меньше 40. Наконец, если нас интересуют только города, названия которых начинаются с «S
», мы можем сделать:
SELECT city, count(*), max(temp_lo) FROM weather WHERE city LIKE 'S%' -- (1) GROUP BY city;
Оператор |
Важно понимать, как соотносятся агрегатные функции и SQL-предложения WHERE
и HAVING
. Основное отличие WHERE
от HAVING
заключается в том, что WHERE
сначала выбирает строки, а затем группирует их и вычисляет агрегатные функции (таким образом, она отбирает строки для вычисления агрегатов), тогда как HAVING
отбирает строки групп после группировки и вычисления агрегатных функций. Как следствие, предложение WHERE
не должно содержать агрегатных функций; не имеет смысла использовать агрегатные функции для определения строк для вычисления агрегатных функций. Предложение HAVING
, напротив, всегда содержит агрегатные функции. (Строго говоря, вы можете написать предложение HAVING
, не используя агрегаты, но это редко бывает полезно. То же самое условие может работать более эффективно на стадии WHERE
.)
В предыдущем примере мы смогли применить фильтр по названию города в предложении WHERE
, так как названия не нужно агрегировать. Такой фильтр эффективнее, чем дополнительное ограничение HAVING
, потому что с ним не приходится группировать и вычислять агрегаты для всех строк, не удовлетворяющих условию WHERE
.
Ещё один способ выбрать строки, которые входят в составные вычисления, — это использовать предложение FILTER
, которое указывается для каждой агрегатной функции:
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)
Предложение FILTER
очень похоже на WHERE
, за исключением того, что отбрасываются входные строки только конкретной агрегатной функции, с которой оно используется. Здесь агрегатная функция count
подсчитывает только строки с temp_lo
ниже 45; но агрегатная функция max
по-прежнему применяется ко всем строкам, поэтому находит значение 46.