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

Агрегатная функция — это функция в системах управления базами данных (СУБД) и языках программирования, которая выполняет вычисления над набором строк (кортежей) и возвращает одно итоговое значение. Агрегатные функции являются фундаментальным инструментом для анализа данных, позволяя получать обобщённую статистическую информацию, такую как суммы, средние значения, количество записей, минимумы и максимумы, без необходимости извлекать и обрабатывать каждую строку отдельно. Они широко применяются в языке SQL (Structured Query Language), а также в табличных процессорах (например, Microsoft Excel) и библиотеках для обработки данных (например, Pandas в Python).

История

Концепция агрегатных функций возникла в 1970-х годах с развитием реляционных баз данных, предложенных Эдгаром Коддом. В первых реляционных СУБД, таких как System R (разработка IBM), были реализованы базовые функции для вычисления сумм, средних и подсчёта строк. Стандартизация SQL, начатая в 1986 году Американским национальным институтом стандартов (ANSI), закрепила набор основных агрегатных функций: COUNT, SUM, AVG, MIN, MAX. Последующие версии стандарта SQL (SQL:1999, SQL:2003, SQL:2016) расширили этот набор, добавив оконные функции, статистические функции (например, STDDEV, VARIANCE) и функции для работы с массивами и JSON. В России развитие агрегатных функций шло параллельно с внедрением реляционных СУБД, таких как Oracle Database, Microsoft SQL Server и PostgreSQL, которые стали основой для корпоративных информационных систем.

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

Агрегатные функции можно классифицировать по нескольким признакам.

По типу вычислений

• Числовые функции: SUM (сумма), AVG (среднее арифметическое), COUNT (количество строк), MIN (минимум), MAX (максимум). Эти функции работают с числовыми данными, хотя MIN и MAX могут применяться и к строкам (лексикографическое сравнение) и датам.
• Статистические функции: STDDEV (стандартное отклонение), VARIANCE (дисперсия), CORR (корреляция), REGR_SLOPE (наклон линии регрессии). Используются для статистического анализа.
• Строковые функции: в некоторых СУБД есть агрегатные функции для объединения строк, например, GROUP_CONCAT в MySQL или STRING_AGG в PostgreSQL.
• Функции для работы с датами: MIN и MAX для дат, а также специфические функции, например, FIRST и LAST в некоторых СУБД.

По способу применения

• Стандартные агрегатные функции: применяются ко всем строкам в группе или ко всей таблице. Пример: SELECT AVG(price) FROM products; — вычисляет среднюю цену всех товаров.
• Оконные (аналитические) функции: выполняют агрегацию с сохранением детализации строк, используя предложение OVER. Пример: SELECT id, price, AVG(price) OVER () AS avg_price FROM products; — возвращает среднюю цену для каждой строки, не группируя данные.
• Фильтрованные агрегатные функции: позволяют применять агрегацию только к строкам, удовлетворяющим условию, с помощью предложения FILTER (WHERE ...). Пример: SELECT AVG(price) FILTER (WHERE category = 'Electronics') FROM products;.

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

В языке SQL агрегатные функции используются в запросах с предложением SELECT и часто комбинируются с предложением GROUP BY для группировки строк по одному или нескольким столбцам. Основные правила:

• Агрегатные функции игнорируют значения NULL, за исключением COUNT(*), который подсчитывает все строки независимо от наличия NULL.
• Функция COUNT(column_name) подсчитывает только ненулевые значения в указанном столбце.
• SUM и AVG работают только с числовыми типами данных.
• MIN и MAX могут применяться к числам, строкам и датам.

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

1. Подсчёт количества записей:

``sql SELECT COUNT(*) AS total_employees FROM employees; `` Возвращает общее количество сотрудников.

1. Сумма продаж по категориям:

``sql SELECT category, SUM(amount) AS total_sales FROM sales GROUP BY category; `` Группирует продажи по категориям и вычисляет сумму для каждой.

1. Средняя зарплата по отделам:

``sql SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department; ``

1. Максимальная дата заказа для каждого клиента:

``sql SELECT customer_id, MAX(order_date) AS last_order FROM orders GROUP BY customer_id; ``

1. Фильтрация групп с помощью HAVING:

``sql SELECT department, COUNT() AS employee_count FROM employees GROUP BY department HAVING COUNT() > 10; `` Возвращает только те отделы, где работает более 10 сотрудников.

Применение в других системах

Табличные процессоры

В Microsoft Excel и Google Sheets агрегатные функции реализованы как встроенные формулы. Например, =SUM(A1:A10) вычисляет сумму значений в диапазоне, =AVERAGE(B1:B10) — среднее, =COUNT(C1:C10) — количество чисел. В Excel также доступны функции MIN, MAX, STDEV (стандартное отклонение) и другие. Сводные таблицы (Pivot Tables) используют агрегатные функции для группировки и обобщения данных.

Языки программирования

В Python библиотека Pandas предоставляет метод agg() для применения агрегатных функций к DataFrame. Пример: ``python import pandas as pd df = pd.DataFrame({'category': ['A', 'A', 'B'], 'value': [10, 20, 30]}) result = df.groupby('category')['value'].agg(['sum', 'mean', 'count']) `` Этот код группирует данные по категориям и вычисляет сумму, среднее и количество для каждой группы.

В R аналогичные функции доступны в пакетах dplyr и data.table. Например, summarise() в dplyr: ``r library(dplyr) df %>% group_by(category) %>% summarise(sum = sum(value), mean = mean(value)) ``

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

• Производительность: Агрегатные функции могут быть ресурсоёмкими при обработке больших объёмов данных, особенно если не используются индексы. Для ускорения запросов рекомендуется создавать индексы на столбцах, участвующих в GROUP BY и WHERE.
• NULL-значения: Как упоминалось, большинство агрегатных функций игнорируют NULL, что может привести к неожиданным результатам, если не учитывать это при проектировании запросов.
• Порядок выполнения: В SQL агрегатные функции применяются после предложения WHERE, но до HAVING и ORDER BY. Это означает, что фильтрация строк происходит до агрегации.
• Вложенные агрегатные функции: Стандарт SQL не допускает прямого вложения агрегатных функций (например, SUM(AVG(...))), но это можно обойти с помощью подзапросов или оконных функций.

Примеры в реальных задачах

• Бизнес-аналитика: вычисление среднего чека, общей выручки за период, количества активных клиентов.
• Научные исследования: расчёт средних значений показателей, стандартных отклонений, корреляций.
• Веб-аналитика: подсчёт количества посещений страниц, среднего времени на сайте, максимального числа просмотров.
• Финансы: суммирование транзакций, расчёт средневзвешенной цены, определение минимального и максимального курса валют.

Источники

• Стандарт ISO/IEC 9075:2016 (SQL:2016) — Information technology — Database languages — SQL.
• Эдгар Кодд. «A Relational Model of Data for Large Shared Data Banks» (1970).
• Документация PostgreSQL: «Aggregate Functions».
• Документация Microsoft SQL Server: «Aggregate Functions (Transact-SQL)».
• Документация MySQL: «Aggregate (GROUP BY) Functions».
• Документация Pandas: «pandas.DataFrame.agg».