Приклади складних запитів з кількома 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!