Агрегатные функции SQL, GROUP BY, HAVING, а также агрегаты с DISTINCT и расширенные агрегаты — с возвращаемыми значениями и однострочными примерами на таблице orders.
Агрегатные функции сворачивают множество строк в один скалярный результат. GROUP BY разбивает строки по ключу до агрегации. HAVING фильтрует группы после агрегации. Входные значения NULL пропускаются всеми агрегатами, кроме COUNT(*).
Справочная таблица · 29 записи
Агрегатные функции SQLExplained
29 of 29 rows
Скалярные
Количество входных значений, отличных от NULL; COUNT(*) считает все строки.
SELECT COUNT(*) FROM orders
Сумма числовых значений, отличных от NULL. Возвращает NULL на пустом наборе.
SELECT SUM(total) FROM orders
Среднее арифметическое числовых значений, отличных от NULL. Значения NULL игнорируются.
SELECT AVG(total) FROM orders
Наименьшее значение, отличное от NULL, для любого сравнимого типа.
SELECT MIN(total) FROM orders
Наибольшее значение, отличное от NULL, для любого сравнимого типа.
SELECT MAX(total) FROM orders
Группировка
Разбивает входные строки по ключу; агрегаты считаются для каждого раздела.
SELECT user_id, SUM(total) FROM orders GROUP BY user_id
Предикат, вычисляемый для каждой группы после агрегации. Заменяет WHERE для агрегатов.
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5
Несколько агрегатов, вычисляемых для группы за один проход.
SELECT user_id, COUNT(*), SUM(total), AVG(total) FROM orders GROUP BY user_id
Вычисляет несколько группировок в одном операторе; ключи вне текущего набора возвращают NULL.
SELECT user_id, region, SUM(total) FROM orders GROUP BY GROUPING SETS ((user_id), (region), ())
Краткая форма иерархических наборов группировки; добавляет промежуточные итоги и общий итог.
SELECT region, user_id, SUM(total) FROM orders GROUP BY ROLLUP (region, user_id)
Краткая форма всех комбинаций перечисленных ключей как наборов группировки.
SELECT region, user_id, SUM(total) FROM orders GROUP BY CUBE (region, user_id)
Смешивает обычные ключи группировки с наборами группировки в одной конструкции.
SELECT user_id, region, SUM(total) FROM orders GROUP BY user_id, GROUPING SETS ((region), ())
Оставляет первую строку для каждого ключа в порядке сортировки. Расширение Postgres.
SELECT DISTINCT ON (user_id) user_id, total FROM orders ORDER BY user_id, created_at DESC
Уникальные и продвинутые
Количество уникальных значений, отличных от NULL. Сначала устраняет дубликаты.
SELECT COUNT(DISTINCT user_id) FROM orders
Оценивает количество уникальных значений с ограниченной погрешностью; создан для больших объёмов данных.
SELECT APPROX_COUNT_DISTINCT(user_id) FROM orders
Соединяет строки, отличные от NULL, через разделитель.
SELECT STRING_AGG(user_id::text, ',') FROM orders
Соединяет строки, отличные от NULL; имя STRING_AGG в MySQL.
SELECT GROUP_CONCAT(user_id SEPARATOR ',') FROM orders
Собирает входные значения, отличные от NULL, в массив.
SELECT ARRAY_AGG(total) FROM orders
Собирает входные строки в JSON-массив; форма JSONB хранит разобранные двоичные данные.
SELECT JSONB_AGG(user_id) FROM orders
TRUE, когда каждое / любое входное значение, отличное от NULL, истинно.
SELECT BOOL_AND(total > 0), BOOL_OR(total > 100) FROM orders
Стандартный псевдоним для BOOL_AND.
SELECT EVERY(total > 0) FROM orders
Интерполированный / ближайший по рангу перцентиль по отсортированному входу; требует WITHIN GROUP.
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total) FROM orders
Самое частое входное значение; агрегат упорядоченного множества в Postgres.
SELECT MODE() WITHIN GROUP (ORDER BY total) FROM orders
Передаёт порядок сортировки в агрегаты упорядоченных и гипотетических множеств.
SELECT RANK(500) WITHIN GROUP (ORDER BY total) FROM orders
Ограничивает агрегат строками, которые удовлетворяют условию.
SELECT COUNT(*) FILTER (WHERE total > 100) FROM orders
Сортирует значения до того, как агрегат их соединит или соберёт.
SELECT STRING_AGG(user_id::text, ',' ORDER BY user_id) FROM orders
COUNT(*) и COUNT(1) считают каждую строку; COUNT(col) пропускает значения NULL.
SELECT COUNT(*), COUNT(total), COUNT(1) FROM orders
Выборочное стандартное отклонение числовых значений, отличных от NULL. STDDEV_POP даёт форму по генеральной совокупности; поведение имени без суффикса различается по движкам.
SELECT STDDEV(total) FROM orders
Выборочная дисперсия числовых значений, отличных от NULL (квадрат STDDEV). VAR_SAMP и VAR_POP именуют каждую форму.