2.7. Агрегатные функции#
2.7. Агрегатные функции #
Как и большинство других продуктов реляционных баз данных, Tantor BE поддерживает агрегатные функции. Агрегатная функция вычисляет единственный результат из нескольких входных строк. Например, существуют агрегатные функции для вычисления 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); WRONG
но это не сработает, так как агрегатная функция 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;
city | count | max ---------------+-------+----- San Francisco | 2 | 46 (1 row)
Оператор |
Важно понимать взаимодействие между агрегатами и предложениями WHERE
и HAVING
в SQL.
Основное различие между 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.