Organizar consultas con expresiones de tabla comunes

12 minutos

Al trabajar con consultas complejas, a menudo es necesario dividir la lógica en partes administrables o hacer referencia a la misma subconsulta varias veces. Las expresiones de tabla comunes (CTE) proporcionan una manera de definir conjuntos de resultados con nombre temporales que solo existen durante una sola consulta, lo que hace que el código sea más legible y fácil de mantener.

Descripción de la sintaxis de CTE

Una expresión de tabla común se define mediante la WITH cláusula , seguida del nombre de CTE, una lista de columnas opcional y una consulta que define el conjunto de resultados. A continuación, se puede hacer referencia a la CTE en la instrucción SELECT, INSERT, UPDATE o DELETE posterior.

WITH CTE_Name (Column1, Column2)
AS
(
    -- CTE query definition
    SELECT Column1, Column2
    FROM SomeTable
    WHERE SomeCondition = 'Value'
)
SELECT * FROM CTE_Name;

Las CTE ofrecen varias ventajas sobre las tablas y subconsultas derivadas:

Legibilidad mejorada: las consultas complejas son más fáciles de entender cuando se dividen en secciones lógicas con nombre.
Funcionalidad de autorreferencia: los CTE recursivos pueden hacer referencia a sí mismos, lo que permite el recorrido jerárquico de datos.
Varias referencias: haga referencia al mismo CTE varias veces en la consulta externa sin redefinirlo.
Diseño modular: cree consultas complejas de forma incremental mediante la definición de varios CTE

Nota:

Los CTE son conjuntos de resultados temporales que solo existen durante la ejecución de la consulta. A diferencia de las tablas tradicionales, no persisten más allá de la sentencia que las usa y no requieren una eliminación explícita.

Crear CTE no recursivos

Los CTE no recursivos definen un conjunto de resultados basado en una consulta sencilla que no hace referencia a sí misma. Este patrón es útil para simplificar combinaciones complejas, desglosar cálculos de varios pasos o mejorar la organización del código.

En el ejemplo siguiente se usa un CTE para calcular las métricas de ventas antes de unirse a la información del producto:

WITH SalesSummary AS
(
    SELECT 
        ProductID,
        SUM(OrderQty) AS TotalQuantity,
        SUM(LineTotal) AS TotalRevenue,
        COUNT(DISTINCT SalesOrderID) AS OrderCount
    FROM SalesLT.SalesOrderDetail
    GROUP BY ProductID
)
SELECT 
    p.Name AS ProductName,
    p.ProductNumber,
    p.ListPrice,
    ss.TotalQuantity,
    ss.TotalRevenue,
    ss.OrderCount,
    ss.TotalRevenue / NULLIF(ss.TotalQuantity, 0) AS AverageUnitPrice
FROM SalesLT.Product AS p
INNER JOIN SalesSummary AS ss
    ON p.ProductID = ss.ProductID
ORDER BY ss.TotalRevenue DESC;

En primer lugar, esta consulta crea un CTE denominado SalesSummary que agrega los detalles del pedido por producto, calculando la cantidad total vendida, los ingresos totales y el recuento de pedidos. A continuación, la consulta principal combina este CTE con la Product tabla para mostrar los nombres de producto junto con sus métricas de ventas. La NULLIF función evita la división por cero al calcular el precio unitario medio.

Puede definir varias CTE en una sola cláusula WITH separándolas con comas. Los CTE posteriores pueden hacer referencia a otros anteriores, lo que permite la transformación progresiva de datos:

WITH CategorySales AS
(
    SELECT 
        p.ProductCategoryID,
        SUM(sod.LineTotal) AS CategoryRevenue
    FROM SalesLT.Product AS p
    INNER JOIN SalesLT.SalesOrderDetail AS sod
        ON p.ProductID = sod.ProductID
    GROUP BY p.ProductCategoryID
),
RankedCategories AS
(
    SELECT 
        ProductCategoryID,
        CategoryRevenue,
        RANK() OVER (ORDER BY CategoryRevenue DESC) AS RevenueRank
    FROM CategorySales
)
SELECT 
    pc.Name AS CategoryName,
    rc.CategoryRevenue,
    rc.RevenueRank
FROM RankedCategories AS rc
INNER JOIN SalesLT.ProductCategory AS pc
    ON rc.ProductCategoryID = pc.ProductCategoryID
WHERE rc.RevenueRank <= 5;

Sugerencia

Al compilar consultas con varios CTE, comience con las transformaciones de datos más granulares y agregue o filtre progresivamente en los CTE posteriores. Este enfoque facilita la depuración, ya que puede probar cada CTE de forma independiente.

Creación de CTE recursivas

Los CTE recursivos hacen referencia a sí mismos para procesar estructuras de datos jerárquicas o similares a grafos. Un CTE recursivo consta de dos partes: un miembro de anclaje que proporciona el conjunto de resultados inicial y un miembro recursivo que hace referencia al CTE que se basa en los resultados anteriores.

La sintaxis general de un CTE recursivo es:

