Агрегатные функции в SQL используются для выполнения вычислений над набором значений и возвращают одно результирующее значение. Эти функции полезны при анализе данных и помогают обобщить информацию, например, подсчитать количество строк, вычислить среднее значение, найти максимальное или минимальное значение и так далее.
Основные агрегатные функции:
- COUNT() — возвращает количество строк в наборе данных.
- SUM() — вычисляет сумму значений в столбце.
- AVG() — находит среднее значение.
- MIN() — возвращает минимальное значение.
- MAX() — возвращает максимальное значение.
Описание основных агрегатных функций:
1. COUNT()
Функция COUNT() возвращает количество строк в наборе данных. Она может подсчитывать как все строки (включая дубликаты и NULL), так и только определённые значения.
- Подсчет всех строк:
В этом примере будут подсчитаны все строки таблицы employees, включая строки с NULL.
- Подсчет строк, где значение не NULL:
Здесь будут подсчитаны только строки, где поле salary не равно NULL.
2. SUM()
Функция SUM() вычисляет сумму значений в указанном столбце. Используется для работы с числовыми значениями.
- Пример:
- В данном случае будет возвращена сумма всех зарплат сотрудников.
3. AVG()
Функция AVG() вычисляет среднее значение для набора числовых данных.
- Пример:
Этот запрос вернет среднюю зарплату всех сотрудников.
4. MIN()
Функция MIN() возвращает минимальное значение в указанном столбце.
- Пример:
- Этот запрос вернет минимальную зарплату среди всех сотрудников.
5. MAX()
Функция MAX() возвращает максимальное значение в указанном столбце.
- Пример:
- Этот запрос вернет максимальную зарплату среди всех сотрудников.
Использование с оператором GROUP BY
Агрегатные функции особенно полезны в комбинации с оператором GROUP BY, который группирует строки по значению одного или нескольких столбцов. Это позволяет выполнять агрегатные операции для каждой группы.
- Пример: Найти среднюю зарплату по каждому отделу:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
Здесь строки будут сгруппированы по полю department_id, и для каждой группы будет вычислено среднее значение зарплаты.
Фильтрация результатов с помощью HAVING
Оператор HAVING используется для фильтрации результатов агрегатных функций. В отличие от WHERE, который фильтрует строки до выполнения группировки, HAVING фильтрует уже агрегированные данные.
- Пример: Найти отделы, где средняя зарплата больше 5000:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
Примечания:
1. NULL и агрегатные функции: Агрегатные функции (кроме COUNT(*)) игнорируют значения NULL. Например, если в столбце есть значения NULL, они не будут учитываться при подсчете суммы, среднем значении и других вычислениях.
2. Совместное использование нескольких агрегатных функций: В одном запросе можно использовать сразу несколько агрегатных функций:
SELECT department_id, COUNT(*), AVG(salary), MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;
Примеры дополнительных агрегатных функций:
- VARIANCE() — вычисляет дисперсию значений.
- STDDEV() — вычисляет стандартное отклонение.
- GROUP_CONCAT() (MySQL) — объединяет значения из группы в одну строку.
Пример использования GROUP_CONCAT():
SELECT department_id, GROUP_CONCAT(employee_name)
FROM employees
GROUP BY department_id;
Этот запрос вернет строку с именами сотрудников для каждого отдела, объединенными через запятую.
Заключение
Агрегатные функции — это мощный инструмент для работы с данными в SQL. Они позволяют быстро обобщать информацию, вычислять ключевые метрики и анализировать данные на высоком уровне. Комбинируя агрегатные функции с GROUP BY и HAVING, можно строить сложные запросы для анализа данных и получения полезных бизнес-отчетов.
Вместо оглавления. Что вы найдете на канале QA Helper - справочник тестировщика?
Не забудьте подписаться на канал, чтобы не пропустить полезную информацию: QA Helper - справочник тестировщика
Пишите в комментариях какой пункт было бы интересно рассмотреть более подробно.
Обязательно прочитайте: Что должен знать и уметь тестировщик
Также будет интересно почитать: Вопросы которые задают на собеседовании тестировщикам