T-SQL avanzado: CTEs, window functions y APPLY para análisis complejo

Muchos analistas conocen GROUP BY y JOIN, pero las funcionalidades avanzadas de T-SQL — CTEs recursivas, window functions y APPLY — resuelven en pocas líneas problemas que de otro modo exigirían cursores o código imperativo.

CTEs recursivas — jerarquías en SQL

-- Jerarquía de departamentos con nivel y ruta completa
WITH CTE_Jerarquia AS (
    -- Ancla: raíz de la jerarquía
    SELECT id_dept, nombre, id_dept_padre, 0 AS nivel, CAST(nombre AS VARCHAR(500)) AS ruta
    FROM dbo.Departamentos WHERE id_dept_padre IS NULL

    UNION ALL

    -- Parte recursiva
    SELECT d.id_dept, d.nombre, d.id_dept_padre,
           h.nivel + 1,
           CAST(h.ruta + ' > ' + d.nombre AS VARCHAR(500))
    FROM dbo.Departamentos d
    JOIN CTE_Jerarquia h ON d.id_dept_padre = h.id_dept
)
SELECT * FROM CTE_Jerarquia ORDER BY ruta;

Window Functions — análisis sin GROUP BY

SELECT
    id_cliente,
    fecha_venta,
    importe,
    -- Ranking dentro de cada cliente
    ROW_NUMBER()  OVER (PARTITION BY id_cliente ORDER BY fecha_venta DESC) AS rn,
    -- Total acumulado
    SUM(importe)  OVER (PARTITION BY id_cliente ORDER BY fecha_venta ROWS UNBOUNDED PRECEDING) AS total_acum,
    -- Comparación con la venta anterior
    LAG(importe, 1) OVER (PARTITION BY id_cliente ORDER BY fecha_venta) AS venta_anterior,
    importe - LAG(importe, 1) OVER (PARTITION BY id_cliente ORDER BY fecha_venta) AS variacion
FROM dbo.Ventas;

CROSS APPLY — joins con funciones y subconsultas correlacionadas

-- Últimas 3 compras de cada cliente (imposible con un JOIN simple)
SELECT c.nombre, v.fecha_venta, v.importe
FROM dbo.Clientes c
CROSS APPLY (
    SELECT TOP 3 fecha_venta, importe
    FROM dbo.Ventas
    WHERE id_cliente = c.id_cliente
    ORDER BY fecha_venta DESC
) v;

-- OUTER APPLY incluye clientes sin compras (como LEFT JOIN)

Conclusión

Las CTEs mejoran la legibilidad de consultas complejas, las window functions eliminan la necesidad de subconsultas para análisis comparativos, y APPLY resuelve problemas de top-N por grupo de forma elegante. Juntas, estas tres funcionalidades cubren el 90% de los casos de análisis avanzado en T-SQL.