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

Функции Агрегирования в SQL

Функции агрегирования — это функции, которые работают с несколькими строками и возвращают одно значение. Они используются для анализа и суммирования данных.

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

COUNT подсчитывает количество строк или ненулевых значений.

-- Общее количество пользователей
SELECT COUNT(*) AS total_users FROM users;

-- Количество пользователей с email
SELECT COUNT(email) AS users_with_email FROM users;

-- Количество уникальных email
SELECT COUNT(DISTINCT email) AS unique_emails FROM users;

-- По группам
SELECT department, COUNT(*) AS emp_count 
FROM employees 
GROUP BY department;

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

SUM суммирует числовые значения.

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

-- По месяцам
SELECT 
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount) AS monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

-- Только положительные значения
SELECT SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) 
FROM transactions;

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

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

-- Средняя зарплата
SELECT AVG(salary) AS avg_salary FROM employees;

-- Средняя оценка по продуктам
SELECT 
    product_id,
    AVG(rating) AS avg_rating,
    COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) >= 4.0;

-- Скользящее среднее
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

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

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

-- Самый молодой и старший сотрудник
SELECT 
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM employees;

-- По группам
SELECT 
    category,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    MAX(price) - MIN(price) AS price_range
FROM products
GROUP BY category;

-- Первый и последний заказ пользователя
SELECT 
    user_id,
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order
FROM orders
GROUP BY user_id;

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

STRING_AGG (PostgreSQL) объединяет значения в одну строку.

-- Список всех товаров в заказе
SELECT 
    order_id,
    STRING_AGG(product_name, ', ' ORDER BY product_name) AS products
FROM order_items
GROUP BY order_id;

-- С разделителем и лимитом
SELECT 
    team_id,
    STRING_AGG(employee_name, '; ') AS team_members
FROM employees
GROUP BY team_id;

В MySQL используй GROUP_CONCAT:

SELECT 
    order_id,
    GROUP_CONCAT(product_name SEPARATOR ', ') AS products
FROM order_items
GROUP BY order_id;

6. ARRAY_AGG — Массив значений

ARRAY_AGG (PostgreSQL) собирает значения в массив.

-- Массив ID всех комментариев к посту
SELECT 
    post_id,
    ARRAY_AGG(comment_id) AS comment_ids,
    ARRAY_AGG(author_id ORDER BY created_at) AS authors
FROM comments
GROUP BY post_id;

-- С фильтром
SELECT 
    category,
    ARRAY_AGG(DISTINCT product_id) AS product_ids
FROM products
GROUP BY category;

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

Оконные функции работают с подмножеством строк.

-- Ранг сотрудника по зарплате
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

-- Процент от общей суммы
SELECT 
    product_id,
    amount,
    ROUND(100.0 * amount / SUM(amount) OVER (), 2) AS percent_of_total
FROM sales;

-- Разница с предыдущей строкой
SELECT 
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    amount - LAG(amount) OVER (ORDER BY order_date) AS change
FROM orders;

8. FILTER — Фильтрация агрегирования

FILTER (PostgreSQL) фильтрует строки для агрегирования.

SELECT 
    category,
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE price > 100) AS expensive,
    AVG(price) AS avg_price,
    AVG(price) FILTER (WHERE discount > 0) AS avg_discounted
FROM products
GROUP BY category;

9. GROUP_CONCAT — Для MySQL

SELECT 
    department_id,
    GROUP_CONCAT(employee_name ORDER BY employee_name SEPARATOR ', ') AS employees
FROM employees
GROUP BY department_id;

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

SELECT 
    DATE_TRUNC('month', o.created_at) AS month,
    COUNT(DISTINCT o.user_id) AS unique_customers,
    COUNT(*) AS total_orders,
    SUM(o.amount) AS total_revenue,
    AVG(o.amount) AS avg_order_value,
    MIN(o.amount) AS min_order,
    MAX(o.amount) AS max_order,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.amount) AS median_order,
    STRING_AGG(DISTINCT oi.product_name, ', ' ORDER BY oi.product_name) AS top_products
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY DATE_TRUNC('month', o.created_at)
ORDER BY month DESC;

Best Practices

• Используй COUNT(*) для общего количества
• COUNT(column) для ненулевых значений
• DISTINCT для уникальных значений
• GROUP BY для агрегирования по группам
• HAVING для фильтрации групп
• Оконные функции для анализа рядов
• FILTER для условного агрегирования (PostgreSQL)
• Помни про NULL — большинство функций его игнорируют