Что такое агрегация в SQL?

Question

claude-haiku-4.5 · Accepted Answer

## Агрегация в SQL: объединение и обработка данных

**Агрегация в SQL** — это процесс объединения нескольких строк данных в одно значение путём применения агрегирующей функции (SUM, COUNT, AVG, MIN, MAX). Агрегация используется для получения сводной статистики по данным в таблице.

### Основные агрегирующие функции

**1. COUNT() — подсчёт строк**

```python
# Подсчитать количество пользователей
SELECT COUNT(*) FROM users;

# Результат: 1523 (всего пользователей)

# Подсчитать количество активных пользователей
SELECT COUNT(*) FROM users WHERE status = 'active';

# Результат: 1200

# Подсчитать уникальные города
SELECT COUNT(DISTINCT city) FROM users;

# Результат: 47
```

**2. SUM() — сумма значений**

```python
# Общая выручка
SELECT SUM(amount) FROM orders;

# Результат: 1500000.00

# Сумма продаж для конкретного продукта
SELECT SUM(quantity) FROM order_items WHERE product_id = 5;

# Результат: 320
```

**3. AVG() — среднее значение**

```python
# Средняя цена товара
SELECT AVG(price) FROM products;

# Результат: 299.99

# Средняя оценка фильма
SELECT AVG(rating) FROM reviews WHERE movie_id = 10;

# Результат: 8.5
```

**4. MIN() и MAX() — минимум и максимум**

```python
# Самый дешёвый товар
SELECT MIN(price) FROM products;

# Результат: 9.99

# Самый дорогой товар
SELECT MAX(price) FROM products;

# Результат: 9999.99

# Диапазон цен
SELECT MIN(price) as min_price, MAX(price) as max_price FROM products;

# Результат:
# min_price | max_price
# 9.99      | 9999.99
```

### GROUP BY — группировка данных

**GROUP BY** позволяет применить агрегирующую функцию к группам строк:

```python
# Продажи по каждому менеджеру
SELECT 
    manager_id,
    COUNT(*) as sale_count,
    SUM(amount) as total_sales
FROM sales
GROUP BY manager_id;

# Результат:
# manager_id | sale_count | total_sales
# 1          | 45         | 125000
# 2          | 38         | 98500
# 3          | 52         | 156700
```

**Сложный пример — агрегация по нескольким полям:**

```python
# Продажи по месяцам и категориям
SELECT 
    DATE_TRUNC('month', created_at) as month,
    category,
    COUNT(*) as orders,
    SUM(amount) as revenue,
    AVG(amount) as avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', created_at), category
ORDER BY month DESC, revenue DESC;

# Результат:
# month      | category   | orders | revenue | avg_order_value
# 2024-03-01 | Electronics| 234    | 125000  | 534.19
# 2024-03-01 | Clothing   | 456    | 89000   | 195.22
# 2024-02-01 | Electronics| 189    | 98500   | 521.16
```

### HAVING — фильтр для агрегированных данных

**HAVING** работает как WHERE, но применяется к агрегированным значениям:

```python
# Менеджеры с продажами > 100000
SELECT 
    manager_id,
    COUNT(*) as sales,
    SUM(amount) as total
FROM sales
GROUP BY manager_id
HAVING SUM(amount) > 100000;  # Фильтр на агрегат!

# Результат (только менеджеры с суммой > 100000):
# manager_id | sales | total
# 1          | 45    | 125000
# 3          | 52    | 156700
```

**Разница между WHERE и HAVING:**

```python
# WHERE — фильтрует строки ДО агрегации
SELECT manager_id, SUM(amount)
FROM sales
WHERE status = 'completed'  # Фильтруем строки
GROUP BY manager_id;

# HAVING — фильтрует строки ПОСЛЕ агрегации
SELECT manager_id, SUM(amount) as total
FROM sales
GROUP BY manager_id
HAVING SUM(amount) > 50000;  # Фильтруем результаты агрегации
```

### Практические примеры агрегации

**Пример 1: Анализ заказов**

