AVG([DISTINCT] expr) [over_clause]

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

Если нет совпадающих строк, AVG() возвращает NULL. Функция также возвращает NULL, если expr является NULL.

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окна»; она не может использоваться с DISTINCT.

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

BIT_AND(expr) [over_clause]

Возвращает побитовое AND всех битов в expr.

Тип результата зависит от того, оцениваются ли значения аргументов функции как двоичные строки или числа:

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

Значения NULL не влияют на результат, если все значения являются NULL. В этом случае результат — нейтральное значение той же длины, что и значения аргументов.

Более подробную информацию о оценке аргументов и типах результатов см. во вводной части Раздела 14.12, «Функции и операторы для битовых операций».

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окна».

BIT_OR(expr) [over_clause]

Возвращает побитовое OR всех битов в expr.

Тип результата зависит от того, оцениваются ли значения аргументов функции как двоичные строки или числа:

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

Значения NULL не влияют на результат, если все значения являются NULL. В этом случае результат — нейтральное значение той же длины, что и значения аргументов.

Более подробную информацию о оценке аргументов и типах результатов см. во вводной части Раздела 14.12, «Функции и операторы для битовых операций».

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окна».

BIT_XOR(expr) [over_clause]

Возвращает побитовое XOR всех битов в expr.

Тип результата зависит от того, оцениваются ли значения аргументов функции как двоичные строки или числа:

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

Значения NULL не влияют на результат, если все значения являются NULL. В этом случае результат — нейтральное значение той же длины, что и значения аргументов.

Более подробную информацию о оценке аргументов и типах результатов см. во вводной части Раздела 14.12, «Функции и операторы для битовых операций».

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окна».

Подсчёт значений

Возвращает количество ненулевых значений столбца expr в строках, полученных с помощью оператора выбора. Результат — значение типа целого числа.

Если сопоставляющих строк нет, подсчёт значений возвращает 0. COUNT(NULL) возвращает 0.

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в разделе 14.20.2, «Концепции и синтаксис функций окна».

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

Функция подсчёта строк отличается тем, что она возвращает количество полученных строк, содержат ли они NULL значения или нет.

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

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

Производительность запросов 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(*) оптимизирована для очень быстрого возврата, если оператор выбора получает данные из одной таблицы, не выбираются другие столбцы и нет условия WHERE. Например:

mysql> SELECT COUNT(*) FROM student;

Эта оптимизация применяется только к таблицам MyISAM, так как для этого движка хранилища сохраняется точное количество строк и к нему можно получить очень быстрый доступ. COUNT(1) подвергается той же оптимизации только в том случае, если первый столбец определен как NOT NULL.

Подсчёт различных значений

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

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

mysql> SELECT COUNT(DISTINCT results) FROM student;

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

GROUP_CONCAT(expr)

Эта функция возвращает строку, содержащую конкатенированные ненулевые значения из группы. Возвращает 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. Дополнительную информацию об этом параметре см. в разделе 6.5.1, «mysql — Клиент MySQL командной строки».

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

JSON_ARRAYAGG(col_or_expr) [over_clause]

Объединяет результат набора строк в одном массиве JSON, элементы которого состоят из строк. Порядок элементов в массиве не определён. Функция применяется к столбцу или выражению, вычисляющему одно значение. Возвращает NULL, если результат не содержит строк или при ошибке. Если col_or_expr — NULL, функция возвращает массив элементов JSON [null].

Эта функция выполняется как функция окна, если over_clause присутствует. over_clause описана в разделе 14.20.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_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)

JSON_OBJECTAGG(key, value) [over_clause]

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.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, которая не допускает дублирующих ключей, в возвращаемом объекте используется только последнее встреченное значение с данным ключом (“последний дублирующий ключ берется”). Это означает, что результат применения этой функции к столбцам из SELECT может зависеть от порядка возвращения строк, который не гарантируется.

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

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

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)

Выбранный ключ из последнего запроса является недетерминированным. Если запрос не использует GROUP BY (что обычно налагает свой собственный порядок независимо) и вы предпочитаете определённый порядок ключей, вы можете вызвать JSON_OBJECTAGG() как функцию окна, включив в запрос OVER-оператор со спецификацией ORDER BY, чтобы наложить определённый порядок на строки фрейма. Следующие примеры показывают, что происходит с ORDER BY и без него для нескольких разных спецификаций фреймов.

Без ORDER BY фрейм охватывает всю партицию:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER () AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 4}  |
| {"key": 4}  |
| {"key": 4}  |
+-------------+

С ORDER BY, где фрейм по умолчанию — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (в порядке возрастания и убывания):

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
| {"key": 4}  |
| {"key": 5}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 4}  |
| {"key": 3}  |
+-------------+

С ORDER BY и явным фреймом всей партиции:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
        AS json_object
       FROM t;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
| {"key": 5}  |
| {"key": 5}  |
+-------------+

Чтобы вернуть конкретное значение ключа (например, наименьшее или наибольшее), включите LIMIT-оператор в соответствующий запрос. Например:

mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 3}  |
+-------------+
mysql> SELECT JSON_OBJECTAGG(c, i)
       OVER (ORDER BY i DESC) AS json_object FROM t LIMIT 1;
+-------------+
| json_object |
+-------------+
| {"key": 5}  |
+-------------+

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

MAX([DISTINCT] expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Понятие и синтаксис функций окна»; она не может быть использована с 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) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Понятие и синтаксис функций окна»; она не может быть использована с DISTINCT.

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

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

STD(expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Понятие и синтаксис функций окна».

SUM([DISTINCT] expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окон»; она не может использоваться с DISTINCT.

VAR_POP(expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окон».

VAR_SAMP(expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окон».

VARIANCE(expr) [over_clause]

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

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

Эта функция выполняется как функция окна, если присутствует over_clause. over_clause описана в Разделе 14.20.2, «Концепции и синтаксис функций окон».