T-SQL avançado: CTEs, window functions e APPLY para análise complexa

Muitos analistas conhecem GROUP BY e JOIN, mas as funcionalidades avançadas do T-SQL — CTEs recursivas, window functions e APPLY — permitem resolver em poucas linhas problemas que de outro modo exigiriam cursores ou código imperativo.

CTEs Recursivas — hierarquias em SQL

-- Hierarquia de departamentos com nível e caminho completo
WITH CTE_Hierarquia AS (
    -- Âncora: raiz da hierarquia
    SELECT id_dept, nome, id_dept_pai, 0 AS nivel, CAST(nome AS VARCHAR(500)) AS caminho
    FROM dbo.Departamentos WHERE id_dept_pai IS NULL

    UNION ALL

    -- Parte recursiva
    SELECT d.id_dept, d.nome, d.id_dept_pai,
           h.nivel + 1,
           CAST(h.caminho + ' > ' + d.nome AS VARCHAR(500))
    FROM dbo.Departamentos d
    JOIN CTE_Hierarquia h ON d.id_dept_pai = h.id_dept
)
SELECT * FROM CTE_Hierarquia ORDER BY caminho;

Window Functions — análise sem GROUP BY

SELECT
    id_cliente,
    data_venda,
    valor,
    -- Ranking dentro de cada cliente
    ROW_NUMBER()  OVER (PARTITION BY id_cliente ORDER BY data_venda DESC) AS rn,
    -- Total acumulado
    SUM(valor)    OVER (PARTITION BY id_cliente ORDER BY data_venda ROWS UNBOUNDED PRECEDING) AS total_acum,
    -- Comparação com venda anterior
    LAG(valor, 1) OVER (PARTITION BY id_cliente ORDER BY data_venda) AS venda_anterior,
    valor - LAG(valor, 1) OVER (PARTITION BY id_cliente ORDER BY data_venda) AS variacao
FROM dbo.Vendas;

CROSS APPLY — joins com funções e subqueries correlacionadas

-- Últimas 3 compras de cada cliente (impossível com JOIN simples)
SELECT c.nome, v.data_venda, v.valor
FROM dbo.Clientes c
CROSS APPLY (
    SELECT TOP 3 data_venda, valor
    FROM dbo.Vendas
    WHERE id_cliente = c.id_cliente
    ORDER BY data_venda DESC
) v;

-- OUTER APPLY inclui clientes sem compras (como LEFT JOIN)

Conclusão

CTEs melhoram a legibilidade de queries complexas, window functions eliminam a necessidade de subconsultas para análises comparativas, e APPLY resolve problemas de top-N por grupo de forma elegante. Juntas, estas três funcionalidades cobrem 90% dos casos de análise avançada em T-SQL.