Агрегатные функции в MySQL

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

• Выполнение вычислений в нескольких строках
• Из одного столбца таблицы
• И возвращаем одно значение.

Стандарт ISO определяет пять (5) агрегатных функций, а именно:

1) СЧЕТ
2) СУММА
3) AVG
4) МИН
5) Макс

Зачем использовать агрегатные функции

С точки зрения бизнеса, разные уровни организации предъявляют разные требования к информации. Менеджеры высшего звена обычно заинтересованы в знании целых цифр, а не в отдельных деталях.

Агрегатные функции позволяют нам легко получать обобщенные данные из нашей базы данных.

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

• Меньше всего берут напрокат фильмы.
• Самые популярные фильмы, которые берут напрокат.
• Среднее количество сданных в прокат каждого фильма в месяц.

Мы легко создаем вышеуказанные отчеты, используя агрегатные функции.

Давайте подробно рассмотрим агрегатные функции.

СЧЁТ (функция СЧЁТ)

Функция COUNT возвращает общее количество значений в указанном поле. Он работает как с числовыми, так и с нечисловыми типами данных. Все агрегатные функции по умолчанию исключают значения NULL перед работой с данными.

COUNT (*) — это специальная реализация функции COUNT, которая возвращает количество всех строк в указанной таблице. COUNT (*) также учитывает обнуляет и дубликаты.

В таблице ниже показаны данные таблицы проката фильмов.

ссылочный_ номер	Дата сделки	Дата возврата	Количество членов	Movie_id	фильм_ вернулся
11	20-06-2012	NULL,	1	1	0
12	22-06-2012	25-06-2012	1	2	0
13	22-06-2012	25-06-2012	3	2	0
14	21-06-2012	24-06-2012	2	2	0
15	23-06-2012	NULL,	3	3	0

Предположим, мы хотим узнать, сколько раз фильм с идентификатором 2 был сдан в прокат.

SELECT COUNT(`movie_id`)  FROM `movierentals` WHERE `movie_id` = 2;

Выполнение вышеуказанного запроса в MySQL верстак против myflixdb дает нам следующие результаты.

COUNT('movie_id')



3

DISTINCT ключевое слово

Ключевое слово DISTINCT, позволяющее исключить дубликаты из результатов. Это достигается путем группировки схожих значений.

Чтобы оценить концепцию Distinct, давайте выполним простой запрос

SELECT `movie_id` FROM `movierentals`;




movie_id


1


2


2


2


3

Теперь давайте выполним тот же запрос с отдельным ключевым словом:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Как показано ниже, Different исключает повторяющиеся записи из результатов.

movie_id



1


2


3

Функция MIN

Функция МИН возвращает наименьшее значение в указанном поле таблицы.

В качестве примера предположим, что мы хотим узнать год, в котором был выпущен самый старый фильм в нашей библиотеке. Мы можем использовать MySQLФункция MIN для получения желаемой информации.

Следующий запрос поможет нам добиться этого

SELECT MIN(`year_released`) FROM `movies`;

Выполнение вышеуказанного запроса в MySQL Workbench против myflixdb дает нам следующие результаты.

MIN('year_released')



2005

Макс функция

Как следует из названия, функция МАКС является противоположностью функции МИН. Это возвращает наибольшее значение из указанного поля таблицы.

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

В следующем примере возвращается последний год выпуска фильма.

SELECT MAX(`year_released`)  FROM `movies`;

Выполнение вышеуказанного запроса в MySQL Workbench с использованием myflixdb дает нам следующие результаты.

MAX('year_released')



2012

СУММА функция

Предположим, нам нужен отчет, в котором указана общая сумма произведенных на данный момент платежей. Мы можем использовать MySQL SUM функция, которая возвращает сумму всех значений в указанном столбце. СУММ работает только с числовыми полями.. Нулевые значения исключаются из возвращаемого результата.

В следующей таблице показаны данные таблицы платежей.

идентификатор_платежа	Количество членов	дата_платежа	описание	сумма_ выплачена	внешний_ ссылочный _номер
1	1	23-07-2012	Оплата проката фильма	2500	11
2	1	25-07-2012	Оплата проката фильма	2000	12
3	3	30-07-2012	Оплата проката фильма	6000	NULL,

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

SELECT SUM(`amount_paid`) FROM `payments`;

Выполнение вышеуказанного запроса в MySQL Workbench против myflixdb дает следующие результаты.

SUM('amount_paid')



10500

AVG функция

MySQL AVG функция возвращает среднее значение значений в указанном столбце. Как и функция СУММ, она работает только с числовыми типами данных.

Предположим, мы хотим найти среднюю уплаченную сумму. Мы можем использовать следующий запрос –

SELECT AVG(`amount_paid`)  FROM `payments`;

Выполнение вышеуказанного запроса в MySQL Workbench, дает нам следующие результаты.

AVG('amount_paid')



3500

Логические

Вы думаете, что агрегатные функции — это просто. Попробуй это!

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

SELECT m.`full_names`,COUNT(p.`payment_id`) AS  `paymentscount`,AVG(p.`amount_paid`)  AS `averagepaymentamount`,SUM(p.`amount_paid`)  AS `totalpayments` FROM members m, payments p WHERE m.`membership_number` = p.`membership_number` GROUP BY m.`full_names`;

Выполнение приведенного выше примера в MySQL Workbench дает нам следующие результаты.

Резюме

• MySQL поддерживает все пять (5) стандартных агрегатных функций ISO COUNT, SUM, AVG, МИН и МАКС.
• СУММА и AVG функции работают только с числовыми данными.
• Если вы хотите исключить повторяющиеся значения из результатов агрегатной функции, используйте ключевое слово DISTINCT. Ключевое слово ALL включает даже дубликаты. Если ничего не указано, значением по умолчанию считается ALL.
• Агрегатные функции можно использовать в сочетании с другими предложениями SQL, такими как ГРУППА ПО