Урок 5. Мастерство продвинутых запросов в PostgreSQL

Изучите агрегатные функции, группировку данных и подзапросы в PostgreSQL. Освойте мощные инструменты для анализа данных и создания сложных отчетов.

Агрегатные функции: мощь анализа данных

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

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

• COUNT(): подсчет количества строк
• SUM(): сумма значений
• AVG(): среднее значение
• MAX(): максимальное значение
• MIN(): минимальное значение

Пример использования:

-- Предположим, у нас есть таблица sales с колонками product_id, quantity, price
SELECT 
    COUNT(*) as total_sales,
    SUM(quantity * price) as total_revenue,
    AVG(price) as average_price,
    MAX(price) as max_price,
    MIN(price) as min_price
FROM sales;

Этот запрос даст нам общее количество продаж, общую выручку, среднюю, максимальную и минимальную цену товара.

Группировка данных: GROUP BY

GROUP BY позволяет группировать результаты по одному или нескольким столбцам. Это особенно полезно в сочетании с агрегатными функциями.

Пример:

-- Получим общую выручку по каждому продукту
SELECT 
    product_id,
    SUM(quantity * price) as total_revenue
FROM sales
GROUP BY product_id
ORDER BY total_revenue DESC;

Этот запрос покажет нам, какие продукты приносят наибольшую выручку.

Фильтрация групп: HAVING

HAVING похож на WHERE, но применяется к результатам группировки. Он позволяет фильтровать группы на основе агрегатных значений.

Пример:

-- Найдем продукты, общая выручка по которым превышает 1000
SELECT 
    product_id,
    SUM(quantity * price) as total_revenue
FROM sales
GROUP BY product_id
HAVING SUM(quantity * price) > 1000
ORDER BY total_revenue DESC;

Подзапросы: запросы внутри запросов

Подзапросы — это запросы, вложенные в другие запросы. Они позволяют выполнять сложные операции и сравнения.

Пример:

-- Найдем продукты, цена которых выше средней
SELECT product_id, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Здесь подзапрос (SELECT AVG(price) FROM products) вычисляет среднюю цену, а основной запрос использует это значение для фильтрации.

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

Давайте объединим все изученное в одном сложном запросе:

-- Найдем топ-5 продуктов по выручке, которые продавались выше средней цены
SELECT 
    p.product_name,
    SUM(s.quantity * s.price) as total_revenue,
    AVG(s.price) as avg_price
FROM 
    sales s
JOIN 
    products p ON s.product_id = p.id
WHERE 
    s.price > (SELECT AVG(price) FROM sales)
GROUP BY 
    p.product_name
HAVING 
    COUNT(*) > 10  -- продано более 10 раз
ORDER BY 
    total_revenue DESC
LIMIT 5;

Этот запрос:

1. Объединяет таблицы sales и products
2. Фильтрует продажи с ценой выше средней
3. Группирует результаты по названию продукта
4. Отбирает только те продукты, которые были проданы более 10 раз
5. Сортирует по общей выручке и выбирает топ-5

Продвинутые запросы в PostgreSQL — это мощный инструмент для анализа данных и получения ценной информации. Агрегатные функции позволяют суммировать и анализировать большие объемы данных. GROUP BY и HAVING дают возможность группировать и фильтровать результаты на уровне групп. Подзапросы открывают новые возможности для сложных вычислений и сравнений. Комбинируя эти инструменты, вы можете создавать сложные и информативные отчеты, которые помогут принимать обоснованные бизнес-решения.

Домашнее задание:

1. Создайте таблицу `employees` с полями `id`, `name`, `department`, `salary`.
2. Вставьте в нее не менее 20 записей с разными зарплатами и отделами.
3. Напишите запрос, который выводит средную зарплату по каждому отделу.
4. Найдите отдел с самой высокой средней зарплатой.
5. Выведите имена сотрудников, чья зарплата выше средней по их отделу.

Удачи в изучении продвинутых запросов PostgreSQL!

Для оптимизации производительности базы данных, перейдите к уроку 6: Индексы и оптимизация запросов в PostgreSQL.