Погружение в GROUP BY PostgreSQL

Введение

«А сколько у нас товаров в каждой категории?», «Какая средняя цена у ноутбуков?», «Покажи мне только те разделы, где товаров больше двух». Эти вопросы — ежедневная рутина любого аналитика, работающего с PostgreSQL.

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

В этой статье мы не будем пересказывать сухую документацию. Мы возьмем датасет интернет-магазина с таблицами products (товары) и categories (категории) и пройдем путь от простого запроса к сложной аналитике.

Структура данных

Для понимания контекста представим, что у нас есть две таблицы:

1. categories: Иерархический справочник. Есть разделы «Электроника», «Одежда», «Книги», а внутри них — подкатегории: «Смартфоны», «Ноутбуки», «Телевизоры» и т.д.

2. products: Содержит product_id, product_name, price (цена закупки), category (внешний ключ), а также поле tags типа TEXT[] (массив тегов, например, {"apple", "флагман", "m2"}).

Блок 1: Базовый подсчет с COUNT и магия LEFT JOIN

Самый частый запрос — посчитать количество товаров. Но здесь кроется первый подводный камень: как не потерять пустые категории?

Запрос №1:

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: Это ключевой момент. Если использовать обычный INNER JOIN, категория «Телевизоры», в которой еще нет товаров, просто не попадет в итоговую выборку. LEFT JOIN гарантирует, что мы увидим все категории, даже с нулевым результатом.

Результат:

Вывод: Всегда используйте LEFT JOIN для справочников, если хотите видеть полную картину, а не только «активные» записи.

Блок 2: Множественная агрегация — Портрет цены

Одного количества часто мало. Руководителю нужно понимать: торгуем ли мы ширпотребом или премиум-товарами? Здесь в дело вступают агрегатные функции MIN, MAX, AVG и SUM.

Запрос №2:

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;

Нюансы этого запроса:

1. JOIN вместо LEFT JOIN: Здесь мы намеренно убрали пустые категории. Зачем считать среднюю цену там, где нет товаров? Это привело бы к NULL, что неинформативно.

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

Результат:

Инсайт: Самая высокая средняя цена у ноутбуков (почти 138 тысяч), но самая дорогая категория по суммарной стоимости запасов — Смартфоны (почти 200 тысяч).

Блок 3: Фильтрация групп — Оператор HAVING

Теперь самое интересное. Мы хотим отфильтровать не отдельные товары (для этого есть WHERE), а целые группы. Например, показать только те категории, где средний чек выше 20 000 рублей.

Для этого существует HAVING.

Запрос №3 (HAVING по цене):

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;

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

Еще один пример HAVING: Фильтрация по количеству

Покажем только те категории, в которых 2 и более товаров. Здесь снова используем LEFT JOIN (чтобы учесть все, включая нули), а потом отсечем лишнее через HAVING.

Запрос №4 (HAVING по COUNT):

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;

Важное правило SQL: В блоке HAVING нельзя использовать алиас (псевдоним) из SELECT. Нельзя написать HAVING количество_товаров >= 2. Нужно писать полное выражение: COUNT(p.product_id). Это связано с порядком выполнения операторов SQL.

Блок 4: Продвинутый уровень — GROUP BY и массивы (Теги)

В реальной жизни данные не всегда лежат в удобных плоских таблицах. Часто используется поле типа ARRAY. Предположим, у каждого товара есть набор тегов: tags TEXT[].

Как узнать, какие уникальные теги используются в каждой категории? Нам нужно «разобрать» массив на строки, сгруппировать и «собрать» обратно.

Запрос №5 (Магия с массивами):

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;

Разбор полетов:

1. CROSS JOIN LATERAL unnest(p.tags): Функция unnest превращает массив в столбец строк. Если у товара 4 тега, из одной строки получится 4. LATERAL позволяет сослаться на колонки из левой части запроса внутри функции.

2. COUNT(DISTINCT unnest): Считаем уникальные теги в рамках категории. Если в разных товарах есть тег «смартфон», он посчитается один раз.

3. ARRAY_AGG(DISTINCT unnest): Обратная операция. Собирает все уникальные теги обратно в единый массив для наглядного отображения.

Результат:

Вывод: GROUP BY отлично дружит с LATERAL и ARRAY_AGG. Это мощнейший инструмент для анализа неструктурированных или слабоструктурированных данных прямо внутри БД.

Блок 5: Бонус — GROUP BY против Оконных функций

Напоследок разберем частую путаницу. Когда использовать GROUP BY, а когда OVER()?

Сценарий А: 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;
-- Результат: 5 строк (по количеству категорий)

Сценарий Б: Оконная функция
Нужно сохранить детализацию (каждый товар), но добавить к нему информацию о среднем по категории.

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 категория, товар;
-- Результат: 11 строк (по количеству товаров)

Теперь мы видим, что iPhone 15 Pro стоит 89 990 руб., при том что средняя по смартфонам — 65 323 руб. Товар выше среднего.

Заключение

Сегодня мы на реальных примерах разобрали оператор GROUP BY в PostgreSQL. Мы научились:

1. Считать товары в категориях, не теряя пустые разделы.

2. Строить ценовую аналитику, используя MIN, MAX, AVG, SUM.

3. Фильтровать сгруппированные данные с помощью HAVING.

4. Работать с массивами тегов через UNNEST и ARRAY_AGG.

5. Понимать разницу между группировкой и оконными функциями.

Эти шесть запросов покрывают 90% потребностей в агрегации данных, с которыми вы столкнетесь в работе с интернет-магазинами, CRM-системами или финансовой отчетностью.

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