Сообщить об ошибке.

MySQL: Агрегатные (групповые) функций

В этом разделе описываются агрегатные функции БД MySQL, которые работают с наборами значений. Они часто используются с предложением GROUP BY для группировки значений в подмножества.

Содержание:


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.