Использование CTE в качестве таблиц

Использование Common Table Expressions (CTE) в SQL позволяет временно создавать таблицы, которые можно использовать в основном запросе. CTE обеспечивают удобный способ для создания и использования промежуточных наборов данных, что упрощает сложные запросы и повышает их читаемость.

Основы использования CTE в качестве таблиц

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

Синтаксис

WITH CTE_Name AS (
    -- Запрос для создания временной таблицы
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
-- Основной запрос, использующий CTE как таблицу
SELECT column1, column2, ...
FROM CTE_Name
WHERE additional_conditions;

Примеры использования CTE в качестве таблиц

Пример 1: Основной запрос с CTE

Создание CTE для упрощения основного запроса.

WITH HighSalaryEmployees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM HighSalaryEmployees
WHERE name LIKE 'A%';

В этом примере CTE HighSalaryEmployees содержит сотрудников с зарплатой более 50,000. Основной запрос выбирает только тех из этих сотрудников, чьи имена начинаются с буквы 'A'.

Пример 2: Использование CTE для объединения данных

Создание нескольких CTE и их использование в основном запросе.

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
),
TopSalaries AS (
    SELECT employee_id, department_id, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
)
SELECT e.department_id, e.employee_id, e.salary
FROM TopSalaries e
JOIN DepartmentSalaries d ON e.department_id = d.department_id
WHERE e.salary > d.average_salary;

Здесь DepartmentSalaries вычисляет среднюю зарплату по отделам, а TopSalaries находит сотрудников с зарплатой выше средней. Основной запрос объединяет результаты и выбирает сотрудников, чья зарплата выше средней зарплаты в их отделе.

Пример 3: Использование CTE в соединениях

Использование CTE как таблицы в соединениях.

WITH RecentOrders AS (
    SELECT order_id, customer_id, order_date
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
)
SELECT c.customer_id, c.customer_name, COUNT(o.order_id) AS recent_orders_count
FROM customers c
JOIN RecentOrders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY recent_orders_count DESC;

В этом примере RecentOrders содержит заказы, сделанные за последние 30 дней. Основной запрос соединяет эту CTE с таблицей клиентов и подсчитывает количество недавних заказов для каждого клиента.

Пример 4: Рекурсивные CTE

Использование рекурсивных CTE для работы с иерархическими данными.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: Начинаем с топ-менеджеров
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: Находим подчиненных
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level, manager_id, employee_id;

Этот рекурсивный CTE создает иерархию сотрудников, начиная с тех, у кого нет руководителя, и добавляет их подчиненных рекурсивно.

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

Пример 5: Использование CTE для аналитических задач

Создание CTE для предварительной агрегации данных и последующая работа с агрегированными данными.

WITH SalesData AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY salesperson_id
),
TopSalespersons AS (
    SELECT salesperson_id, total_sales
    FROM SalesData
    WHERE total_sales > 10000
)
SELECT s.salesperson_id, s.name, ts.total_sales
FROM salespersons s
JOIN TopSalespersons ts ON s.salesperson_id = ts.salesperson_id
ORDER BY ts.total_sales DESC;

В этом примере SalesData вычисляет общие продажи для каждого продавца. TopSalespersons фильтрует продавцов с продажами выше 10,000. Основной запрос соединяет данные и выводит информацию о топ-продавцах.

Заключение

CTE (Common Table Expressions) позволяют использовать временные результаты как таблицы в основном запросе, что упрощает написание сложных запросов и делает их более читаемыми. Использование CTE позволяет создавать промежуточные наборы данных, которые можно использовать в соединениях, фильтрациях и агрегатах. Рекурсивные CTE особенно полезны для работы с иерархическими данными.