Примеры сложных запросов с несколькими CTE

В этой лекции мы будем заниматься настоящей магией запросов! Мы создадим несколько примеров с использованием нескольких CTE, чтобы показать, как они могут быть интегрированы друг с другом для построения сложных и многоэтапных запросов. Эти примеры пригодятся в реальной жизни, особенно в сложных аналитических задачах.

Пример 1: Анализ работы студентов

Представьте, что у нас есть база данных университета с тремя таблицами:

Таблица students:

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    student_name TEXT NOT NULL
);

Таблица grades:

CREATE TABLE grades (
    grade_id SERIAL PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT NOT NULL,
    grade NUMERIC(3, 1) NOT NULL
);

Таблица courses:

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name TEXT NOT NULL
);

Задача: получить список студентов, у которых средний балл выше 85, с указанием их среднего балла и названиями курсов, которые они посещают.

Запрос:

WITH high_achievers AS (
    -- Находим студентов с высоким средним баллом
    SELECT 
        student_id, 
        AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 85
),
student_courses AS (
    -- Находим курсы, на которые записан каждый студент
    SELECT 
        s.student_id, 
        c.course_name
    FROM grades g
    JOIN courses c ON g.course_id = c.course_id
    JOIN students s ON g.student_id = s.student_id
)
-- Объединяем результаты
SELECT 
    s.student_name, 
    ha.avg_grade, 
    sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id
JOIN students s ON s.student_id = ha.student_id;

Объяснение:

1. В первом CTE (high_achievers) мы вычисляем средний балл для каждого студента и выбираем тех, чьи оценки выше 85.
2. Во втором CTE (student_courses) мы сопоставляем студентов с их курсами.
3. В главном запросе мы объединяем данные из обоих CTE, чтобы получить список студентов, их средний балл и курсы, которые они посещают.

Пример 2: Отчет по продажам для интернет-магазина

Представим, что мы работаем с интернет-магазином и у нас есть следующие таблицы:

Таблица orders (заказы):

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

Таблица customers (клиенты):

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name TEXT NOT NULL
);

Таблица order_items (товары в заказах):

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price NUMERIC(10, 2) NOT NULL
);

Задача: построить отчет, показывающий для каждого клиента:

• Общее количество заказов.
• Общую сумму всех заказов за последний месяц.
• Список всех уникальных товаров, которые он покупал.

Запрос:

WITH recent_orders AS (
    -- Выбираем заказы за последний месяц
    SELECT 
        order_id, 
        customer_id, 
        total_amount 
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
),
customer_summary AS (
    -- Считаем общее количество заказов и сумму для каждого клиента
    SELECT 
        ro.customer_id,
        COUNT(ro.order_id) AS total_orders,
        SUM(ro.total_amount) AS total_spent
    FROM recent_orders ro
    GROUP BY ro.customer_id
),
customer_products AS (
    -- Выбираем уникальные товары, которые покупал каждый клиент
    SELECT DISTINCT
        ro.customer_id,
        oi.product_id
    FROM recent_orders ro
    JOIN order_items oi ON ro.order_id = oi.order_id
)
-- Объединяем результаты
SELECT 
    c.customer_name,
    cs.total_orders,
    cs.total_spent,
    ARRAY_AGG(cp.product_id) AS purchased_products
FROM customer_summary cs
JOIN customers c ON cs.customer_id = c.customer_id
JOIN customer_products cp ON cp.customer_id = c.customer_id
GROUP BY c.customer_name, cs.total_orders, cs.total_spent;

Объяснение:

1. В первом CTE (recent_orders) мы выбираем заказы за последний месяц.
2. Во втором CTE (customer_summary) мы рассчитываем общее количество заказов и общую сумму для каждого клиента.
3. В третьем CTE (customer_products) мы получаем уникальные товары, которые покупал каждый клиент.
4. В итоговом запросе мы объединяем данные и используем ARRAY_AGG() для формирования списка уникальных товаров.

Пример 3: Анализ иерархии сотрудников

У нас есть таблица сотрудников:

Таблица employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name TEXT NOT NULL,
    manager_id INT NULL
);

Задача: построить иерархию сотрудников, начиная с генерального директора. Указать уровень каждого сотрудника в иерархии.

Запрос:

WITH RECURSIVE employee_hierarchy AS (
    -- Начинаем с сотрудников, у которых нет менеджера (генеральный директор)
    SELECT 
        employee_id, 
        employee_name, 
        manager_id, 
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Добавляем всех подчиненных текущего уровня
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
-- Выводим иерархию
SELECT 
    employee_id, 
    employee_name, 
    manager_id, 
    level
FROM employee_hierarchy
ORDER BY level, employee_id;

1. В рекурсивном запросе мы начинаем с генерального директора (сотрудники без менеджера).
2. На каждом шаге добавляем подчиненных текущего уровня, увеличивая уровень (level) на единицу.
3. В главном запросе мы выбираем всю иерархию, отсортированную по уровню и идентификатору сотрудника.

Полезные советы и типичные ошибки

• Избыток CTE: не используйте их, где можно обойтись подзапросом. CTE иногда могут снижать производительность из-за материализации данных.
• Имена CTE: давайте понятные и короткие имена вашим CTE, чтобы запросы оставались читаемыми.
• Порядок выполнения: помните, что CTE выполняются строго в порядке их объявления.
• Группировка данных: используйте GROUP BY только там, где это необходимо, чтобы избежать лишних операций.

Все эти примеры показывают, как CTE можно использовать для разделения сложных задач на этапы, улучшая читаемость и поддержку запросов. Теперь вы вооружены инструментами для решения сложных аналитических задач с помощью PostgreSQL!

2

Задача

SQL SELF, 28 уровень, 3 лекция

Недоступна

Анализ продаж за последние три месяца

Анализ продаж за последние три месяца

Открыть