1. Синтаксис

aggregate-function-invocation: hide

Агрегатные функции, показанные ниже, доступны по умолчанию. Есть еще две совокупности, сгруппированные с функциями JSON SQL. Приложения могут определить свои агрегатные функции, используя sqlite3_create_function() API.

В любой агрегатной функции, которая берет отдельный аргумент, тому аргументу может предшествовать ключевое слово DISTINCT. В таких случаях двойные элементы фильтрованы прежде чем быть переданными в агрегатную функцию. Например, функция "count(distinct X)" возвратит количество отличных значений колонки X вместо общего количества ненулевых значений в колонке X.

Если указано FILTER, то только строки, для которых expr = true, включены в совокупность.

Если указано ORDER BY, то пункт определяет порядок, в котором обрабатываются входы к совокупности. Для таких агрегатных функций как max() и count(), не имеет значения входной порядок. Но для таких вещей, как string_agg() и json_group_object(), ORDER BY будет иметь значение в результате. Если никакой пункт ORDER BY не определяется, входы к совокупности происходят в произвольном порядке, который мог бы измениться между вызовами.

См. также: scalar functions и window functions.

2. Список встроенных агрегатных функций

3. Описания встроенных агрегатных функций

avg(X)

avg() возвращает среднее значение всех не-NULL X в группе. Последовательности и значения BLOB, которые не похожи на числа, интерпретируются как 0. Результатом avg() всегда является значение с плавающей точкой каждый раз, когда есть по крайней мере один вход не-NULL, даже если все входы это целые числа. Результатом avg() будет NULL при отсутствии входов не-NULL. Результат avg() вычисляется как total()/ count(), так что все ограничения, которые относятся к total(), также применимы к avg().

count(X)
count(*)

count(X) возвращает количество раз, которое X не NULL в группе. count(*) (без аргументов) возвращает общее количество строк в группе.

group_concat(X)
group_concat(X,Y)
string_agg(X,Y)

group_concat() возвращает последовательность, которая является связью всех значений не-NULL в X. Если Y присутствует, тогда он используется в качестве сепаратора между экземплярами X. Если Y не задан, используется запятая (",").

string_agg(X,Y) это псевдоним для group_concat(X,Y). String_agg() совместим с PostgreSQL и SQL Server, group_concat() совместим с MySQL.

Порядок связанных элементов произволен, если аргумент ORDER BY немедленно не включен после последнего параметра.

max(X)

max() возвращает максимальное значение из всех в группе. Максимальное значение это значение, которое было бы возвращено последним в ORDER BY на той же самой колонке. Совокупность max() вернет NULL если и только если отсутствуют значения не-NULL в группе.

min(X)

min() возвращает минимальное значение не-NULL из всех значений в группе. Минимальное значение это первое значение не-NULL, которое появилось бы в ORDER BY колонки. min() возвращает NULL, если и только если при отсутствии значений не-NULL в группе.

sum(X)
total(X)

sum() и total() возвращает сумму всех не-NULL значений в группе. Если нет никаких входных строк не-NULL, sum() вернет NULL, но total() вернет 0.0. NULL обычно не полезный результат для суммы строк, но стандарт SQL требует его и большая часть других СУБД SQL работает именно так. Таким образом, SQLite делает это таким же образом, чтобы быть совместимым. Нестандартная функция total() обеспечивается как удобный способ работать вокруг этой проблемы проектирования на языке SQL.

Результатом total() всегда является значение с плавающей точкой. Результатом sum() является целочисленное значение, если все входы не-NULL это целые числа. Если какой-либо вход sum() не является ни целым числом, ни NULL, то sum() вернет значение с плавающей точкой, которое является приближением математической суммы.

Sum() бросит исключение "integer overflow", если все входы будут целыми числами или NULL, и целочисленное переполнение происходит в любом пункте во время вычисления. Никакая ошибка переполнения никогда не поднимается, если какой-либо предшествующий вход был значением с плавающей точкой. Total() никогда не бросает целочисленное переполнение.

Суммируя значения с плавающей запятой, если величины значений сильно отличаются, тогда получающаяся сумма могла бы быть неточной вследствие того, что значения с плавающей точкой IEEE 754 является приближениями. Используйте decimal_sum(X) в десятичном расширении, чтобы получить точное суммирование чисел с плавающей точкой. Рассмотрите этот тестовый сценарий:

CREATE TABLE t1(x REAL);
INSERT INTO t1 VALUES(1.55e+308),(1.23),(3.2e-16),(-1.23),(-1.55e308);
SELECT sum(x), decimal_sum(x) FROM t1;

Большие значения ±1.55e+308 уравновешивают друг друга, но отмена не происходит до конца суммы и тем временем большое +1.55e+308 перевешивает маленькое значение 3.2e-16. Конечный результат это неточный результат sum(). decimal_sum() производит точный ответ, за счет дополнительного использования CPU и памяти. Отметьте также, что decimal_sum() не встроен в ядро SQLite, это загружаемое расширение.

Если сумма входов слишком большая, чтобы представлять как значение с плавающей точкой IEEE 754, то вернется +Infinity или -Infinity. Если очень большие значения с отличающимися знаками используются таким образом, что SUM() или TOTAL() неспособна определить, является ли правильным результатом +Infinity или -Infinity или некоторое другое промежуточное значение, то результат NULL. Следовательно, например, следующий запрос возвращает NULL:

WITH t1(x) AS (VALUES(1.0),(-9e+999),(2.0),(+9e+999),(3.0))
SELECT sum(x) FROM t1;