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.
2.7. Aggregate Functions
Like most other relational database products, Postgres Pro supports aggregate functions. An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows.
As an example, we can find the highest low-temperature reading anywhere with:
SELECT max(temp_lo) FROM weather;
max ----- 46 (1 row)
If we wanted to know what city (or cities) that reading occurred in, we might try:
SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG
but this will not work since the aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation; so obviously it has to be evaluated before aggregate functions are computed.) However, as is often the case the query can be restated to accomplish the desired result, here by using a subquery:
SELECT city FROM weather
WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
city
---------------
San Francisco
(1 row)
This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query.
Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the number of readings and the maximum low temperature observed in each city with:
SELECT city, count(*), max(temp_lo)
FROM weather
GROUP BY city;
city | count | max
---------------+-------+-----
Hayward | 1 | 37
San Francisco | 2 | 46
(2 rows)
which gives us one output row per city. Each aggregate result is computed over the table rows matching that city. We can filter these grouped rows using 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)
which gives us the same results for only the cities that have all temp_lo values below 40. Finally, if we only care about cities whose names begin with “S”, we might do:
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)
The |
It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)
In the previous example, we can apply the city name restriction in WHERE, since it needs no aggregate. This is more efficient than adding the restriction to HAVING, because we avoid doing the grouping and aggregate calculations for all rows that fail the WHERE check.
Another way to select the rows that go into an aggregate computation is to use FILTER, which is a per-aggregate option:
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 is much like WHERE, except that it removes rows only from the input of the particular aggregate function that it is attached to. Here, the count aggregate counts only rows with temp_lo below 45; but the max aggregate is still applied to all rows, so it still finds the reading of 46.