```python
# Статистика по каждому клиенту
SELECT 
    customer_id,
    customer_name,
    COUNT(*) as order_count,
    SUM(total_amount) as lifetime_value,
    AVG(total_amount) as avg_order_value,
    MIN(order_date) as first_order,
    MAX(order_date) as last_order
FROM orders
GROUP BY customer_id, customer_name
HAVING COUNT(*) >= 5  # Минимум 5 заказов
ORDER BY lifetime_value DESC;
```

**Пример 2: Анализ эффективности сотрудников**

```python
# Рейтинг продавцов
SELECT 
    salesperson_id,
    salesperson_name,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(*) as total_transactions,
    SUM(amount) as total_sales,
    AVG(amount) as avg_sale,
    ROUND(SUM(amount) / COUNT(DISTINCT customer_id), 2) as revenue_per_customer
FROM transactions
WHERE YEAR(date) = 2024
GROUP BY salesperson_id, salesperson_name
ORDER BY total_sales DESC
LIMIT 10;
```

**Пример 3: Анализ продуктов**

```python
# Какие товары приносят больше всего прибыли
SELECT 
    product_id,
    product_name,
    COUNT(*) as times_sold,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total_revenue,
    SUM(quantity * (unit_price - cost_price)) as total_profit
FROM sales_items
GROUP BY product_id, product_name
HAVING SUM(quantity * (unit_price - cost_price)) > 10000
ORDER BY total_profit DESC;
```

### Оконные функции vs агрегация

**Агрегация (GROUP BY) — теряет детали:**

```python
SELECT manager_id, SUM(amount)
FROM sales
GROUP BY manager_id;

# Результат: 1 строка на менеджера
# manager_id | sum
# 1          | 125000
# 2          | 98500
```

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

```python
SELECT 
    manager_id,
    amount,
    SUM(amount) OVER (PARTITION BY manager_id) as manager_total,
    SUM(amount) OVER () as grand_total
FROM sales;

# Результат: каждая строка с добавленными агрегатами
# manager_id | amount | manager_total | grand_total
# 1          | 5000   | 125000        | 223500
# 1          | 7000   | 125000        | 223500
# 1          | 6000   | 125000        | 223500
# 2          | 4500   | 98500         | 223500
```

### Оптимизация агрегирующих запросов

```python
# Плохо — сканирует всю таблицу
SELECT COUNT(*) FROM huge_table;

# Лучше — использует индекс
SELECT COUNT(*) FROM huge_table WHERE status = 'active';
# И убедись, что есть индекс на status!
CREATE INDEX idx_status ON huge_table(status);

# Ещё лучше — используй приблизительное значение
SELECT reltuples FROM pg_class WHERE relname = 'huge_table';
# (Для PostgreSQL)
```

### Использование агрегации в Python (SQLAlchemy)

```python
from sqlalchemy import func
from sqlalchemy.orm import Session

# COUNT
count = session.query(func.count(User.id)).scalar()

# SUM
total_sales = session.query(func.sum(Order.amount)).scalar()

# GROUP BY
results = session.query(
    Manager.id,
    func.count(Sale.id).label('sale_count'),
    func.sum(Sale.amount).label('total_sales')
).join(Sale, Manager.id == Sale.manager_id).group_by(Manager.id).all()

# HAVING
results = session.query(
    Manager.id,
    func.sum(Sale.amount).label('total')
).join(Sale, Manager.id == Sale.manager_id).group_by(
    Manager.id
).having(func.sum(Sale.amount) > 100000).all()
```

### Вывод

**Агрегация в SQL** — это мощный инструмент для анализа данных. Основные моменты:
- **COUNT**, **SUM**, **AVG**, **MIN**, **MAX** — базовые функции
- **GROUP BY** — группировка по столбцам
- **HAVING** — фильтр агрегированных данных
- **WHERE** применяется до группировки, **HAVING** — после
- Оконные функции (OVER) сохраняют детальность данных

Применяй агрегацию для получения сводной статистики, аналитики и бизнес-метрик.

Что такое агрегация в SQL?

Комментарии (1)

Агрегация в SQL: объединение и обработка данных

Основные агрегирующие функции

GROUP BY — группировка данных

HAVING — фильтр для агрегированных данных

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

Оконные функции vs агрегация

Оптимизация агрегирующих запросов

Использование агрегации в Python (SQLAlchemy)

Вывод