Группировка результатов — Аналитика на SQL

• Оператор GROUP BY
• Взаимосвязь между SELECT и GROUP BY
• Группировка по нескольким показателям
• Оператор HAVING
• Выводы

В этом уроке мы продолжим работать с данными из базы продаж и на этом примере рассмотрим важнейшую функцию для аналитика — группировку.

Эта функция позволяет объединить строки на основе определенного столбца или набора столбцов, а затем сгруппировать их в одну запись. Таким образом, мы сможем вычислить:

• Общее количество проданных товаров по каждой категории — так мы определим самые популярные категорий продуктов
• Среднюю цену продукта в каждой категории — найдем категории с самой высокой и низкой ценой
• Общее количество продаж в каждом магазине — найдем наиболее и наименее успешные магазины
• Общее количество продаж по каждому региону — определим региональные тенденции и паттерны

Оператор GROUP BY

Группировка в SQL выполняется с помощью оператора GROUP BY. При выполнении запроса с GROUP BY каждая строка результата является группой. Другими словами, она представляет одну или несколько строк с такими же значениями в одном или нескольких указанных столбцах.

Для примера сравним два запроса. Первый из них возвращает количество проданных товаров для магазинов с id = 3:

SELECT
    store_id,
    quantity
FROM sales
WHERE store_id = 3;

Второй запрос помещает все продажи из магазина с id = 3 в группу. Затем он возвращает информацию об этой группе — общее количество продаж, общую сумму продаж, минимальную и максимальную продажу:

SELECT
    store_id,
    COUNT(quantity),
    SUM(quantity),
    MIN(quantity),
    MAX(quantity)
FROM sales
WHERE store_id = 3
GROUP BY store_id;

Представим, что мы не знаем об операторе GROUP BY. В таком случае мы не смогли бы организовать группу магазинов с id = 3 в одну группу и получить информацию из второго запроса. Нам пришлось бы вручную анализировать строки для этого магазина.

В примере выше группа содержала всего две строки, поэтому проблем не возникло. Но если бы таких строк было сотни или тысячи, ситуация бы кардинально поменялась. Таким образом, группировка результатов помогает получить информацию о многих строках таблицы без необходимости проверять каждую из них по отдельности.

Взаимосвязь между SELECT и GROUP BY

При группировке важно понимать взаимосвязь между операторами SELECT и GROUP BY.

В работе с COUNT и другими агрегатными функциями важно не забывать, что любой элемент в списке SELECT должен быть частью группы, если он не является аргументом агрегатной функции.

Для примера попробуем записать выражение SELECT в таком виде:

SELECT
    store_id,
    COUNT(*) AS cnt
FROM sales;

Чтобы запрос сработал верно, нужно указать store_id в операторе GROUP BY:

SELECT
    store_id,
    COUNT(*) AS cnt
FROM sales
GROUP BY store_id;

В итоге мы получим такой результат:

Группировка по нескольким показателям

Группировка данных по нескольким показателям — это один из наиболее эффективных способов более глубокого анализа.

Она позволяет провести дополнительные исследования на разных уровнях детализации — например, сравнить разные комбинации и сегментировать данные по нескольким критериям. Группировка данных по нескольким показателям расширяет наши возможности и выявляет зависимости и тенденции, которые не видны при обычном анализе данных.

Допустим, мы хотим провести группировку по региону и городу, чтобы получить общее количество географических точек продаж наших товаров:

SELECT
    region,
    city,
    COUNT(*) AS cnt
FROM stores
GROUP BY region, city;

Результатом стал набор строк, содержащих уникальные комбинации региона и города, а также соответствующее количество точек продаж для каждой комбинации:

Оператор HAVING

Оператор HAVING используется для фильтрации данных на основе агрегатных функций после выполнения группировки с помощью оператора GROUP BY. В отличие от оператора WHERE, который фильтрует строки перед группировкой, оператор HAVING фильтрует группы после группировки.

Предположим, мы хотим найти категории товаров, у которых средняя цена превышает 50:

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

В этом запросе мы сначала проводим группировку по категории товаров с помощью оператора GROUP BY. Затем с помощью агрегатной функции AVG(price) мы вычисляем среднюю цену для каждой категории. В конце мы используем оператор HAVING, чтобы ограничить результаты только категориями со средней ценой выше 50.

Результатом будет набор строк с категориями товаров, у которых средняя цена больше 50.

Важно отметить разницу между операторами:

• HAVING работает с агрегатными функциями и фильтрует результаты группировки. Оператор HAVING проводит условное выражение на основе агрегатных функций и фильтрует группы данных
• WHERE проводит фильтрацию до группировки, то есть он фильтрует отдельные строки

Выводы

На этом уроке мы изучили группировки в SQL и узнали следующее:

• Оператор GROUP BY группирует строки на основе определенного столбца или набора столбцов, а затем объединяет их в одну запись
• Группировка позволяет проводить агрегацию и анализ на уровне групп, что дает более компактную и сводную информацию о данных
• Оператор GROUP BY работает вместе с агрегатными функциями COUNT, SUM, AVG. Так можно вычислять сумму, средние значения и другие статистические метрики внутри каждой группы
• Оператор HAVING фильтрует данные после выполнения группировки и позволяет применять условные выражения на агрегатные функции
• Оператор WHERE фильтрует строки до группировки, а HAVING — после нее

Далее мы научимся сортировать результаты фильтрации и группировки данных по определенным условиям.

Дополнительные материалы

1. GROUP BY