ресурс для начинающих веб-разработчиков
комплексные веб-услуги по созданию сайтов

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

Функции, применяемые совместно с конструкцией GROUP BY, часто называют агрегатными или суммирующими функциями. Они предназначены для вычисления одного значения для каждой группы, создаваемой конструкцией GROUP BY. Агрегатные функции позволяют определить количество строк, входящих в группу, подсчитать среднее значение или получить сумму значений столбцов. Результирующее значение рассчитывается только для значений, не равных NULL(исключение составляет лишь функция COUNT(*), которая подсчитывает общее количество строк). Данные функции допустимо использовать и в запросах без группировки: в этом случае вся выборка выступает как одна большая группа.

Среднее значение

Функция AVG() возвращает среднее значение аргумента expr. В качестве аргумента обычно выступает имя столбца. Необязательное ключевое слово DISTINCT позволяет дать указание СУБД MySQL обрабатывать только уникальные значения столбца expr.

Для примера, создадим таблицу catalogs, состоящую из трех полей: id_catalog — первичный ключ, name — название товара и total — количество единиц данного товара на складе.

Таблица catalogs

Среднее количество товарныз позиций на складе

При использовании конструкции GROUP BY функция AVG() вычисляет среднее значение для каждой группы товаров. Создадим новую таблицу products, в которой хранится информация обо всех имеющихся товарах. Таблица содержит следующие поля:

• id_product —первичный ключ;
• name — название товара;
• priсe — цена за единицу товара;
• id_catalog — ссылка на раздел каталога, к которому относится товар.

Таблица products

Если сгрупперовать записи таблицы products по полю id_catalog, при помощи встроенной функции AVG() можно узнать среднюю цену по каждому из пяти разделов каталога.

Использование функции AVG() совместно с GROUP BY

Средние значения, полученные при помощи функции AVG(), могут использоваться в вычисляемых столбцах. Например, для того чтобы увеличить среднее значение для каждого раздела каталога на 20%, достаточно умножить либо столбец priсe, либо функцию AVG() на 1.2.

Использование функции AVG() в выражениях

Сортировка агрегатных значений

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

Сортировка результирующей таблицы

Подсчет количества записей в таблице

Подсчет количества записей в таблице осуществляется при помощи функции COUNT(), имеющей несколько форм со следующим синтаксисом:

COUNT(expr)
COUNT(*)
COUNT(DISTINCT expr1, expr2, . . .)

Первая форма возвращает количество записей в таблице, поле expr для которых не равно NULL.

Для примера, воспользуемся ранее созданной таблицей tb1 в базе данных wet.

Использование функции COUNT()

Как видно из примера, для полей id и id_cat возвращаются различные значения. Это связано с тем, что количество NULL-полей в столбцах различаются.

Форма функции COUNT(*) возвращает общее количество строк в таблице, независимо от того, принимает какое-либо поле значение NULL или нет. Запись учитывается в результате, даже если все поля равны NULL.

Использование функции COUNT(*)

Функция COUNT(*) оптимизирована для быстрого возврата результата при условии, что команда SELECT извлекает данные из одной таблицы, ни какие другие столбцы не обрабатываются и запрос не содержит условие WHERE.

Функция COUNT() может быть использована не только для подсчета общего количества записей в таблице, но и для подсчета количества строк в выборке с условием WHERE.

Использование функции COUNT() совместно с WHERE

В примере, представлен запрос, извлекающий из таблицы tb1 записи, чье поле id_cat больше 20. Следует обратить внимание, что поля, содержащие NULL, не удовлетворяют этому условию, так как NULL обозначает отсутствие данных.

Разумеется, как агрегатная функция COUNT() может быть использована для вычисления количества записей в каждой из групп, полученных в результате применения группировки результата с помощью конструкции GROUP BY.

Рассмотрим запрос к таблице products, сообщающий о количестве имеющегося товара по каждому разделу каталога.

Подсчет количества товара для каждого раздела каталога

Сортировка строк в результирующей таблице производится по количеству имеющегося товара. Для этого столбцу COUNT(*) при помощи ключевого слова AS назначается псевдоним total, который затем используется в выражении GROUP BY.

Третий вариант функции COUNT() позволяет использовать ключевое слово DISTINCT, которое обеспечивает подсчет только уникальных значений столбца.

Использование ключевого слова DISTINCT совместно с COUNT()

Объединение значений группы

Для объединения значения группы предназначена функция GROUP_CONCAT(), которая имеет следующий синтаксис:

GROUP_CONCAT([DISTINCT] expr [, expr ...]
[ORDER BY {unsigned_integer|col_name|expr}
[ASC|DESC] [, col_name ...]]
[SEPARATOR str_val])

В простейшем случае функция принимает имя столбца expr и возвращает строку со значениями столбца, разделенными запятыми

.

Рассмотрим пример использования функции GROUP_CONCAT() для поля id_catalog таблицы products.

Использование функции GROUP_CONCAT()

При передаче в качестве аргумента имени числового столбца его значения автоматически преобразуются к текстовому типу.

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

Рассмотрим пример, где представлен запрос, извлекающий уникальные значения столбца id_catalog таблицы products с использованием в качестве разделителя символа "-".

Использование ключевых слов DISTINCT и SEPARATOR

Ключевое слово ORDER BY позволяет отсортировать значения в рамках возвращаемой строки. Отсортируем значения в обратном порядке.

Использование ключевого слова ORDER BY

А теперь возвратим список цен (в порядке убывания) на товары из каждого раздела каталога.

Список цен на товары по разделам каталога

Поиск минимального и максимального значений

Для поиска минимального значения в столбце expr предназначена функция MIN([DISTINCT] expr).

В качестве аргумента обычно выступает имя столбца. Необязательное слово DISTINCT позволяет дать указание СУБД MySQL обрабатывать только уникальные значения столбца expr.

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

Использование функции MIN()

Использование конструкции GROUP BY id_catalog позволяет найти минимальную цену для каждого раздела каталога.

Использование функции MIN () совместно с конструкцией GROUP BY

Как видно из запроса, товар с минимальной ценой назодится в пятом разделе каталога.

Примечание. Функцию MIN() можно использовать также со строковыми столбцами. В этом случае возвращается минимальное лексикографическое значение.

Для поиска максимального значения в столбце expr предназначена функция

MAX([DISTINCT] expr)

В качестве аргумента expr обычно выступает имя столбца. Необязательное ключевое слово DISTINCT позволяет дать указание СУБД MySQL обрабатывать только уникальные значения столбца expr.

Использование функции MAX()

Сумма столбцов

Сумму столбца expr позволяет подсчитать функции

SUM([DISTINCT] expr)

Если возвращаемый набор данных не содержит ни одной строки, то функция возвращает NULL. Необязательное ключевое слово DISTINCT позволяет потребовать от СУБД MySQL обрабатывать только уникальные значения столбца expr.

Использование функции SUM()