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, «Концепции и синтаксис функций окон».