Агрегатные функции — это все о
- Выполнение расчетов в несколько рядов
- Из одного столбца таблицы
- И возвращая единственное значение.
Стандарт ISO определяет пять (5) агрегатных функций, а именно:
1) COUNT
2) SUM
3) AVG
4) MIN
5) MAX
Зачем использовать агрегатные функции.
С точки зрения бизнеса разные уровни организации предъявляют разные информационные требования. Менеджеры высшего уровня, как правило, заинтересованы в знании целых цифр и не нуждаются в отдельных деталях.
> Агрегатные функции позволяют нам легко получать сводные данные из нашей базы данных.
Например, из нашей базы данных myflix, руководство может потребовать следующих отчетов
- Наименее арендованные фильмы.
- Самые арендованные фильмы.
- Среднее число, которое каждый фильм сдается в месяц.
Мы легко создаем вышеуказанные отчеты, используя агрегатные функции.
Давайте рассмотрим агрегатные функции подробно.
Функция COUNT
Функция COUNT возвращает общее количество значений в указанном поле. Он работает как с числовыми, так и с нечисловыми типами данных. Все агрегатные функции по умолчанию исключают нулевые значения перед работой с данными.
COUNT (*) — это специальная реализация функции COUNT, которая возвращает количество всех строк в указанной таблице. COUNT (*) также считает Null и дубликаты.
В приведенной ниже таблице показаны данные в таблице movierentals
| номер ссылки | Дата сделки | Дата возвращения | членский номер | 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 Workbench против 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`;
Как показано ниже, отчет об исключении повторяющихся записей из результатов.
movie_id 1 2 3
Функция MIN
Функция MIN возвращает наименьшее значение в указанном поле таблицы .
В качестве примера, давайте предположим, что мы хотим знать год, в который был выпущен самый старый фильм в нашей библиотеке, мы можем использовать функцию MIN в MySQL, чтобы получить необходимую информацию.
Следующий запрос помогает нам достичь этого
SELECT MIN(`year_released`) FROM `movies`;
Выполнение вышеупомянутого запроса в MySQL Workbench против myflixdb дает нам следующие результаты.
MIN('year_released') 2005
Макс функция
Как следует из названия, функция MAX противоположна функции MIN. Он возвращает наибольшее значение из указанного поля таблицы .
Давайте предположим, что мы хотим получить год, когда был выпущен последний фильм в нашей базе данных. Мы можем легко использовать функцию MAX для достижения этой цели.
В следующем примере возвращается последний год выпуска фильма.
SELECT MAX(`year_released`) FROM `movies`;
Выполнение вышеуказанного запроса в MySQL Workbench с использованием myflixdb дает нам следующие результаты.
MAX('year_released') 2012
СУММА функция
Предположим, мы хотим получить отчет, в котором указана общая сумма выполненных платежей. Мы можем использовать функцию MySQL SUM, которая возвращает сумму всех значений в указанном столбце . SUM работает только с числовыми полями . Нулевые значения исключаются из возвращаемого результата.
В следующей таблице приведены данные в таблице платежей.
| идентификатор платежа | членский номер | дата платежа | описание | выплаченная сумма | external_ reference_number |
|---|---|---|---|---|---|
| 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 возвращает среднее значение в указанном столбце . Как и функция SUM, она работает только с числовыми типами данных .
Предположим, мы хотим найти среднюю уплаченную сумму. Мы можем использовать следующий запрос —
SELECT AVG(`amount_paid`) FROM `payments`;
Выполнение вышеуказанного запроса в MySQL Workbench дает нам следующие результаты.
AVG('amount_paid') 3500
Резюме
- MySQL поддерживает все пять (5) стандартных агрегатных функций ISO COUNT, SUM, AVG, MIN и MAX.
- Функции SUM и AVG работают только с числовыми данными.
- Если вы хотите исключить повторяющиеся значения из результатов агрегатной функции, используйте ключевое слово DISTINCT. Ключевое слово ALL включает даже дубликаты. Если ничего не указано, по умолчанию принимается ALL.
- Агрегатные функции могут использоваться в сочетании с другими предложениями SQL, такими как GROUP BY
Логические
Вы думаете, что агрегатные функции просты. Попробуй это!
В следующем примере группируются участники по имени, подсчитывается общее количество платежей, средняя сумма платежей и общая сумма сумм платежей.
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 дает нам следующие результаты.
