Какие знаешь агрегатные функции в SQL?

Какие знаешь агрегатные функции в SQL

Агрегатные функции в SQL — это мощный инструмент для обработки групп данных и расчёта статистики. Знание этих функций критично для backend-разработчика при работе с базами данных.

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

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

-- Количество всех строк
SELECT COUNT(*) FROM users;

-- Количество непустых значений в столбце
SELECT COUNT(email) FROM users;

-- Количество различных значений
SELECT COUNT(DISTINCT country) FROM users;

-- С условием
SELECT COUNT(*) FROM orders WHERE status = 'completed';

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

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

-- Сумма по группам
SELECT user_id, SUM(amount) as total_spent
FROM orders
GROUP BY user_id;

-- Сумма с условием
SELECT SUM(amount) FROM orders WHERE status = 'paid';

-- Игнорирует NULL значения
SELECT SUM(bonus) FROM users;  -- NULL пропускаются

AVG — среднее значение

-- Средний заказ
SELECT AVG(amount) FROM orders;

-- Средняя зарплата по отделам
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department;

-- Среднее только для активных пользователей
SELECT AVG(balance) FROM users WHERE is_active = true;

MIN / MAX — минимум и максимум

-- Минимальный и максимальный заказ
SELECT MIN(amount), MAX(amount) FROM orders;

-- Первый и последний заказ по времени
SELECT MIN(created_at), MAX(created_at) FROM orders;

-- Самый дешёвый товар по категориям
SELECT category, MIN(price) as cheapest
FROM products
GROUP BY category;

-- Работает и со строками
SELECT MIN(username), MAX(username) FROM users;

GROUP BY и HAVING

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

-- Количество заказов по пользователям
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id;

-- Множественная группировка
SELECT 
    user_id, 
    DATE(created_at) as order_date,
    COUNT(*) as count,
    SUM(amount) as total
FROM orders
GROUP BY user_id, DATE(created_at)
ORDER BY total DESC;

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

-- Пользователи с суммой заказов > 1000
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

-- Отделы с количеством сотрудников >= 5
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;

-- HAVING для AVG
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

Дополнительные функции

STRING_AGG / GROUP_CONCAT — объединение строк

-- PostgreSQL
SELECT 
    user_id,
    STRING_AGG(product_name, ', ') as products
FROM orders
GROUP BY user_id;

-- MySQL
SELECT 
    user_id,
    GROUP_CONCAT(product_name SEPARATOR ', ') as products
FROM orders
GROUP BY user_id;

-- С сортировкой (PostgreSQL)
SELECT 
    user_id,
    STRING_AGG(product_name, ', ' ORDER BY product_name) as sorted_products
FROM orders
GROUP BY user_id;

ARRAY_AGG — агрегирование в массив (PostgreSQL)

SELECT 
    user_id,
    ARRAY_AGG(DISTINCT product_id) as product_ids,
    ARRAY_AGG(amount ORDER BY created_at) as amounts
FROM orders
GROUP BY user_id;

JSON_AGG / JSON_OBJECT_AGG (PostgreSQL)

-- Агрегирование в JSON
SELECT 
    user_id,
    JSON_AGG(json_build_object(
        'id', order_id,
        'amount', amount,
        'date', created_at
    )) as orders
FROM orders
GROUP BY user_id;

Оконные функции (Window Functions)

Это расширенные агрегирующие функции с доступом к контексту строк:

-- Рейтинг заказов по размеру
SELECT 
    order_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) as rank,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as row_num
FROM orders;

-- Разбиение на окна по пользователям
SELECT 
    user_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) as user_total,
    AVG(amount) OVER (PARTITION BY user_id) as user_avg,
    LAG(amount) OVER (PARTITION BY user_id ORDER BY created_at) as prev_amount
FROM orders;

-- Процентил
SELECT 
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) as percentile
FROM employees;

Производительность и оптимизация

-- Плохо: без индекса на status
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Хорошо: индекс ускорит запрос
CREATE INDEX idx_orders_status ON orders(status);

-- Очень большие таблицы: использовать EXPLAIN
EXPLAIN ANALYZE
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

Частые ошибки

-- Ошибка: столбец не в GROUP BY и не агрегирован
SELECT user_id, product_id, SUM(amount)  -- ERROR!
FROM orders
GROUP BY user_id;

-- Правильно: добавить product_id в GROUP BY
SELECT user_id, product_id, SUM(amount)
FROM orders
GROUP BY user_id, product_id;

-- Ошибка: WHERE не работает с агрегирующими функциями
SELECT user_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000  -- ERROR!
GROUP BY user_id;

-- Правильно: использовать HAVING
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

Практический пример: комплексный запрос

SELECT 
    u.user_id,
    u.username,
    COUNT(DISTINCT o.order_id) as total_orders,
    SUM(o.amount) as total_spent,
    AVG(o.amount) as avg_order_size,
    MIN(o.created_at) as first_order,
    MAX(o.created_at) as last_order,
    STRING_AGG(DISTINCT p.category, ', ') as categories
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
LEFT JOIN products p ON o.product_id = p.product_id
WHERE u.is_active = true
GROUP BY u.user_id, u.username
HAVING COUNT(DISTINCT o.order_id) > 0
ORDER BY total_spent DESC
LIMIT 10;

Итоги

Главные функции: COUNT, SUM, AVG, MIN, MAX — используются постоянно GROUP BY / HAVING — критичны для аналитики Оконные функции — для сложной аналитики и рейтингов Специфичные функции (STRING_AGG, ARRAY_AGG) — зависят от СУБД

Составление правильных агрегирующих запросов — это искусство, которое приходит с опытом.