AVG([DISTINCT] expr)

Возвращает среднее значение expr. Опция DISTINCT может быть использована для возвращения среднего значения различных значений expr.

Если нет соответствующих строк, AVG() возвращает NULL.

mysql> SELECT student_name, AVG(test_score)
       FROM student
       GROUP BY student_name;

BIT_AND(expr)

Возвращает побитовое AND всех битов в expr. Вычисление выполняется с 64-битной (BIGINT) точностью.

Если нет соответствующих строк, BIT_AND() возвращает нейтральное значение (все биты установлены в 1).

BIT_OR(expr)

Возвращает побитовое OR всех битов в expr. Вычисление выполняется с 64-битной (BIGINT) точностью.

Если нет соответствующих строк, BIT_OR() возвращает нейтральное значение (все биты установлены в 0).

BIT_XOR(expr)

Возвращает побитовое XOR всех битов в expr. Вычисление выполняется с 64-битной (BIGINT) точностью.

Если нет соответствующих строк, BIT_XOR() возвращает нейтральное значение (все биты установлены в 0).

COUNT(expr)

Возвращает количество значений expr, не являющихся NULL, в строках, полученных оператором SELECT. Результат является значением BIGINT.

Если нет соответствующих строк, COUNT() возвращает 0.

mysql> SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

COUNT(*) несколько отличается тем, что возвращает количество полученных строк, содержат ли они значения NULL или нет.

Для транзакционных хранилищ, таких как InnoDB, хранение точного количества строк представляет собой проблему. Одновременно может выполняться несколько транзакций, каждая из которых может повлиять на счёт.

InnoDB не сохраняет внутренний счёт строк в таблице, так как параллельные транзакции могут “видеть” разное количество строк одновременно. Вследствие этого, операторы SELECT COUNT(*) подсчитывают только строки, видимые текущей транзакцией.

До MySQL 5.7.18, InnoDB обрабатывает операторы SELECT COUNT(*), сканируя кластеризованный индекс. Начиная с MySQL 5.7.18, 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(DISTINCT expr,[expr...])

Возвращает количество строк с различными значениями expr, не являющимися NULL.

Если нет соответствующих строк, COUNT(DISTINCT) возвращает 0.

mysql> SELECT COUNT(DISTINCT results) FROM student;

В MySQL можно получить количество различных комбинаций выражений, не содержащих NULL, указав список выражений. В стандартном SQL пришлось бы использовать конкатенацию всех выражений внутри COUNT(DISTINCT ...).

GROUP_CONCAT(expr)

Эта функция возвращает строковый результат с конкатенированными значениями, не являющимися NULL, из группы. Возвращает NULL, если нет значений, не являющихся NULL. Полный синтаксис следующий:

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

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;

В MySQL вы можете получить конкатенированные значения комбинаций выражений. Для устранения дубликатов значений используйте предложение DISTINCT. Для сортировки значений в результате используйте предложение ORDER BY. Для сортировки в обратном порядке добавьте ключевое слово DESC (убывание) к имени столбца, по которому вы сортируете в предложении ORDER BY. По умолчанию используется возрастающий порядок; это может быть указано явно с помощью ключевого слова ASC. По умолчанию разделителем между значениями в группе является запятая (,). Для явного указания разделителя используйте SEPARATOR, за которым следует строковая константа, которая должна быть вставлена между значениями группы. Для удаления разделителя полностью укажите SEPARATOR ''.

Результат усекается до максимальной длины, задаваемой переменной сервера 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.

Если GROUP_CONCAT() вызывается из клиента mysql, результаты бинарных строк отображаются в шестнадцатеричном формате в зависимости от значения --binary-as-hex. Дополнительную информацию об этом параметре см. в разделе 4.5.1, «mysql — Клиент командной строки MySQL».

См. также CONCAT() и CONCAT_WS(): раздел 12.8, «Функции и операторы работы со строками».

JSON_ARRAYAGG(col_or_expr)

Агрегирует результат в одном массиве JSON, элементы которого состоят из строк. Порядок элементов в этом массиве не определён. Функция работает со столбцом или выражением, которое оценивается как одиночное значение. Возвращает NULL, если результат не содержит строк или при ошибке.

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_ARRAYAGG(attribute) AS attributes
    -> FROM t3 GROUP BY o_id;
+------+---------------------+
| o_id | attributes          |
+------+---------------------+
|    2 | ["color", "fabric"] |
|    3 | ["color", "shape"]  |
+------+---------------------+
2 rows in set (0.00 sec)

Добавлен в MySQL 5.7.22.

JSON_OBJECTAGG(key, value)

Принимает два имени столбцов или выражения в качестве аргументов, первое из которых используется в качестве ключа, а второе — в качестве значения, и возвращает объект JSON, содержащий пары ключ-значение. Возвращает NULL, если результат не содержит строк или в случае ошибки. Ошибка возникает, если любое имя ключа является NULL или количество аргументов не равно 2.

mysql> SELECT o_id, attribute, value FROM t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
|    2 | color     | red   |
|    2 | fabric    | silk  |
|    3 | color     | green |
|    3 | shape     | square|
+------+-----------+-------+
4 rows in set (0.00 sec)

