Связанные CTE для фильтрации данных

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

Основные принципы связанных CTE

1. Разделение логики: Вы можете создать несколько связанных CTE для разделения различных этапов обработки данных. Это помогает упростить запрос и повысить его читаемость.

2. Множественные CTE: Можно определять несколько CTE в одном запросе и использовать их совместно.

3. Использование CTE как таблиц: CTE можно использовать в основной части запроса так же, как и обычные таблицы, что позволяет объединять результаты различных CTE.

Примеры использования связанных CTE для фильтрации данных

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

Предположим, у нас есть таблица sales, содержащая данные о продажах. Мы хотим найти продажи по каждому продавцу за последние 30 дней и затем фильтровать продавцов с суммарными продажами выше определенного порога.

WITH RecentSales AS (
    SELECT salesperson_id, SUM(sales_amount) AS total_sales
    FROM sales
    WHERE sale_date > CURRENT_DATE - INTERVAL '30 days'
    GROUP BY salesperson_id
),
HighPerformers AS (
    SELECT salesperson_id, total_sales
    FROM RecentSales
    WHERE total_sales > 5000
)
SELECT sp.salesperson_id, sp.name, hp.total_sales
FROM salespersons sp
JOIN HighPerformers hp ON sp.salesperson_id = hp.salesperson_id
ORDER BY hp.total_sales DESC;

В этом примере RecentSales находит суммарные продажи для каждого продавца за последние 30 дней. Затем HighPerformers фильтрует продавцов с суммарными продажами выше 5,000. Основной запрос объединяет результаты и выводит информацию о высокопроизводительных продавцах.

Пример 2: Комбинирование CTE для сложной фильтрации

Рассмотрим таблицу employees и таблицу departments. Мы хотим найти сотрудников, которые работают в департаментах с низким уровнем зарплат.

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
LowSalaryDepartments AS (
    SELECT department_id
    FROM DepartmentSalaries
    WHERE avg_salary < 40000
)
SELECT e.employee_id, e.name, e.salary, e.department_id
FROM employees e
JOIN LowSalaryDepartments lsd ON e.department_id = lsd.department_id
WHERE e.salary < 40000;

Здесь DepartmentSalaries вычисляет среднюю зарплату по каждому департаменту, а LowSalaryDepartments фильтрует департаменты с средней зарплатой ниже 40,000. Основной запрос выбирает сотрудников из этих департаментов, чья зарплата также ниже 40,000.

Пример 3: Анализ заказов с несколькими CTE

Рассмотрим таблицу orders и таблицу customers. Мы хотим найти клиентов, которые сделали заказы на сумму более 10,000 и затем определить, сколько таких заказов они сделали.

WITH CustomerOrders AS (
    SELECT customer_id, COUNT(order_id) AS order_count, SUM(order_amount) AS total_amount
    FROM orders
    GROUP BY customer_id
),
HighValueCustomers AS (
    SELECT customer_id
    FROM CustomerOrders
    WHERE total_amount > 10000
)
SELECT c.customer_id, c.customer_name, co.order_count, co.total_amount
FROM customers c
JOIN CustomerOrders co ON c.customer_id = co.customer_id
JOIN HighValueCustomers hvc ON c.customer_id = hvc.customer_id;

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

Пример 4: Рекурсивные CTE для фильтрации иерархических данных

Предположим, у нас есть таблица employees, и мы хотим найти всех подчиненных для определенного менеджера, включая их подчиненных на всех уровнях.

WITH RECURSIVE SubordinateHierarchy AS (
    -- Anchor Member: Начинаем с заданного менеджера
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id = 1234

    UNION ALL

    -- Recursive Member: Находим подчиненных для каждого сотрудника
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN SubordinateHierarchy sh ON e.manager_id = sh.employee_id
)
SELECT *
FROM SubordinateHierarchy;

В этом примере SubordinateHierarchy рекурсивно находит всех подчиненных для менеджера с employee_id равным 1234. Запрос возвращает список всех сотрудников, которые находятся под этим менеджером, включая их подчиненных.

Заключение

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