Как работает агрегатная функция GROUP BY?
GROUP BY в SQL — это ключевое слово, используемое в запросах для группировки строк с одинаковыми значениями в один набор, с последующим применением агрегатных функций к каждой из этих групп. Оно позволяет агрегировать данные по категориям или признакам, например: суммировать продажи по каждому клиенту, подсчитать количество заказов по странам, вычислить среднее значение по категориям и т.д.
Основы работы GROUP BY
Когда вы используете GROUP BY, SQL разбивает строки результирующего набора на группы, основываясь на значениях указанных столбцов. К каждой такой группе можно применить агрегатную функцию, такую как:
-
COUNT() — количество строк
-
SUM() — сумма значений
-
AVG() — среднее значение
-
MIN() — минимальное значение
-
MAX() — максимальное значение
-
STRING_AGG() (в некоторых СУБД) — объединение строк
Синтаксис
SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;
column1 — столбец, по которому происходит группировка.
AGG_FUNC(column2) — агрегатная функция, применяемая к другим столбцам в каждой группе.
Пример 1: Подсчёт заказов по каждому клиенту
SELECT CustomerID, COUNT(\*) AS OrderCount
FROM Orders
GROUP BY CustomerID;
Этот запрос покажет, сколько заказов сделал каждый клиент. Каждая группа создаётся по CustomerID, и COUNT(*) считает строки в каждой группе.
Пример 2: Сумма продаж по каждому продукту
SELECT ProductID, SUM(Quantity \* Price) AS TotalRevenue
FROM OrderDetails
GROUP BY ProductID;
Здесь каждая группа — это отдельный продукт, и для него вычисляется сумма всех продаж.
Поведение с несколькими столбцами
Можно группировать по нескольким столбцам:
SELECT Country, City, COUNT(\*) AS StoreCount
FROM Stores
GROUP BY Country, City;
Группы формируются по уникальной комбинации Country и City.
Использование с WHERE и HAVING
WHERE — фильтрует строки до группировки:
SELECT Department, AVG(Salary)
FROM Employees
WHERE HireDate >= '2020-01-01'
GROUP BY Department;
HAVING — фильтрует группы после группировки:
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
HAVING работает как WHERE, но для агрегатных функций.
Особенности GROUP BY:
-
Все столбцы, указанные в SELECT, должны быть либо:
-
перечислены в GROUP BY, либо
-
быть агрегатными функциями.
-
Пример недопустимого запроса:
SELECT Department, Name FROM Employees GROUP BY Department;
Исправленный вариант:
SELECT Department, MAX(Name) FROM Employees GROUP BY Department;
-
GROUP BY всегда выполняется после WHERE, но до HAVING и ORDER BY.
Порядок выполнения запроса:-
FROM
-
WHERE
-
GROUP BY
-
HAVING
-
SELECT
-
ORDER BY
-
Пример 3: Группировка и сортировка
SELECT CategoryID, AVG(Price) AS AvgPrice
FROM Products
GROUP BY CategoryID
ORDER BY AvgPrice DESC;
Сначала все продукты группируются по CategoryID, затем считается средняя цена в каждой категории, а потом результаты сортируются по убыванию этой средней цены.
Пример 4: Группировка по дате
SELECT DATE(OrderDate) AS OrderDay, COUNT(\*) AS OrdersPerDay
FROM Orders
GROUP BY DATE(OrderDate);
Если OrderDate содержит дату и время, то DATE(OrderDate) извлекает только дату — и группировка будет по дням.
Пример 5: Группировка с подзапросом
SELECT Department, MAX(AverageSalary) AS TopAverage
FROM (
SELECT Department, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY Department
) AS DeptAvg
GROUP BY Department;
Этот пример показывает, как можно использовать вложенную группировку — сначала вычисляется средняя зарплата по отделам, потом берётся максимум из них.
Использование с JOIN
SELECT c.Country, COUNT(o.OrderID) AS OrderCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Country;
Можно группировать данные после соединения нескольких таблиц.
Функции окон против GROUP BY
Иногда вместо GROUP BY можно использовать оконные функции (OVER()), чтобы получить агрегатную информацию, не группируя результат:
SELECT
EmployeeID,
Department,
Salary,
AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary
FROM Employees;
Здесь результат не агрегируется в группы, но для каждой строки показывается среднее по отделу.
GROUP BY — мощный инструмент для агрегирования данных в SQL. Он незаменим при создании отчётов, аналитике, подсчётах и анализе категорий. Его правильное использование требует понимания порядка выполнения SQL-запросов и работы агрегатных функций.