WITH RecursiveCTE AS
(
    -- Anchor member: starting point
    SELECT columns
    FROM table
    WHERE starting_condition
    
    UNION ALL
    
    -- Recursive member: references the CTE
    SELECT columns
    FROM table
    INNER JOIN RecursiveCTE
        ON join_condition
)
SELECT * FROM RecursiveCTE;

Un caso de uso común es atravesar una jerarquía organizativa. Considere una tabla de empleados en la que cada empleado tiene un administrador:

WITH EmployeeHierarchy AS
(
    -- Anchor: Start with top-level managers (no manager)
    SELECT 
        EmployeeID,
        FirstName,
        LastName,
        ManagerID,
        0 AS Level,
        CAST(FirstName + ' ' + LastName AS NVARCHAR(500)) AS HierarchyPath
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- Recursive: Find employees who report to previously found employees
    SELECT 
        e.EmployeeID,
        e.FirstName,
        e.LastName,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.FirstName + ' ' + e.LastName AS NVARCHAR(500))
    FROM HumanResources.Employee AS e
    INNER JOIN EmployeeHierarchy AS eh
        ON e.ManagerID = eh.EmployeeID
)
SELECT 
    EmployeeID,
    FirstName,
    LastName,
    Level,
    HierarchyPath
FROM EmployeeHierarchy
ORDER BY HierarchyPath;

Importante

Los CTE recursivos pueden provocar bucles infinitos si nunca se cumple la condición de terminación. SQL Server limita la recursividad a 100 niveles de forma predeterminada. Use OPTION (MAXRECURSION n) para cambiar este límite, donde n es la profundidad máxima de recursividad (0 para ilimitado).

Generar secuencias con CTEs recursivas

Los CTE recursivos se destacan al generar secuencias de números o fechas sin necesidad de una tabla de números físicos. Esta técnica es útil para crear intervalos de fechas, rellenar huecos en los datos o generar datos de prueba:

-- Generate a sequence of dates for the current month
WITH DateSequence AS
(
    -- Anchor: Get the first day of the current month
    SELECT CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS DATE) AS DateValue
    
    UNION ALL
    
    -- Recursive: Add one day until we reach the end of the month
    SELECT DATEADD(DAY, 1, DateValue)
    FROM DateSequence
    WHERE DateValue < EOMONTH(GETDATE())
)
-- Output each date with its day name
SELECT DateValue, DATENAME(WEEKDAY, DateValue) AS DayName
FROM DateSequence
OPTION (MAXRECURSION 31);  -- Allow up to 31 iterations (max days in a month)

Puede combinar secuencias generadas con datos reales para identificar brechas o crear informes de resumen:

-- Generate a numbers table from 1 to 1000
WITH Numbers AS
(
    -- Anchor: Start with 1
    SELECT 1 AS n
    UNION ALL
    -- Recursive: Increment until we reach 1000
    SELECT n + 1 FROM Numbers WHERE n < 1000
),
-- Convert numbers to dates for the entire year
DateRange AS
(
    SELECT DATEADD(DAY, n - 1, '2024-01-01') AS OrderDate
    FROM Numbers
    WHERE DATEADD(DAY, n - 1, '2024-01-01') <= '2024-12-31'
)
-- Count orders for each date, showing 0 for dates with no orders
SELECT 
    dr.OrderDate,
    COALESCE(COUNT(soh.SalesOrderID), 0) AS OrderCount
FROM DateRange AS dr
LEFT JOIN SalesLT.SalesOrderHeader AS soh
    ON CAST(soh.OrderDate AS DATE) = dr.OrderDate
GROUP BY dr.OrderDate
ORDER BY dr.OrderDate
OPTION (MAXRECURSION 366);  -- Allow up to 366 iterations (leap year)

Uso de CTE con instrucciones de modificación de datos

Las CTEs se pueden usar con las instrucciones INSERT, UPDATE y DELETE, no solo SELECT. Esta funcionalidad es útil cuando la lógica de modificación requiere cálculos o filtrado complejos:

-- Update using a CTE to identify target rows
WITH DiscontinuedProducts AS
(
    SELECT ProductID
    FROM SalesLT.Product
    WHERE SellEndDate < DATEADD(YEAR, -2, GETDATE())
        AND ProductID NOT IN (
            SELECT DISTINCT ProductID 
            FROM SalesLT.SalesOrderDetail
            WHERE ModifiedDate > DATEADD(YEAR, -1, GETDATE())
        )
)
UPDATE SalesLT.Product
SET DiscontinuedDate = GETDATE()
WHERE ProductID IN (SELECT ProductID FROM DiscontinuedProducts);

Esta consulta usa un CTE para identificar los productos que se deben marcar como discontinuos. El CTE busca productos en los que la fecha de finalización vendida es más de dos años atrás y que no han aparecido en ningún detalle de pedido modificado en el último año. A continuación, la instrucción UPDATE establece la DiscontinuedDate para esos productos. Al separar la lógica de selección en un CTE, la consulta resulta más fácil de leer y probar de forma independiente.

Para obtener más información sobre las expresiones de tabla comunes, vea WITH common_table_expression (Transact-SQL) y consultas recursivas mediante expresiones de tabla comunes.

Comentarios

¿Le ha resultado útil esta página?