Агрегатные функции

Агрегатные функции — это функции в системах управления базами данных (СУБД) и языках программирования, которые выполняют вычисления над набором значений (группой строк) и возвращают одно итоговое значение. Они являются фундаментальным инструментом для анализа данных, позволяя получать обобщённую информацию из таблиц, такую как суммы, средние значения, количества, минимумы и максимумы. В отличие от скалярных функций, которые обрабатывают каждую строку отдельно, агрегатные функции работают с группами строк, объединённых по определённому критерию.

Основные характеристики

Агрегатные функции обычно применяются в запросах к базам данных, чаще всего в сочетании с оператором SELECT и конструкцией GROUP BY. Ключевой особенностью является то, что они игнорируют значения NULL при вычислениях, за исключением функции COUNT(*), которая подсчитывает все строки, включая те, которые содержат NULL. Результат агрегатной функции — это всегда одно значение для каждой группы или для всего набора данных.

Агрегатные функции могут быть встроенными (предопределёнными в СУБД) или пользовательскими (созданными разработчиком). Стандарт SQL (ISO/IEC 9075) определяет набор базовых агрегатных функций, которые поддерживаются большинством реляционных СУБД.

Классификация

Стандартные агрегатные функции SQL

К наиболее распространённым встроенным агрегатным функциям относятся:

COUNT() — возвращает количество строк в группе. COUNT(*) подсчитывает все строки, включая дубликаты и NULL; COUNT(column) подсчитывает только строки, где значение в указанном столбце не равно NULL; COUNT(DISTINCT column) подсчитывает количество уникальных не-NULL значений.
SUM() — возвращает сумму всех значений в группе. Применяется только к числовым типам данных.
AVG() — возвращает среднее арифметическое значение в группе. Также работает только с числами. Игнорирует NULL.
MIN() — возвращает минимальное значение в группе. Может применяться к числам, строкам и датам.
MAX() — возвращает максимальное значение в группе. Аналогично MIN().

Специализированные и расширенные функции

Многие СУБД (например, PostgreSQL, Oracle, Microsoft SQL Server, MySQL) добавляют собственные агрегатные функции, расширяющие стандартные возможности:

Статистические функции: STDDEV() (среднеквадратическое отклонение), VARIANCE() (дисперсия), CORR() (корреляция), REGR_*() (функции регрессионного анализа).
Функции для работы со строками: STRING_AGG() (в PostgreSQL, SQL Server) или GROUP_CONCAT() (в MySQL) — объединяют строковые значения из группы в одну строку с разделителем.
Функции для работы с массивами: ARRAY_AGG() (в PostgreSQL) — собирает значения в массив.
Функции для ранжирования и оконных вычислений: хотя они часто выделяются в отдельную категорию оконных функций, некоторые из них (например, ROW_NUMBER(), RANK(), DENSE_RANK()) могут использоваться в агрегирующем контексте, но их основное назначение — присвоение номеров строкам в группе, а не вычисление одного значения.
Пользовательские агрегатные функции (UDA): создаются разработчиками для решения специфических задач, например, для вычисления моды, медианы или сложных финансовых показателей.

Применение в SQL

Агрегатные функции используются в запросах SELECT для получения сводной информации. Основные правила их применения:

Без GROUP BY: Если агрегатная функция используется без GROUP BY, она обрабатывает все строки таблицы, возвращая одно итоговое значение.

``sql SELECT AVG(price) AS average_price FROM products; ``

С GROUP BY: Функция применяется к каждой группе, образованной значениями столбца, указанного в GROUP BY.

``sql SELECT category, COUNT(*) AS product_count, AVG(price) AS avg_price FROM products GROUP BY category; ``

С HAVING: Условие HAVING используется для фильтрации групп после применения агрегатных функций (аналог WHERE для групп).

``sql SELECT category, AVG(price) AS avg_price FROM products GROUP BY category HAVING AVG(price) > 100; ``

С DISTINCT: Многие агрегатные функции поддерживают ключевое слово DISTINCT внутри себя для учёта только уникальных значений.

``sql SELECT COUNT(DISTINCT city) AS unique_cities FROM customers; ``

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

``sql SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); ``

Примеры использования

Рассмотрим таблицу employees со столбцами id, name, department, salary.

Подсчёт сотрудников в каждом отделе:

``sql SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department; ``

Средняя зарплата по компании:

``sql SELECT AVG(salary) AS average_salary FROM employees; ``

Максимальная и минимальная зарплата в отделе 'IT':

``sql SELECT MAX(salary) AS max_salary, MIN(salary) AS min_salary FROM employees WHERE department = 'IT'; ``

Сумма зарплат в отделах, где работает более 3 сотрудников:

``sql SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING COUNT(*) > 3; ``

Ограничения и особенности

Нельзя смешивать: В одном запросе SELECT без GROUP BY нельзя смешивать агрегатные функции и обычные столбцы, если только эти столбцы не являются частью GROUP BY. Например, запрос SELECT name, AVG(salary) FROM employees; вызовет ошибку в большинстве СУБД, так как name не агрегирован и не указан в GROUP BY.
Производительность: Агрегатные функции, особенно на больших таблицах, могут быть ресурсоёмкими. Для ускорения работы рекомендуется использовать индексы на столбцах, участвующих в GROUP BY и WHERE.
Обработка NULL: Как упоминалось, большинство агрегатных функций игнорируют NULL. Это важно учитывать при интерпретации результатов, особенно для AVG() и COUNT(column).
Сортировка: Результат запроса с GROUP BY не гарантирует определённого порядка строк. Для сортировки используется ORDER BY, который может применяться как к агрегатным функциям, так и к столбцам группировки.