Гайд по GROUP BY в PostgreSQL: 6 запросов к реальным данным

Сегодня продолжаем погружение в PostgreSQL и разбираем один из самых важных инструментов аналитика — оператор GROUP BY. В этой статье на реальных примерах мы научимся группировать данные, считать статистику по категориям, фильтровать группы и даже работать с массивами.

Для экспериментов у нас есть две таблицы: products (товары) и categories (категории). Категории имеют иерархию: есть родительские разделы («Электроника», «Одежда», «Книги»), а внутри — подкатегории вроде «Смартфоны» или «Ноутбуки».

1. Базовый подсчет: COUNT + LEFT JOIN

Самый частый вопрос: «Сколько товаров в каждой категории?». Важно увидеть даже пустые категории, поэтому используем LEFT JOIN.

SELECT
    c.categori_name AS категория,
    COUNT(p.product_id) AS количество_товаров
FROM categories c
LEFT JOIN products p ON c.categori_id = p.category
GROUP BY c.categori_name
ORDER BY количество_товаров DESC;

Что происходит:

• GROUP BY c.categori_name — группируем строки по названиям категорий.

• COUNT(p.product_id) — для каждой группы считаем количество товаров.

• LEFT JOIN — оставляет все категории, даже те, где товаров нет (в результате у них будет 0).

Результат: Мы сразу видим наполненность каталога. Категория «Телевизоры» пустая, но отобразилась благодаря LEFT JOIN.

2. Множественная агрегация: MIN, MAX, AVG, SUM

Количества часто мало. Давайте посмотрим на ценовую картину: минимальные, максимальные и средние цены, а также общую стоимость товаров в категории. Здесь нас интересуют только непустые категории, поэтому используем INNER JOIN.

SELECT
    c.categori_name AS категория,
    COUNT(p.product_id) AS количество_товаров,
    ROUND(MIN(p.price)::numeric, 2) AS минимальная_цена,
    ROUND(MAX(p.price)::numeric, 2) AS максимальная_цена,
    ROUND(AVG(p.price)::numeric, 2) AS средняя_цена,
    ROUND(SUM(p.price)::numeric, 2) AS общая_стоимость
FROM products p
JOIN categories c ON p.category = c.categori_id
GROUP BY c.categori_name
ORDER BY средняя_цена DESC;

Что нового:

• MIN, MAX, AVG, SUM — стандартные агрегатные функции.

• ROUND(...::numeric, 2) — приводим цену к числовому типу и округляем до двух знаков.

Результат: Самая высокая средняя цена — у ноутбуков, самая низкая — у книг. А вот по общей стоимости запасов лидируют смартфоны.

3. Фильтрация групп: HAVING

WHERE фильтрует строки до группировки. А если мы хотим отфильтровать уже готовые группы (например, показать только категории со средней ценой выше 20 000)? Для этого существует оператор HAVING.

Пример 1: Категории со средней ценой > 20 000

SELECT
    c.categori_name AS категория,
    COUNT(p.product_id) AS количество_товаров,
    ROUND(AVG(p.price)::numeric, 2) AS средняя_цена
FROM products p
JOIN categories c ON p.category = c.categori_id
GROUP BY c.categori_name
HAVING AVG(p.price) > 20000
ORDER BY средняя_цена DESC;

В результате остаются только «Ноутбуки» и «Смартфоны».

Пример 2: Категории с 2 и более товарами

SELECT
    c.categori_name AS категория,
    COUNT(p.product_id) AS количество_товаров
FROM categories c
LEFT JOIN products p ON c.categori_id = p.category
GROUP BY c.categori_name
HAVING COUNT(p.product_id) >= 2
ORDER BY количество_товаров DESC;

Важно: В HAVING нужно писать полное агрегатное выражение (COUNT(...)), а не алиас (количество_товаров).

4. Продвинутая работа с массивами: unnest и ARRAY_AGG

В таблице products есть поле tags — это массив текстовых тегов (например, {смартфон, apple, флагман}). Давайте развернем массивы и соберем статистику по тегам в категориях.

SELECT
    c.categori_name AS категория,
    COUNT(p.product_id) AS количество_товаров,
    COUNT(DISTINCT unnest) AS количество_уникальных_тегов,
    ARRAY_AGG(DISTINCT unnest) AS все_используемые_теги
FROM categories c
JOIN products p ON c.categori_id = p.category
CROSS JOIN LATERAL unnest(p.tags) AS unnest
WHERE p.tags IS NOT NULL AND array_length(p.tags, 1) > 0
GROUP BY c.categori_name
ORDER BY количество_уникальных_тегов DESC;

Магия запроса:

• CROSS JOIN LATERAL unnest(p.tags) — разворачивает массив каждого товара в отдельные строки (один тег — одна строка).

• COUNT(DISTINCT unnest) — считаем уникальные теги в пределах категории.

• ARRAY_AGG(DISTINCT unnest) — собирает все уникальные теги категории обратно в массив для наглядности.

Результат: Мы видим, что в «Ноутбуках» используется 5 уникальных тегов, и вот они все: {apple, m2, ноутбук, ультрабук,...}.

5. GROUP BY vs Оконные функции

Начинающие часто путают группировку и оконные функции. Главное отличие: GROUP BY схлопывает строки, а оконные функции — нет.

GROUP BY (агрегация):

SELECT
    c.categori_name AS категория,
    AVG(p.price) AS средняя_цена_по_категории
FROM products p
JOIN categories c ON p.category = c.categori_id
GROUP BY c.categori_name;

Результат: одна строка на категорию.

Оконная функция (сохраняет детали):

SELECT
    p.product_name AS товар,
    c.categori_name AS категория,
    p.price AS цена_товара,
    ROUND(AVG(p.price) OVER(PARTITION BY c.categori_id)::numeric, 2) AS средняя_цена_в_категории
FROM products p
JOIN categories c ON p.category = c.categori_id
ORDER BY категория, товар;

Результат: каждая строка — отдельный товар, но рядом с ним появилась колонка со средней ценой по его категории. Это позволяет сравнить товар со средними показателями в группе.

Итог

Мы разобрали 5 ключевых сценариев использования GROUP BY:

1. Базовый подсчет строк.

2. Множественные агрегаты (min, max, avg).

3. Фильтрация групп через HAVING.

4. Работа с массивами через unnest.

5. Отличие группировки от оконных функций.

GROUP BY — это не страшно. Это просто способ сказать базе данных: «Сгруппируй данные по этому признаку и посчитай для каждой группы».

Экспериментируйте с данными и пишите свои запросы! Если остались вопросы — задавайте в комментариях.

Статья подготовлена по материалам обучающего видео для вашего удобства.