В этом разделе описываются агрегатные функции БД MySQL, которые работают с наборами значений. Они часто используются с предложением GROUP BY
для группировки значений в подмножества.
AVG()
возвращает среднее значение,COUNT(*)
возвращает общее количество извлеченных строк,COUNT(expr)
возвращает количество строк ненулевых значений столбца,COUNT(DISTINCT)
возвращает количество уникальных значений ненулевых значений строк,GROUP_CONCAT()
возвращает строку, с объединенными ненулевыми значениями из группы,MAX()
возвращает максимальное значение,MIN()
возвращает минимальное значение,SUM()
возвращает сумму,STD()
синоним STDDEV_POP()
,STDDEV()
синоним STDDEV_POP()
,STDDEV_POP()
возвращает стандартное отклонение выборки,STDDEV_SAMP()
возвращает выборочное стандартное отклонение,VAR_POP()
возвращает стандартную дисперсию генеральной совокупности,VAR_SAMP()
возвращает выборочную дисперсию,VARIANCE()
синоним VAR_POP()
.AVG([DISTINCT] expr)
:MySQL функция AVG()
возвращает среднее значение выражения expr
. Опцию DISTINCT
можно использовать для возврата среднего значения различных значений expr
.
Если совпадающих строк нет, то AVG()
возвращает NULL
. Функция также возвращает NULL
, если expr
имеет значение NULL
.
Эта функция выполняется как оконная функция, если присутствует over_clause
.
mysql> SELECT student_name, AVG(test_score) FROM student GROUP BY student_name;
COUNT(*)
:Функция COUNT(*)
несколько отличается тем, что возвращает общее количество извлеченных строк, независимо от того, содержат они значения NULL
или нет.
Для механизмов хранения транзакций, таких как InnoDB, сохранение точного количества строк проблематично. Одновременно может выполняться несколько транзакций, каждая из которых может повлиять на подсчет.
InnoDB не ведет внутреннего подсчета строк в таблице, потому что параллельные транзакции могут "видеть" разное количество строк одновременно. Следовательно, операторы SELECT COUNT(*)
подсчитывают только строки, видимые для текущей транзакции.
Начиная с MySQL 8.0.13 (узнать версию SELECT VERSION();
), производительность запросов SELECT COUNT(*) FROM tbl_name
для таблиц InnoDB оптимизирована для однопоточных рабочих нагрузок, если нет дополнительных предложений, таких как WHERE
или GROUP BY
.
InnoDB обрабатывает операторы SELECT COUNT(*)
, просматривая наименьший доступный вторичный индекс, если индекс или подсказка оптимизатора не указывает оптимизатору использовать другой индекс. Если вторичный индекс отсутствует, то InnoDB обрабатывает операторы SELECT COUNT(*)
, сканируя кластеризованный индекс.
Обработка операторов SELECT COUNT(*)
занимает некоторое время, если записи индекса не полностью находятся в пуле буферов. Для более быстрого подсчета, необходимо создать таблицу счетчиков и разрешить приложению обновлять ее в соответствии с выполняемыми вставками и удалениями. Однако этот метод может плохо масштабироваться в ситуациях, когда тысячи одновременных транзакций инициируют обновления одной и той же таблицы счетчиков. Если достаточно приблизительного количества строк, то лучше использовать SHOW TABLE STATUS
.
InnoDB одинаково обрабатывает операции SELECT COUNT(*)
и SELECT COUNT(1)
. Разницы в производительности нет.
Для таблиц MyISAM, быстродействие функции COUNT(*)
оптимизировано, если SELECT
извлекается из одной таблицы, другие столбцы не извлекаются и отсутствует предложение WHERE
. Например:
mysql> SELECT COUNT(*) FROM student;
Эта оптимизация применима только к таблицам MyISAM, поскольку для этого механизма хранения хранится точное количество строк, и к ним можно получить очень быстрый доступ. COUNT(1)
подвергается такой же оптимизации только в том случае, если первый столбец определен как NOT NULL
.
COUNT(expr)
:MySQL функция COUNT(expr)
возвращает количество ненулевых значений expr
, извлеченных оператором SELECT
. Результатом является значение BIGINT
.
Другими словами, функция COUNT(expr)
будет подсчитывать значения, где выражение expr
не равно NULL
или пустой строке. Выражение expr
может быть чем-то простым, например, именем столбца, или сложным выражением, таким как MySQL-функция IF()
.
Предположим, что нужно подсчитать количество строк, в которых есть значения для колонки product
- названий продуктов (т.е. не пустая строка и не NULL
). В этом случае нужно добавить имя колонки product
в качестве выражения в функции COUNT
, что и приведет к подсчету нужных строк.
SELECT COUNT(product) FROM product_details;
Смотрим пример использования условия IF()
внутри функции COUNT()
. И так, если передать IF()
как выражение функции COUNT
, а также установить в IF()
значение NULL
для ложного условия и любое ненулевое значение для истинного условия, то каждое ненулевое значение будет считаться одной строкой для подсчета функцией COUNT()
.
Используем функцию COUNT()
с условием, чтобы найти все товары в пределах определенного диапазона цен.
SELECT COUNT(IF(price>0 AND price<20, 1, NULL)) AS count0_20 FROM product_details;
Запрос выше выведет количество всех продуктов, диапазон цен которых находится в диапазоне от 0 до 20. Обратите внимание, что для ложного условия установлено значение NULL
, которое не учитывается COUNT
.
COUNT(DISTINCT expr,[expr...])
:MySQL функция COUNT(DISTINCT expr)
возвращает количество строк только уникальных значений выражения expr
, отличных от NULL
.
Например, выражение COUNT(DISTINCT customerName)
будет подсчитывать только те строки/записи, которые имеют различные значения для колонки customerName
.
mysql> SELECT COUNT(DISTINCT customerName) FROM order;
При помощи COUNT(DISTINCT expr, expr1, ...) можно получить количество различных комбинаций записей в столбцах, не содержащих NULL
, передав названия требуемых столбцов как список выражений expr, expr1, ...
. В стандартном SQL пришлось бы выполнять конкатенацию всех выражений внутри COUNT(DISTINCT ...)
.
Еще пример с сочетанием COUNT(DISTINCT ...)
и COUNT(expr)
. Необходимо узнать количество общих групп, в котором состоят пользователи user1
и user2
. То есть найти пересечения между пользователи по таблице user_group
. Таблица user_group
хранит связи между user_id
и group_id
.
Следующий запрос выдаст количество записей из таблицы user_group
, которые принадлежат пользователям user1
и user2
, с уникальным group_id
:
SELECT COUNT(group_id) FROM user_group WHERE user_id IN ('user1', 'user2')
В данном случае, если нашлись такие группы, которые были привязаны сразу к двум пользователям, то они будут "схлопнуты" в одну строку.
Если отнять общее число групп двух пользователей и число групп, с учётом уникальности group_id
, то в результате получим искомое количество общих групп у двух пользователей. В результате получаем итоговый запрос.
SELECT (COUNT(group_id) - COUNT(DISTINCT group_id)) AS shared_groups FROM user_group WHERE user_id IN ('user1', 'user2')
Как можно ещё решить задачу?
SELECT COUNT(*) AS shared_groups FROM ( SELECT group_id FROM user_group WHERE user_id IN ('user1','user2') GROUP BY group_id HAVING COUNT(*) > 1 )
GROUP_CONCAT(expr)
:MySQL функция GROUP_CONCAT()
возвращает объединенные значения комбинаций выражений expr
, отличные от NULL
.
Синтаксис:
GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])
Чтобы исключить повторяющиеся значения, нужно использовать предложение DISTINCT
.
Чтобы отсортировать значения в результате, то необходимо использовать предложение ORDER BY
. Для сортировки в обратном порядке необходимо добавления ключевого слова DESC
(по убыванию) к имени столбца, по которому выполняется сортировка, в предложении ORDER BY
. По умолчанию используется восходящий порядок, что может быть указано явно с помощью ключевого слова ASC
.
Разделителем по умолчанию между значениями в группе является запятая ,
. Чтобы явно указать разделитель, нужно использовать ключевое слово SEPARATOR
, за которым следует строковое литеральное значение, которое должно быть вставлено между значениями. Чтобы полностью исключить разделитель, необходимо указать SEPARATOR ' '
.
Примеры:
mysql> SELECT student_name, GROUP_CONCAT(test_score) FROM student GROUP BY student_name; -- или: mysql> SELECT student_name, GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ') FROM student GROUP BY student_name;
Результат усекается до максимальной длины, заданной системной переменной group_concat_max_len
, которая имеет значение по умолчанию 1024. Значение может быть установлено выше, хотя эффективная максимальная длина возвращаемого значения ограничена значением max_allowed_packet
. Синтаксис для изменения значения group_concat_max_len
во время выполнения следующий, где val
- целое число без знака:
SET [GLOBAL | SESSION] group_concat_max_len = val;
Возвращаемое значение представляет собой обычную или двоичную строку, в зависимости от того, являются ли аргументы обычными или двоичными строками. Тип результата - TEXT
или BLOB
, если значение group_concat_max_len
больше 512, если меньше, то тип результата - VARCHAR
или VARBINARY
.
Смотрите также функции CONCAT()
и CONCAT_WS()
.
MAX([DISTINCT] expr)
:MySQL функция MAX()
возвращает максимальное значение выражения expr
. Функция MAX()
может принимать строковый аргумент. В этом случае возвращается максимальное строковое значение.
Ключевое слово DISTINCT
можно использовать для поиска максимального числа различных значений expr
, однако это дает тот же результат, что и пропуск DISTINCT
.
Если совпадающих строк нет или выражение равно NULL
, то MAX()
возвращает NULL
.
Эта функция выполняется как оконная функция, если присутствует over_clause
; его нельзя использовать с DISTINCT
.
mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
Для функции MAX()
, MySQL в настоящее время сравнивает столбцы ENUM
и SET
по их строковому значению, а не по относительному положению строки в наборе. Поведение отличается от того, как их сравнивает ORDER BY
.
MIN([DISTINCT] expr)
:MySQL функция MIN()
возвращает минимальное значение выражения expr
. Функция MIN()
может принимать строковый аргумент. В этом случае возвращается максимальное строковое значение.
Ключевое слово DISTINCT
можно использовать для поиска максимального числа различных значений expr
, однако это дает тот же результат, что и пропуск DISTINCT
.
Если совпадающих строк нет или выражение равно NULL
, то MAX()
возвращает NULL
.
Эта функция выполняется как оконная функция, если присутствует over_clause
; его нельзя использовать с DISTINCT
.
mysql> SELECT student_name, MIN(test_score), MAX(test_score) FROM student GROUP BY student_name;
Для функции MIN()
, MySQL в настоящее время сравнивает столбцы ENUM
и SET
по их строковому значению, а не по относительному положению строки в наборе. Поведение отличается от того, как их сравнивает ORDER BY
.
SUM([DISTINCT] expr)
:MySQL функция SUM()
возвращает сумму expr
. Если в возвращаемом наборе нет строк, то функция SUM()
возвращает NULL
. Ключевое слово DISTINCT
может использоваться для суммирования только различных значений expr
.
Если совпадающих строк нет или expr
равно NULL
, то функция SUM()
возвращает NULL
.
STD(expr)
:MySQL функция STD()
возвращает стандартное отклонение выборки expr
. Функция STD()
- это синоним стандартной SQL-функции STDDEV_POP()
, предоставляемой как расширение MySQL.
Если совпадающих строк нет или если значение expr
равно NULL
, то STD()
возвращает значение NULL
.
STDDEV(expr)
:MySQL функция STDDEV()
возвращает стандартное отклонение генеральной совокупности expr
. Функция STDDEV()
является синонимом стандартной SQL-функции STDDEV_POP()
, предназначенной для совместимости с Oracle.
Если совпадающих строк нет или если значение expr
равно NULL
, то STDDEV()
возвращает значение NULL
.
STDDEV_POP(expr)
:MySQL функция STDDEV_POP()
возвращает стандартное отклонение выборки expr
(квадратный корень из VAR_POP()
). Можно использовать функции STD()
или STDDEV()
, которые эквивалентны, но не являются стандартным SQL-функциями.
Если совпадающих строк нет или если значение expr
равно NULL
, то STDDEV_POP()
возвращает значение NULL
.
STDDEV_SAMP(expr)
:MySQL функция STDDEV_SAMP()
возвращает выборочное стандартное отклонение expr
(квадратный корень из VAR_SAMP()
.
Если совпадающих строк нет или значение expr
равно NULL
, то функция STDDEV_SAMP()
возвращает значение NULL
.
VAR_POP(expr)
:MySQL функция VAR_POP()
возвращает стандартную дисперсию генеральной совокупности expr
. Функция рассматривает строки как всю совокупность, а не как выборку, поэтому количество строк используется в качестве знаменателя. Также можно использовать функцию VARIANCE()
, что эквивалентно, но не является стандартным SQL.
Если совпадающих строк нет или значение expr
равно NULL
, то функция VAR_POP()
возвращает значение NULL
.
VAR_SAMP(expr)
:MySQL функция VAR_SAMP()
возвращает выборочную дисперсию expr
. То есть знаменатель равен количеству строк минус один.
Если совпадающих строк нет или значение expr
равно NULL
, то функция VAR_SAMP()
возвращает значение NULL
.
VARIANCE(expr)
:MySQL функция VARIANCE()
возвращает стандартную дисперсию генеральной совокупности expr
. Функция VARIANCE()
является синонимом стандартной SQL-функции VAR_POP()
, предоставляемой как расширение MySQL.
Если совпадающих строк нет или значение expr
равно NULL
, то функция VARIANCE()
возвращает значение NULL
.