Что такое агрегатные функции
Перейти к содержимому

Что такое агрегатные функции

  • автор:

Что такое агрегатные функции

Агрегатные функции вычисляют некоторые скалярные значения в наборе строк. В 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, хотя в принципе устанавливать псевдоним необязательно.

Функция avg и поиск среднего значения в MySQL

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

SELECT AVG(Price) FROM Products WHERE Manufacturer='Apple'

Также можно находить среднее значение для более сложных выражений. Например, найдем среднюю сумму всех товаров, учитывая их количество:

SELECT AVG(Price * ProductCount) FROM Products

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM Products

Функция count в MySQL и вычисление количества строк

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно. Например, найдем минимальную цену среди товаров:

SELECT MIN(Price), MAX(Price) FROM Products

MIN и MAX в MySQL

Данные функции также игнорируют значения 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

Агрегатные функции — Основы SQL

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

Чтобы вызвать агрегатную функцию, нужно написать ее имя и затем в круглых скобках передать аргументы — переменные, над которыми будут совершаться действия. В этом уроке мы поближе познакомимся с агрегатными функциями и их аргументами.

Функция COUNT

Ранее в курсе мы научились выбирать все записи из таблицы. Например, мы можем выбрать все записи из таблицы пользователей с помощью такого запроса:

SELECT * FROM users;

Запрос вернет нам много строк, но точное их количество мы не знаем. Чтобы это выяснить, можно использовать функцию COUNT — она возвращает количество записей в выборке.

Ответить на вопрос «А сколько записей содержится в таблице users ?» можно с помощью такого запроса:

SELECT COUNT(*) FROM users;

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

В качестве аргумента мы передали символ * , который заменяет все поля таблицы.

Еще в качестве аргумента мы можем передать имя поля. В этом случае функция вернет нам количество строк, в которых поле НЕ равно NULL . Выполним такой запрос:

SELECT COUNT(id) FROM users;

Этот запрос также вернул значение 99 — это количество строк, id которых не равен NULL .

Подадим в качестве аргумента поле email_confirmed :

SELECT COUNT(email_confirmed) FROM users;

Этот запрос вернул 0 , потому что в таблице поле email_confirmed не содержит никаких данных.

Обратите внимание, что COUNT возвращает количество записей в запросе, а не в таблице:

SELECT COUNT(*) FROM users WHERE gender = 'female';

Этот запрос вернет количество девушек среди всех пользователей.

Функция SUM

Функция SUM возвращает сумму всех значений:

SELECT SUM(spent_minutes) FROM course_reviews;

В этом запросе мы нашли суммарное время, проведенное всеми пользователями на онлайн-курсах.

В качестве аргумента в функцию SUM нужно передать числовое поле. Если передать поле, в котором хранятся строки или даты, то запрос вернет ошибку. Также, не получится передать в функцию символ * , как мы это делали с функцией COUNT :

-- Передадим поле с датами создания — запрос вернет ошибку SELECT SUM(created_at) FROM course_reviews; -- Передадим символ '*' — запрос завершится с ошибкой SELECT SUM(*) FROM course_reviews;

Функция AVG

Чтобы найти среднее арифметическое всех значений, нужно воспользоваться функцией AVG (average — среднее).

В таблице ниже вы увидите, как это происходит:

Работает функция так же как и SUM . В качестве аргумента нужно передать числовое поле.

Найдем среднее время, проведенное пользователем с id = 9 на площадке онлайн-школы:

SELECT AVG(spent_minutes) FROM course_reviews WHERE user_id = 9;

Что такое агрегатные функции

В статье об группировках мы обсудили, что при использовании оператора GROUP BY мы можем использовать агрегатные функции. Давайте поговорим о них поглубже ��.

Агрегатная функция – это функция, которая выполняет вычисление на наборе значений и возвращает одиночное значение.

MySQL
SELECT [литералы, агрегатные_функции, поля_группировки] FROM имя_таблицы GROUP BY поля_группировки; 


Например, запрос с использованием агрегатной функции AVG может выглядеть так:


MySQL
 
SELECT home_type, AVG(price) as avg_price FROM Rooms GROUP BY home_type 





home_type
avg_price






Private room
89.4286




Entire home/apt
148.6667




Shared room
40






Агрегатные функции SQL (SQL)


С помощью агрегатных функций SQL вы можете определить различные статистические данные для наборов значений. Вы можете использовать эти функции в запросе и агрегатных выражениях в свойстве SQL объекта QueryDef или при создании объекта Recordset на основе SQL запроса.


    

  • Функция получения среднего
    • 

  • Функция счета
    • 

  • Функции первый и последний (Access SQL)
    • 

  • функция минимум и максимум
    • 

  • Функции StDev и StDevP (Access SQL)
    • 

  • Функция SUM
    • 

  • Функции Var и VarP (Access SQL)
    • 



Дополнительные ресурсы


Значок отказа согласно Закону Калифорнии о защите конфиденциальности потребителей (CCPA)





Похожие публикации:
    

  1. Где находится датчик уровня воды в стиральной машине
    2. 

  2. Как включить компьютер без кнопки включения с помощью клавиатуры
    3. 

  3. Как скрыть все примечания в excel одновременно
    4. 

  4. Телевизор показывает синим цветом как исправить
    5. 









		

		
Добавить комментарий 
Ваш адрес email не будет опубликован. Обязательные поля помечены *