Что такое агрегатные функции в SQL? Приведите примеры.?

Question

claude-haiku-4.5 · Accepted Answer

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

Агрегатные функции (Aggregate Functions) — это функции SQL, которые выполняют вычисления над набором строк и возвращают одно значение. Они используются для получения статистики и сводок по данным.

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

#### 1. COUNT — подсчёт количества строк

**Синтаксис:**
```sql
COUNT(столбец) — подсчитывает ненулевые значения
COUNT(*) — подсчитывает все строки (включая NULL)
COUNT(DISTINCT столбец) — подсчитывает уникальные значения
```

**Примеры:**

```sql
-- Сколько всего сотрудников?
SELECT COUNT(*) 
FROM employees;
-- Результат: 150

-- Сколько сотрудников имеют опыт (не NULL)?
SELECT COUNT(experience_years) 
FROM employees;
-- Результат: 145 (5 записей имеют NULL)

-- Сколько разных отделов?
SELECT COUNT(DISTINCT department_id) 
FROM employees;
-- Результат: 12

-- Сколько заказов по каждому клиенту?
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id;
```

**Результат:**
```
customer_id  order_count
1            5
2            12
3            3
...
```

#### 2. SUM — сумма значений

**Синтаксис:**
```sql
SUM(столбец) — суммирует все значения (NULL игнорируются)
SUM(DISTINCT столбец) — сумма уникальных значений
```

**Примеры:**

```sql
-- Общая сумма продаж
SELECT SUM(amount) as total_sales
FROM orders;
-- Результат: 1234567.89

-- Сумма продаж по каждому месяцу
SELECT DATE_TRUNC('month', order_date) as month, SUM(amount)
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- Результат:
month              sum
2025-01-01         50000
2025-02-01         75000
2025-03-01         120000

-- Сумма количества товара по складам
SELECT warehouse_id, SUM(quantity) as total_items
FROM inventory
GROUP BY warehouse_id;
```

#### 3. AVG — среднее арифметическое

**Синтаксис:**
```sql
AVG(столбец) — средняя величина (NULL игнорируются)
AVG(DISTINCT столбец) — среднее уникальных значений
```

**Примеры:**

```sql
-- Средняя зарплата сотрудников
SELECT AVG(salary) as average_salary
FROM employees;
-- Результат: 85000.50

-- Средний размер заказа по клиентам
SELECT customer_id, AVG(amount) as avg_order
FROM orders
GROUP BY customer_id;

-- Средний рейтинг продукта
SELECT product_id, AVG(rating) as avg_rating
FROM reviews
WHERE rating IS NOT NULL
GROUP BY product_id;

-- Результат:
product_id  avg_rating
1           4.5
2           3.8
3           4.2
```

#### 4. MIN и MAX — минимальное и максимальное значение

**Синтаксис:**
```sql
MIN(столбец) — минимальное значение
MAX(столбец) — максимальное значение
```

**Примеры:**

```sql
-- Самая низкая и высокая зарплата
SELECT MIN(salary) as min_salary, MAX(salary) as max_salary
FROM employees;
-- Результат: min_salary: 40000, max_salary: 250000

-- Самый ранний и последний заказ
SELECT MIN(order_date) as first_order, MAX(order_date) as last_order
FROM orders;

-- Цена товара с минимальной и максимальной стоимостью
SELECT 
  MIN(price) as cheapest,
  MAX(price) as most_expensive
FROM products;

-- Последний заказ каждого клиента
SELECT customer_id, MAX(order_date) as last_order_date
FROM orders
GROUP BY customer_id;
```

### Группировка агрегатных функций

**GROUP BY — группировка по одному столбцу:**

```sql
-- Среднее количество заказов по отделам
SELECT 
  d.department_name,
  COUNT(e.id) as employee_count,
  AVG(e.salary) as avg_salary,
  MIN(e.salary) as min_salary,
  MAX(e.salary) as max_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
ORDER BY avg_salary DESC;

-- Результат:
department_name  employee_count  avg_salary  min_salary  max_salary
IT               15              120000      90000       200000
Sales            20              95000       50000       180000
HR               8               75000       60000       95000
```

**GROUP BY с множественными столбцами:**

```sql
-- Продажи по городам и месяцам
SELECT 
  city,
  DATE_TRUNC('month', order_date) as month,
  COUNT(*) as orders,
  SUM(amount) as total_amount,
  AVG(amount) as avg_order
FROM orders
GROUP BY city, DATE_TRUNC('month', order_date)
ORDER BY month DESC, city;

-- Результат:
city      month              orders  total_amount  avg_order
Москва    2025-03-01         50      250000        5000
Москва    2025-02-01         45      225000        5000
СПб       2025-03-01         30      120000        4000
СПб       2025-02-01         28      112000        4000
```

### Фильтрация агрегатных данных — HAVING

**HAVING — условие для агрегатных функций:**

