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

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

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

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

Подсчитывает количество строк, соответствующих условию.

-- Подсчёт всех пользователей
SELECT COUNT(*) as total_users FROM users;

-- Подсчёт только заполненных email
SELECT COUNT(email) as users_with_email FROM users;

-- Подсчёт уникальных значений
SELECT COUNT(DISTINCT country) as countries FROM users;

-- С условием WHERE
SELECT COUNT(*) as active_users FROM users WHERE status = 'active';

-- Группировка
SELECT country, COUNT(*) as user_count FROM users GROUP BY country;

В Python с SQLAlchemy:

from sqlalchemy import func, select
from models import User

# COUNT(*)
query = select(func.count()).select_from(User)
count = session.execute(query).scalar()

# COUNT DISTINCT
query = select(func.count(func.distinct(User.country)))
distinct_countries = session.execute(query).scalar()

# COUNT с условием
query = select(func.count()).select_from(User).where(User.status == 'active')
active_count = session.execute(query).scalar()

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

Складывает все значения в колонке.

-- Общая сумма продаж
SELECT SUM(amount) as total_sales FROM orders;

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

-- Сумма с фильтром
SELECT SUM(amount) as monthly_total FROM orders WHERE DATE_TRUNC('month', created_at) = '2024-01';

-- NULL не учитывается в SUM
SELECT SUM(commission) FROM sales;  -- Игнорирует NULL значения

В Python:

from sqlalchemy import func
from models import Order

# SUM всех заказов
query = select(func.sum(Order.amount))
total = session.execute(query).scalar()

# SUM по группам
from sqlalchemy import select
query = select(
    Order.customer_id,
    func.sum(Order.amount).label('total')
).group_by(Order.customer_id)

results = session.execute(query).all()
for customer_id, total in results:
    print(f"Customer {customer_id}: {total}")

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

Вычисляет среднее арифметическое.

-- Средняя цена товара
SELECT AVG(price) as average_price FROM products;

-- Средний рейтинг по категориям
SELECT category, AVG(rating) as avg_rating FROM products GROUP BY category;

-- AVG с HAVING (фильтр на результат агрегации)
SELECT category, AVG(rating) as avg_rating FROM products 
GROUP BY category
HAVING AVG(rating) > 4.0;

В Python:

from sqlalchemy import func
from models import Product

# AVG
query = select(func.avg(Product.price))
average_price = session.execute(query).scalar()

# AVG с GROUP BY
query = select(
    Product.category,
    func.avg(Product.rating).label('avg_rating')
).group_by(Product.category)

for category, rating in session.execute(query):
    print(f"{category}: {rating}")

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

Находят наименьшее и наибольшее значения.

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

-- Дата первого и последнего заказа
SELECT 
    MIN(created_at) as first_order,
    MAX(created_at) as last_order
FROM orders;

-- MIN/MAX по группам
SELECT 
    customer_id,
    MIN(amount) as smallest_order,
    MAX(amount) as largest_order
FROM orders
GROUP BY customer_id;

В Python:

from sqlalchemy import func
from models import Product, Order

# MIN и MAX
query = select(
    func.min(Product.price).label('min_price'),
    func.max(Product.price).label('max_price')
)
min_price, max_price = session.execute(query).first()

# С GROUP BY
query = select(
    Order.customer_id,
    func.min(Order.amount).label('min'),
    func.max(Order.amount).label('max')
).group_by(Order.customer_id)

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

Объединяет строки в одну с разделителем.

-- MySQL: GROUP_CONCAT
SELECT user_id, GROUP_CONCAT(skill SEPARATOR ', ') as skills 
FROM user_skills 
GROUP BY user_id;

-- PostgreSQL: STRING_AGG
SELECT user_id, STRING_AGG(skill, ', ') as skills 
FROM user_skills 
GROUP BY user_id;

-- Oracle: LISTAGG
SELECT user_id, LISTAGG(skill, ', ') WITHIN GROUP (ORDER BY skill) as skills
FROM user_skills
GROUP BY user_id;

Результат:

user_id | skills
1       | Python, JavaScript, Go
2       | Java, C++

6. STDDEV / VARIANCE — стандартное отклонение и дисперсия

Для статистического анализа.

-- Стандартное отклонение цен
SELECT STDDEV(price) as price_std_dev FROM products;

-- Дисперсия оценок
SELECT category, VARIANCE(rating) as rating_variance 
FROM products 
GROUP BY category;

7. PERCENTILE — процентили

Полезны для аналитики распределения данных.

-- PostgreSQL: PERCENTILE_CONT
SELECT 
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY price) as p25,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY price) as median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY price) as p75
FROM products;

-- Результат: квартили распределения цен

8. Оконные функции (Window Functions) с агрегацией

Онцевые функции более мощны чем обычная агрегация.

-- Бегущий итог
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders
ORDER BY order_date;

-- Ранг в группе
SELECT 
    product_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) as rank
FROM sales;

-- Среднее относительно группы
SELECT 
    name,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg
FROM employees;

В Python:

from sqlalchemy import func, over, window
from models import Order

# Оконная функция - бегущий итог
running_total = func.sum(Order.amount).over(
    order_by=Order.order_date
)
query = select(Order.order_date, Order.amount, running_total)

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

SELECT 
    DATE_TRUNC('month', order_date)::date as month,
    COUNT(*) as order_count,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order_value,
    MIN(amount) as min_order,
    MAX(amount) as max_order,
    STDDEV(amount) as revenue_stddev,
    STRING_AGG(DISTINCT category, ', ') as categories
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
HAVING COUNT(*) > 10
ORDER BY month DESC;

В Python:

from sqlalchemy import func, text
from sqlalchemy.sql import select
from models import Order

query = select(
    func.date_trunc('month', Order.order_date).label('month'),
    func.count().label('count'),
    func.sum(Order.amount).label('total'),
    func.avg(Order.amount).label('avg'),
    func.min(Order.amount).label('min'),
    func.max(Order.amount).label('max'),
    func.stddev(Order.amount).label('stddev')
).where(
    Order.status == 'completed'
).group_by(
    func.date_trunc('month', Order.order_date)
).having(
    func.count() > 10
).order_by(
    func.date_trunc('month', Order.order_date).desc()
)

results = session.execute(query).all()

Выводы

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

• COUNT — количество строк
• SUM, AVG — сумма и среднее
• MIN, MAX — экстремумы
• STRING_AGG — объединение строк
• STDDEV, VARIANCE — статистика

Важные правила:

• Используй GROUP BY с агрегатами
• HAVING фильтрует результаты агрегации
• NULL не учитывается в большинстве функций
• Оконные функции мощнее обычной агрегации
• Проверь документацию конкретной БД (синтаксис отличается)