mysql> SELECT o_id, JSON_OBJECTAGG(attribute, value)
    -> FROM t3 GROUP BY o_id;
+------+---------------------------------------+
| o_id | JSON_OBJECTAGG(attribute, value)      |
+------+---------------------------------------+
|    2 | {"color": "red", "fabric": "silk"}    |
|    3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)

Обработка дубликатов ключей. При нормализации результата этой функции значения с дублирующимися ключами отбрасываются. В соответствии со спецификацией типа данных MySQL JSON, которая не допускает дубликатов ключей, используется только последнее встреченное значение с этим ключом в возвращаемом объекте (“last duplicate key wins”). Это означает, что результат использования этой функции для столбцов из SELECT может зависеть от порядка, в котором возвращаются строки, что не гарантируется.

Рассмотрим следующее:

mysql> CREATE TABLE t(c VARCHAR(10), i INT);
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 4), ('key', 5);
Query OK, 3 rows affected (0.10 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    4 |
| key  |    5 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 5}           |
+----------------------+
1 row in set (0.00 sec)

mysql> DELETE FROM t;
Query OK, 3 rows affected (0.08 sec)

mysql> INSERT INTO t VALUES ('key', 3), ('key', 5), ('key', 4);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c, i FROM t;
+------+------+
| c    | i    |
+------+------+
| key  |    3 |
| key  |    5 |
| key  |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT JSON_OBJECTAGG(c, i) FROM t;
+----------------------+
| JSON_OBJECTAGG(c, i) |
+----------------------+
| {"key": 4}           |
+----------------------+
1 row in set (0.00 sec)

См. Нормализация, объединение и автоматическое оборачивание значений JSON для получения дополнительной информации и примеров.

Добавлено в MySQL 5.7.22.

MAX([DISTINCT] expr)

Возвращает максимальное значение expr. MAX() может принимать строковый аргумент; в таких случаях он возвращает максимальное строковое значение. См. Раздел 8.3.1, «Как MySQL использует индексы». Ключевое слово DISTINCT может использоваться для поиска максимума среди различных значений expr, однако это дает тот же результат, что и пропуск DISTINCT.

Если нет совпадающих строк, MAX() возвращает NULL.

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)

Возвращает минимальное значение expr. MIN() может принимать строковый аргумент; в таких случаях он возвращает минимальное строковое значение. См. Раздел 8.3.1, «Как MySQL использует индексы». Ключевое слово DISTINCT может использоваться для поиска минимума среди различных значений expr, однако это дает тот же результат, что и пропуск DISTINCT.

Если нет совпадающих строк, MIN() возвращает NULL.

mysql> SELECT student_name, MIN(test_score), MAX(test_score)
       FROM student
       GROUP BY student_name;

Для MIN(), MySQL в настоящее время сравнивает столбцы ENUM и SET по их строковому значению, а не по относительному положению строки в наборе. Это отличается от того, как ORDER BY сравнивает их.

STD(expr)

Возвращает генеральное стандартное отклонение expr. STD() является синонимом стандартной функции SQL STDDEV_POP(), предоставленной как расширение MySQL.

Если нет совпадающих строк, STD() возвращает NULL.

STDDEV(expr)

Возвращает генеральное стандартное отклонение expr. STDDEV() является синонимом стандартной функции SQL STDDEV_POP(), предоставленной для совместимости с Oracle.

Если нет совпадающих строк, STDDEV() возвращает NULL.

STDDEV_POP(expr)

Возвращает генеральное стандартное отклонение expr (квадратный корень из VAR_POP()). Вы также можете использовать STD() или STDDEV(), которые эквивалентны, но не являются стандартным SQL.

Если нет совпадающих строк, STDDEV_POP() возвращает NULL.

STDDEV_SAMP(expr)

Возвращает выборочное стандартное отклонение expr (квадратный корень из VAR_SAMP().

Если нет совпадающих строк, STDDEV_SAMP() возвращает NULL.

SUM([DISTINCT] expr)

Возвращает сумму expr. Если возвращаемый набор не содержит строк, SUM() возвращает NULL. Ключевое слово DISTINCT может использоваться для суммирования только различных значений expr.

Если нет совпадающих строк, SUM() возвращает NULL.

VAR_POP(expr)

Возвращает генеральную дисперсию expr. Она рассматривает строки как всю генеральную совокупность, а не как выборку, поэтому в знаменателе используется количество строк. Вы также можете использовать VARIANCE(), которая эквивалентна, но не является стандартным SQL.

Если нет совпадающих строк, VAR_POP() возвращает NULL.

VAR_SAMP(expr)

Возвращает выборочную дисперсию expr. То есть знаменатель равен количеству строк минус один.

Если нет совпадающих строк, VAR_SAMP() возвращает NULL.

VARIANCE(expr)

Возвращает генеральную дисперсию expr. VARIANCE() является синонимом стандартной функции SQL VAR_POP(), предоставленной как расширение MySQL.

Если нет совпадающих строк, VARIANCE() возвращает NULL.