Что такое агрегатные функции
Агрегатные функции вычисляют некоторые скалярные значения в наборе строк. В MySQL есть следующие агрегатные функции:
- AVG : вычисляет среднее значение
- SUM : вычисляет сумму значений
- MIN : вычисляет наименьшее значение
- MAX : вычисляет наибольшее значение
- COUNT : вычисляет количество строк в запросе
Все агрегатные функции принимают в качестве параметра выражение, которое представляет критерий для определения значений. Зачастую, в качестве выражения выступает название столбца, над значениями которого надо проводить вычисления.
Выражения в функциях AVG и SUM должно представлять числовое значение (например, столбец, который хранит числовые значения). Выражение в функциях MIN , MAX и COUNT может представлять числовое или строковое значение или дату.
Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.
Avg
Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.
Например, пусть есть следующая таблица товаров Products:
CREATE TABLE Products ( Id INT AUTO_INCREMENT PRIMARY KEY, ProductName VARCHAR(30) NOT NULL, Manufacturer VARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price DECIMAL NOT NULL ); INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) VALUES ('iPhone X', 'Apple', 3, 76000), ('iPhone 8', 'Apple', 2, 51000), ('iPhone 7', 'Apple', 5, 32000), ('Galaxy S9', 'Samsung', 2, 56000), ('Galaxy S8', 'Samsung', 1, 46000), ('Honor 10', 'Huawei', 5, 28000), ('Nokia 8', 'HMD Global', 6, 38000)
Найдем среднюю цену товаров из базы данных:
SELECT AVG(Price) AS Average_Price FROM Products
Для поиска среднего значения в качестве выражения в функцию передается столбец Price. Для получаемого значения устанавливается псевдоним Average_Price, хотя в принципе устанавливать псевдоним необязательно.
На этапе выборки можно применять фильтрацию. Например, найдем среднюю цену для товаров определенного производителя:
SELECT AVG(Price) FROM Products WHERE Manufacturer='Apple'
Также можно находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:
SELECT AVG(Price * ProductCount) FROM Products
Count
Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:
SELECT COUNT(*) FROM Products
Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:
SELECT COUNT(Manufacturer) FROM Products
Min и Max
Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров:
SELECT MIN(Price), MAX(Price) FROM Products
Данные функции также игнорируют значения NULL и не учитывают их при подсчете.
Sum
Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:
SELECT SUM(ProductCount) FROM Products
Также вместо имени столбца может передаваться вычисляемое выражение. Например, найдем общую стоимость всех имеющихся товаров:
SELECT SUM(ProductCount * Price) FROM Products
All и Distinct
По умолчанию все вышеперечисленных пять функций учитывают все строки выборки для вычисления результата. Но выборка может содержать повторяющие значения. Если необходимо выполнить вычисления только над уникальными значениями, исключив из набора значений повторяющиеся данные, то для этого применяется оператор DISTINCT .
SELECT COUNT(DISTINCT Manufacturer) FROM Products
По умолчанию вместо DISTINCT применяется оператор ALL , который выбирает все строки:
SELECT COUNT(ALL Manufacturer) FROM Products
В данном случае мы видим, что производители могут повторяться в таблице, так как некоторые товары могут иметь одних и тех же производителей. Поэтому чтобы подсчитать количество уникальных производителей, необходимо использовать оператор DISTINCT.
Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.
Комбинирование функций
Объединим применение нескольких функций:
SELECT COUNT(*) AS ProdCount, SUM(ProductCount) AS TotalCount, MIN(Price) AS MinPrice, MAX(Price) AS MaxPrice, AVG(Price) AS AvgPrice FROM Products
Агрегатные функции (Transact-SQL)
Агрегатная функция выполняет вычисление на наборе значений и возвращает одиночное значение. Агрегатные функции, за исключением COUNT(*) , не учитывают значения NULL. Агрегатные функции часто используются в выражении GROUP BY инструкции SELECT.
Все агрегатные функции являются детерминированными. Другими словами, агрегатные функции возвращают одну и ту же величину при каждом их вызове на одном и том же наборе входных значений. Дополнительные сведения о детерминированности функций см. в статье Детерминированные и недетерминированные функции. Предложение OVER может следовать за всеми агрегатными функциями, кроме STRING_AGG, GROUPING или GROUPING_ID.
Агрегатные функции можно использовать в качестве выражений только в следующих случаях.
- Список выбора инструкции SELECT (вложенный или внешний запрос).
- Предложение HAVING.
Transact-SQL предоставляет следующие агрегатные функции:
2.7. Агрегатные функции
PostgreSQL , как и многие другие реляционные СУБД, поддерживает агрегатные функции. Агрегатная функция производит вычисление над единичным результатом от множества записей. Например, есть агрегаты для вычисления count (количества), sum (суммы), avg (среднего арифметического), max (максимального значения) и min (минимального значения) списка записей.
В качестве примера, мы можем найти наиболее высокую низкую температуру, создав запрос:
SELECT max(temp_lo) FROM weather;
Если мы хотим знать, в каком городе (или городах) это происходило, мы можем попытаться создать такой запрос:
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;
(1) Оператор LIKE выполняет сравнение по шаблону и описывается подробно в PostgreSQL User’s Guide .
Очень важно понимать различия между агрегатами и такими конструкциями SQL как WHERE и HAVING . Фундаментальное отличие между WHERE и HAVING состоит в следующем: WHERE выбирает входящие записи перед группированием и вычислениями агрегатами (а значит управляет тем, какие записи попадут в агрегатные функции), в то время как HAVING выбирает группу записей после группирования и вычисления агрегатов. Таким образом, предложение WHERE не должно содержать агрегатных функций; оно не оказывает влияния на попытку использовать агрегаты для того, чтобы определить какие записи будут переданы агрегатам. С другой стороны предложение HAVING всегда содержит агрегатные функции. (Вкратце, вам разрешается писать предложение HAVING , которое не использует агрегаты, но это непроизводительно: такое же условие можно использовать в предложении WHERE с большей эффективностью).
Обратите внимание, что мы можем применять ограничения по имени города в предложении WHERE , так как оно не требует использования агрегата. Это будет более эффективно, чем добавлять это ограничение в предложение HAVING , потому что мы пропускаем операции группирования и вычисления агрегатов для всех записей, которые не соответствуют условию в предложении WHERE .
| Prev | Home | Next |
| Объединения таблиц (Join) | Up | Обновление данных в таблицах |
Агрегатные функции
SQL позволяет работать не только с отдельными строками, но и производить аналитику по всем строкам или по группе строк. Функции, возвращающие единственное значение для набора строк, называются агрегатными.
В большинстве задач можно обойтись следующим джентельменским набором агрегатных функций:
- avg(выражение) — арифметическое среднее;
- min(выражение) — минимальное значение выражения;
- max(выражение) — маскимальное значение выражения;
- sum(выражение) — сумма значений выражения;
- count(*) — количество строк в результате запроса;
- count(выражение) — количество значений выражения, не равных NULL .
Если в SQL запросе не используется GROUP BY , то значение агрегатной функции вычисляется по всем строкам, полученным в результате выполнения инструкций FROM и WHERE .
Чтобы вызвать агрегатную функцию необходимо указать ее в списке выборки (после SELECT ). Если в SELECT использована хотя бы одна агрегатная функция, то значениями других столбцов могут быть только вызовы агрегатных функций, либо константы. Попробуем воспользоваться агрегатными функциями на следующей таблице:
SELECT * FROM table1 | value |
|---|
| 1 |
| 2 |
| 3 |
| NULL |
SELECT avg(value), min(value), max(value), sum(value), count(*) as count_total, count(value) as count_value FROM table1 | avg | min | max | sum | count_total | count_value |
|---|---|---|---|---|---|
| 2 | 1 | 3 | 6 | 4 | 3 |
При попытке вызова агрегатной функции вместе с выводом столбца таблицы в списке выборки возникнет ошибка:
SELECT product_id, max(count) FROM purchase_item column "purchase_item.product_id" must appear in the GROUP BY clause or be used in an aggregate function И это логично. Агрегатная функция должна вернуть одно значение на весь набор данных (в нашем запросе одну строку), а какое значение product_id взять из всех строк? Помимо агрегатных функций есть еще аналитические функции, которые не изменяют количество строк в результате выборки, но при этом позволяют вычислять значения по набору строк. С ними мы познакомимся позднее.