Продвинутое использование CTE

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

1. Рекурсивные CTE

Рекурсивные CTE позволяют решать задачи, связанные с иерархическими структурами, такими как организационные структуры, пути в графах или иерархические каталоги. Рекурсивные CTE имеют две части:

• Anchor Member: начальная часть рекурсии.

• Recursive Member: часть, которая выполняется рекурсивно.

Пример: Иерархия сотрудников

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

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;

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

2. Использование CTE с оконными функциями

CTE можно использовать в сочетании с оконными функциями для сложных аналитических задач.

Пример: Ранжирование сотрудников по зарплате внутри каждого отдела

WITH RankedSalaries AS (
    SELECT employee_id, name, department_id, salary,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT department_id, employee_id, name, salary, rank
FROM RankedSalaries
WHERE rank <= 5;

Этот запрос сначала ранжирует сотрудников по зарплате в каждом отделе, а затем выбирает топ-5 сотрудников по зарплате в каждом отделе.

3. Множественные CTE и их использование в сложных запросах

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

Пример: Сравнение средней зарплаты по отделам

WITH DepartmentSalaries AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
),
HighSalaryDepartments AS (
    SELECT department_id
    FROM DepartmentSalaries
    WHERE average_salary > 60000
)
SELECT e.department_id, e.employee_id, e.name, e.salary
FROM employees e
JOIN HighSalaryDepartments hsd ON e.department_id = hsd.department_id
ORDER BY e.salary DESC;

В этом примере создаются два CTE: DepartmentSalaries для вычисления средней зарплаты по отделам и HighSalaryDepartments для фильтрации отделов с высокой средней зарплатой. Затем основной запрос выбирает сотрудников из этих высокооплачиваемых отделов.

4. Использование CTE в сложных фильтрациях

CTE можно использовать для предварительной фильтрации данных, что упрощает создание сложных условий в основном запросе.

Пример: Найти сотрудников, у которых была зарплата больше 50,000 в последнем году

WITH RecentSalaries AS (
    SELECT employee_id, salary, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY year DESC) AS rn
    FROM salaries
)
SELECT e.employee_id, e.name, rs.salary
FROM employees e
JOIN RecentSalaries rs ON e.employee_id = rs.employee_id
WHERE rs.rn = 1 AND rs.salary > 50000;

Здесь RecentSalaries вычисляет последние зарплаты для каждого сотрудника. Затем основной запрос выбирает сотрудников, у которых последняя зарплата превышала 50,000.

5. Комбинирование CTE с другими SQL конструкциями

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

Пример: Определение сотрудников, которые сделали больше 10 заказов, и подсчет их общих расходов

WITH OrderCounts AS (
    SELECT customer_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
    HAVING COUNT(*) > 10
),
CustomerSpending AS (
    SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.customer_id, c.customer_name
)
SELECT cs.customer_name, cs.total_spent
FROM CustomerSpending cs
JOIN OrderCounts oc ON cs.customer_id = oc.customer_id
ORDER BY cs.total_spent DESC;

В этом примере OrderCounts находит клиентов с более чем 10 заказами, а CustomerSpending подсчитывает их общие расходы. Основной запрос объединяет результаты и сортирует их по сумме расходов.

Заключение

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