Что такое общие табличные выражения в SQL?

Что такое общие табличные выражения (CTE) в SQL?

Общие табличные выражения (англ. Common Table Expressions, CTE) — это временные именованные результирующие наборы данных, которые существуют только в течение выполнения одного SQL-запроса. Они позволяют структурировать сложные запросы, улучшая их читаемость, поддерживаемость и производительность в определённых сценариях. CTE были введены в стандарте SQL:1999 и поддерживаются большинством современных СУБД, включая Microsoft SQL Server, PostgreSQL, Oracle и MySQL (с версии 8.0).

Синтаксис и примеры

Базовый синтаксис CTE выглядит следующим образом:

WITH имя_cte (столбец1, столбец2, ...) AS (
    -- Подзапрос, формирующий временный набор данных
    SELECT столбец1, столбец2, ...
    FROM исходная_таблица
    WHERE условия
)
-- Основной запрос, использующий CTE
SELECT *
FROM имя_cte;

Пример 1: Простой CTE для фильтрации данных

-- Выбираем сотрудников с зарплатой выше среднего
WITH HighEarners AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > (SELECT AVG(Salary) FROM Employees)
)
SELECT *
FROM HighEarners
ORDER BY Salary DESC;

Ключевые особенности и преимущества

1. Улучшение читаемости и структурирования кода

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

2. Возможность рекурсивных запросов

• Рекурсивные CTE — одно из самых мощных применений, позволяющих обрабатывать иерархические или древовидные структуры данных (например, организационные иерархии, деревья комментариев).

-- Пример рекурсивного CTE для организации иерархии
WITH RecursiveCTE AS (
    -- Якорная часть: начальные строки
    SELECT EmployeeID, ManagerID, FirstName, 1 AS Level
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Рекурсивная часть: присоединение дочерних элементов
    SELECT e.EmployeeID, e.ManagerID, e.FirstName, r.Level + 1
    FROM Employees e
    INNER JOIN RecursiveCTE r ON e.ManagerID = r.EmployeeID
)
SELECT *
FROM RecursiveCTE;

3. Замена представлений (Views) в рамках одного запроса

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

4. Множественное использование в одном запросе

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

WITH 
DeptSummary AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
),
HighBudgetDepts AS (
    SELECT DepartmentID
    FROM Departments
    WHERE Budget > 1000000
)
SELECT d.DepartmentName, ds.AvgSalary
FROM DeptSummary ds
JOIN HighBudgetDepts hbd ON ds.DepartmentID = hbd.DepartmentID
JOIN Departments d ON ds.DepartmentID = d.DepartmentID;

5. Упрощение операций с оконными функциями и агрегацией

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

Ограничения и производительность

• Временное существование: CTE не материализуются по умолчанию (хотя некоторые СУБД, например PostgreSQL, могут оптимизировать их), что означает, что при многократном обращении к CTE в основном запросе подзапрос может выполняться несколько раз.
• Область видимости: CTE доступна только в рамках того запроса, в котором она определена (включая подзапросы внутри основного запроса).
• Производительность: В некоторых случаях, особенно при работе с большими объёмами данных, временные таблицы могут оказаться более эффективными, так как они материализуются и могут индексироваться.

Практическое применение в Backend-разработке на C#

В контексте backend-разработки на C# с использованием ORM, таких как Entity Framework Core, CTE часто применяются:

• Для написания сложных сырых SQL-запросов (Raw Queries) при оптимизации критических участков.
• В хранимых процедурах и функциях баз данных, которые вызываются из C#-кода.
• При обработке иерархических данных (например, построение деревьев категорий в интернет-магазине).

Пример использования в Entity Framework Core:

var query = @"WITH RecentOrders AS (
                SELECT OrderId, CustomerId, OrderDate
                FROM Orders
                WHERE OrderDate >= DATEADD(month, -1, GETDATE())
              )
              SELECT c.Name, COUNT(ro.OrderId) AS OrderCount
              FROM RecentOrders ro
              JOIN Customers c ON ro.CustomerId = c.Id
              GROUP BY c.Name";

var results = await context.Customers
    .FromSqlRaw(query)
    .ToListAsync();

Заключение

Общие табличные выражения — мощный инструмент SQL, который повышает читаемость и поддерживаемость сложных запросов, особенно при рекурсивной обработке данных. Хотя они не всегда являются оптимальным решением для производительности (в сравнении с временными таблицами в некоторых сценариях), их грамотное использование позволяет писать чистый, модульный SQL-код. Для backend-разработчика на C# понимание CTE важно при работе с оптимизированными запросами, хранимыми процедурами и сложной бизнес-логикой на уровне базы данных.