Как связать агрегатные функции и GROUP BY

Агрегатные функции и GROUP BY: подробное руководство

Агрегатные функции и GROUP BY — это фундамент для работы с данными на SQL. Разберу механику и покажу, как их правильно связывать.

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

Это функции, которые обрабатывают множество строк и возвращают одно значение:

-- COUNT, SUM, AVG, MIN, MAX
SELECT 
  COUNT(*) as total_orders,      -- количество строк
  SUM(amount) as total_revenue,  -- сумма
  AVG(amount) as avg_order,      -- среднее
  MIN(amount) as min_order,      -- минимум
  MAX(amount) as max_order       -- максимум
FROM orders;

Это вернет одну строку с результатом для всей таблицы.

Зачем нужен GROUP BY

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

SELECT 
  customer_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent,
  AVG(amount) as avg_order
FROM orders
GROUP BY customer_id;

Теперь результат — одна строка на каждого клиента.

Правило: выбирайте только агрегированные столбцы или группирующие

Важное правило: в SELECT можно использовать только:

1. Столбцы, по которым группируем (GROUP BY)
2. Агрегатные функции

-- ПРАВИЛЬНО
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

-- НЕПРАВИЛЬНО (будет ошибка в PostgreSQL)
SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id;
-- order_date не в GROUP BY, Error!

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

WHERE фильтрует строки ДО группировки, HAVING фильтрует группы ПОСЛЕ:

SELECT 
  customer_id,
  COUNT(*) as order_count,
  SUM(amount) as total_spent
FROM orders
WHERE amount > 100  -- до группировки
GROUP BY customer_id
HAVING COUNT(*) > 5; -- после группировки (только группы с 5+ заказами)

Пример из реальной практики: Анализ продаж

SELECT 
  p.category,
  p.product_id,
  COUNT(o.id) as sales_count,
  SUM(o.amount) as total_revenue,
  AVG(o.amount) as avg_price,
  MIN(o.created_at) as first_sale,
  MAX(o.created_at) as last_sale
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
WHERE o.created_at >= '2025-01-01'
GROUP BY p.category, p.product_id
HAVING SUM(o.amount) > 10000
ORDER BY total_revenue DESC;

На Java с ORM

В JPA/Hibernate это выглядит так:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
  // Native query для сложной аналитики
  @Query("""
    SELECT new com.example.OrderStats(
      o.customerId,
      COUNT(o.id),
      SUM(o.amount),
      AVG(o.amount)
    )
    FROM Order o
    WHERE o.createdAt >= :startDate
    GROUP BY o.customerId
    HAVING SUM(o.amount) > :minRevenue
    ORDER BY SUM(o.amount) DESC
  """)
  List<OrderStats> getCustomerStats(
    @Param("startDate") LocalDate start,
    @Param("minRevenue") BigDecimal minRev
  );
}

Распространенные ошибки

1. Забыли столбец в GROUP BY

   -- ОШИБКА
   SELECT user_id, email, COUNT(*) FROM users GROUP BY user_id;
   -- email не в GROUP BY!
   

2. Используете агрегатную функцию в WHERE

   -- ОШИБКА
   SELECT * FROM orders WHERE COUNT(*) > 5;
   -- Используй HAVING!
   

3. Забыли GROUP BY совсем

   -- ОШИБКА
   SELECT customer_id, COUNT(*) FROM orders;
   -- Нужен GROUP BY customer_id
   

Порядок выполнения SQL

Важно понимать последовательность:

1. FROM — выбор таблиц
2. WHERE — фильтр строк
3. GROUP BY — группировка
4. HAVING — фильтр групп
5. SELECT — выбор столбцов
6. ORDER BY — сортировка
7. LIMIT — ограничение

Это объясняет, почему HAVING работает, а WHERE с агрегатом нет!

Производительность

Для больших таблиц:

• Используй индексы на столбцах GROUP BY
• Фильтруй WHERE как можно раньше
• Избегай GROUP BY на TEXT полях, лучше ID
• Для сложной аналитики рассмотри materialized views