Агрегация и группировка

Агрегация и группировка

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

Агрегатные функции

Агрегатные функции вычисляют одно значение из набора строк.

Примеры

-- Общее количество пользователей
SELECT COUNT(*) FROM users;

-- Количество активных пользователей
SELECT COUNT(*) FROM users WHERE is_active = true;

-- Сумма всех заказов
SELECT SUM(total) FROM orders;

-- Средняя сумма заказа
SELECT AVG(total) FROM orders;

-- Самый дорогой и самый дешёвый товар
SELECT MIN(price), MAX(price) FROM products;

-- Количество уникальных категорий товаров
SELECT COUNT(DISTINCT category) FROM products;

GROUP BY: группировка

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

Синтаксис

SELECT столбец_группировки, агрегат(столбец)
FROM таблица
WHERE условие          -- фильтрует строки ДО группировки
GROUP BY столбец_группировки
ORDER BY агрегат DESC;

Примеры

-- Количество заказов по каждому статусу
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- Результат:
-- status    | order_count
-- ----------+------------
-- pending   | 45
-- paid      | 120
-- cancelled | 23

-- Выручка по категориям товаров
SELECT category, SUM(price * stock) AS total_value
FROM products
GROUP BY category
ORDER BY total_value DESC;

-- Количество заказов и средняя сумма для каждого пользователя
SELECT user_id, COUNT(*) AS orders_count, AVG(total) AS avg_total
FROM orders
GROUP BY user_id
ORDER BY orders_count DESC;

HAVING: фильтрация групп

WHERE фильтрует строки до группировки. HAVING фильтрует группы после группировки.

-- НЕЛЬЗЯ использовать агрегат в WHERE:
SELECT status, COUNT(*) FROM orders
WHERE COUNT(*) > 10   -- ❌ Ошибка!
GROUP BY status;

-- ПРАВИЛЬНО: агрегат в HAVING:
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
HAVING COUNT(*) > 10;  -- ✅ Оставить только группы с >10 заказами

Практические примеры с HAVING

-- Пользователи, сделавшие более 3 заказов
SELECT user_id, COUNT(*) AS orders_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 3
ORDER BY orders_count DESC;

-- Категории товаров, где средняя цена выше 1000 рублей
SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 1000
ORDER BY avg_price DESC;

-- Поиск дублирующихся email (важно для тестов уникальности)
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Статусы заказов, встречающиеся редко (меньше 5 раз)
SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
HAVING COUNT(*) < 5;

WHERE vs HAVING: ключевое отличие

-- WHERE и HAVING вместе: сначала фильтр строк, потом фильтр групп
SELECT user_id, COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid'        -- сначала: только оплаченные заказы
GROUP BY user_id
HAVING COUNT(*) >= 5         -- потом: только те, у кого >= 5 таких заказов
ORDER BY paid_orders DESC;

Псевдонимы (AS)

AS даёт столбцу или выражению читаемое имя в результате.

SELECT
  status,
  COUNT(*) AS количество,
  SUM(total) AS выручка,
  AVG(total) AS средний_чек
FROM orders
GROUP BY status;

Практические задачи для тестировщика

-- Сколько заказов было создано сегодня?
SELECT COUNT(*) AS today_orders
FROM orders
WHERE created_at >= CURRENT_DATE;

-- Топ-5 пользователей по общей сумме заказов
SELECT user_id, SUM(total) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY user_id
ORDER BY total_spent DESC
LIMIT 5;

-- Распределение заказов по статусам (для проверки баланса)
SELECT status, COUNT(*) AS count,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS percent
FROM orders
GROUP BY status;

-- Есть ли дубликаты email? (тест уникальности поля)
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Итоги

• Агрегатные функции: COUNT, SUM, AVG, MIN, MAX — вычисляют итог по группе строк
• GROUP BY — разбивает строки на группы; агрегатные функции применяются к каждой группе
• HAVING — фильтрует группы после GROUP BY (WHERE фильтрует строки до)
• AS — псевдоним для столбца или выражения
• Порядок выполнения: WHERE → GROUP BY → HAVING → ORDER BY → LIMIT