```sql
-- Отделы с средней зарплатой выше 100K
SELECT 
  department_id,
  AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 100000;

-- Результат:
department_id  avg_salary
1              120000
3              105000

-- Клиенты, потратившие больше 50K
SELECT 
  customer_id,
  SUM(amount) as total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 50000
ORDER BY total_spent DESC;
```

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

```sql
-- WHERE — фильтрует строки ДО группировки
-- HAVING — фильтрует ПОСЛЕ группировки

SELECT 
  department_id,
  COUNT(*) as employee_count,
  AVG(salary) as avg_salary
FROM employees
WHERE salary > 60000  -- Фильтруем сотрудников с зарплатой > 60K
GROUP BY department_id
HAVING COUNT(*) > 5   -- Потом берём отделы с > 5 сотрудниками
```

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

#### Пример 1: Анализ продаж

```sql
SELECT 
  p.category,
  COUNT(o.id) as total_orders,
  SUM(od.quantity) as total_items_sold,
  SUM(od.quantity * od.price) as revenue,
  AVG(od.price) as avg_price,
  MIN(od.price) as min_price,
  MAX(od.price) as max_price
FROM products p
JOIN order_details od ON p.id = od.product_id
JOIN orders o ON od.order_id = o.id
WHERE o.order_date >= '2025-01-01'
GROUP BY p.category
HAVING COUNT(o.id) > 10
ORDER BY revenue DESC;
```

#### Пример 2: Анализ клиентов

```sql
SELECT 
  c.name,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_spent,
  AVG(o.amount) as avg_order_value,
  MIN(o.order_date) as first_order,
  MAX(o.order_date) as last_order,
  DATEDIFF(DAY, MIN(o.order_date), MAX(o.order_date)) as customer_lifetime
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
HAVING COUNT(o.id) > 0
ORDER BY total_spent DESC;
```

#### Пример 3: Отчёт по складам

```sql
SELECT 
  w.name as warehouse,
  COUNT(DISTINCT p.id) as unique_items,
  SUM(i.quantity) as total_quantity,
  AVG(i.quantity) as avg_items_per_sku,
  MIN(i.quantity) as min_stock,
  MAX(i.quantity) as max_stock,
  SUM(i.quantity * p.price) as total_value
FROM warehouses w
JOIN inventory i ON w.id = i.warehouse_id
JOIN products p ON i.product_id = p.id
GROUP BY w.id, w.name
ORDER BY total_value DESC;
```

### Window Functions vs Aggregate Functions

**Отличие:**

```sql
-- AGGREGATE (возвращает 1 строку на группу)
SELECT 
  department_id,
  AVG(salary) as avg_salary
FROM employees
GROUP BY department_id;
-- Результат: 10 строк (для 10 отделов)

-- WINDOW (возвращает строку для каждого сотрудника)
SELECT 
  id,
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg_salary
FROM employees;
-- Результат: 150 строк (все сотрудники)
```

### Обработка NULL в агрегатных функциях

```sql
-- COUNT игнорирует NULL
SELECT 
  COUNT(*) as total_rows,           -- 150
  COUNT(commission) as with_commission -- 120 (30 NULL)
FROM employees;

-- SUM, AVG, MIN, MAX тоже игнорируют NULL
SELECT 
  SUM(bonus) as total_bonus,        -- Суммирует только ненулевые
  COUNT(bonus) as employees_with_bonus,  -- Количество ненулевых
  AVG(bonus) as avg_bonus           -- Среднее по ненулевым
FROM employees;

-- Если хочешь обработать NULL, используй COALESCE
SELECT 
  AVG(COALESCE(bonus, 0)) as avg_including_zeros
FROM employees;
```

### Производительность агрегатных функций

**Оптимизация:**
1. Индексируй столбцы в GROUP BY
2. Используй WHERE перед GROUP BY (уменьшает количество строк)
3. Избегай агрегирования больших текстовых полей
4. Используй DISTINCT осторожно (может быть медленно)

```sql
-- Медленно
SELECT COUNT(DISTINCT customer_name) FROM orders;

-- Быстрее (если есть customer_id)
SELECT COUNT(DISTINCT customer_id) FROM orders;
```

### Заключение

Агрегатные функции — это мощный инструмент для анализа данных:
- **COUNT** — количество строк
- **SUM** — сумма значений
- **AVG** — среднее значение
- **MIN/MAX** — минимум/максимум
- **GROUP BY** — группировка данных
- **HAVING** — фильтрация после группировки

Эти функции используются в 90% аналитических запросов и отчётов. Понимание их работы — обязательный навык для системного аналитика.

Что такое агрегатные функции в SQL? Приведите примеры.?

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

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

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

1. COUNT — подсчёт количества строк

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

3. AVG — среднее арифметическое

4. MIN и MAX — минимальное и максимальное значение

Группировка агрегатных функций

Фильтрация агрегатных данных — HAVING

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

Пример 1: Анализ продаж

Пример 2: Анализ клиентов

Пример 3: Отчёт по складам

Window Functions vs Aggregate Functions

Обработка NULL в агрегатных функциях

Производительность агрегатных функций

Заключение