MySQL Агрегатные функции: SUM, COUNT, AVG & МАКС

Что такое агрегатные функции в... MySQL?

An агрегатная функция Функция считывает множество строк одного столбца и объединяет их в одно значение. Агрегатные функции в основном предназначены для:

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

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

1. СЧИТАТЬ
2. SUM
3. AVG
4. MIN
5. MAX

Одно правило применимо ко всем пяти: Агрегатные функции игнорируют значения NULL.Функция COUNT(*) является единственным исключением, и мы рассмотрим, почему это так, ниже.

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

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

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

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

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

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

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

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

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

В таблице movierentals хранятся следующие данные:

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

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

Выполнение этого в MySQL Верстак Запрос к базе данных myflixdb возвращает 3, потому что три строки содержат movie_id 2.

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

COUNT отвечает на вопрос «сколько». Следующий вопрос обычно звучит так: «сколько?» различный «единицы», и именно для этого и существует DISTINCT.

Ключевое слово DISTINCT исключает дубликаты из результатов поиска по группам.ping Идентичные значения сопоставляются, как и показано на иллюстрации выше.

Для начала выполним простой запрос.

SELECT `movie_id` FROM `movierentals`;

Теперь тот же запрос с ключевым словом DISTINCT:

SELECT DISTINCT `movie_id` FROM `movierentals`;

Параметр DISTINCT исключает повторяющиеся записи:

COUNT против COUNT(*) против COUNT(DISTINCT): какой из них следует использовать?

DISTINCT также может быть размещен внутри агрегатная функция, и именно здесь большинство новичков теряются. track из этих строк фактически подсчитываются. Все четыре приведенные ниже формы работают с одной и той же таблицей проката фильмов, состоящей из пяти строк, показанной ранее, однако они не все возвращают одинаковое число. Разница сводится к двум вопросам: подсчитывает ли форма строки или значения, и сохраняет ли она дубликаты?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Совет: Используйте COUNT(*) для подсчета строк, COUNT(column), когда NULL означает «не применимо», и COUNT(DISTINCT column) для уникальных значений. Противоположностью DISTINCT является ALL — значение по умолчанию, поэтому его редко указывают.

Функция MIN

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

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

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

Результат:

Макс функция

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

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

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

Результат:

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

Функции MIN и MAX выбирают существующее значение из столбца. Функции SUM и SUM... AVG Вычислите новое число из всего столбца.

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

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

Приведенный ниже запрос получает все совершенные платежи и суммирует их в один результат: 2500 + 2000 + 6000 = 10500.

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

Результат:

AVG функция

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

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

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

Результат:

⚠️ Предупреждение: AVG Делится на количество строк, не содержащих NULL, а не на общее количество строк в таблице. Значения NULL пропускаются, а не учитываются как ноль, что незаметно повышает среднее значение. AVG(IFNULL(`amount_paid`, 0)) когда отсутствующее значение означает ноль.

Практический пример: сочетание агрегатных функций с функцией 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 выдает следующие результаты.

Запрос объединяет две таблицы в условии WHERE — старый стиль объединения с помощью запятой. Современный код использует ту же логику в явном виде. ВНУТРЕННЕЕ СОЕДИНЕНИЕ… ВКЛ.Обратите также внимание, что каждый неагрегированный столбец в списке SELECT должен присутствовать в GROUP BY, или MySQL В версиях 5.7 и более поздних версиях запрос отклоняется в рамках параметра ONLY_FULL_GROUP_BY. См. Официальный представитель в Грузии MySQL справочник агрегатных функций.

Часто задаваемые вопросы (FAQ)