Thiago Zavaschi R2 www.zavaschi.com

Entendendo as Common Table Expressions – CTE – Parte 2 (Final)

Exemplos de queries recursivas

Bom, mostrei um pouco de teoria, mostrei alguns exemplos didáticos (o de números é bastante utilizado na prática), mas vamos ver mais alguns exemplos:

Vou começar com um exemplo já mostrado aqui no blog (sim, finalmente este é o post sobre o qual falei no referido post :) ).

Este é o método baseado em CTE para realizar Split no SQL Server (atentar para o limite da recursividade, se necessário usar o maxrecursion).

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = 'separar por espaço em branco'
SET @d = ' ' 

;WITH split(i,j) AS
(
SELECT i = 1, j = CHARINDEX(@d, @s + @d)
UNION ALL
SELECT i = j + 1, j = CHARINDEX(@d, @s + @d, j + 1) FROM split
   WHERE CHARINDEX(@d, @s + @d, j + 1) <> 0
)
SELECT SUBSTRING(@s,i,j-i)
FROM split

O método é facilmente adaptável para o seu outro grande uso: parser de textos CSV. Basta apenas trocar o delimitador. Simples não?

DECLARE @s VARCHAR(8000), @d VARCHAR(10)
SET @s = '123;4;thiago@zavaschi.com;2232323'
SET @d = ';' 

-- Continuação mostrada anteriormente.

Um exemplo interessante que sempre cobro em aula (acho que vou ter que parar de cobrar :P) é para realizar o exemplo clássico do fatorial. O resultado final eu diria ser muito elegante.

WITH fat(f, n) AS
(
    SELECT CAST (1 as bigint) as f, 0  as n -- fat de 0 é 1
    UNION ALL
    SELECT CAST (1 as bigint) as f, 1 as n -- fat de 1 é 1
    UNION ALL
    SELECT f * (n + 1), n +1
    FROM fat
    WHERE n < 20 AND n <> 0
    -- 20 é o limite neste caso, pois o fatorial de 21 
    -- não cabe em um tipo bigint. O <> 0 é para cortar a recursão
    -- do primeiro âncora, senão repetiria tudo, faça o teste. 
)
SELECT f
FROM fat
WHERE n = 6 -- troque n pelo fatorial que quer calcular

Para melhorar a legibilidade das queries. podemos encapsular isso em uma função:

CREATE FUNCTION fatorial
(
    @n INT
)
RETURNS INT
AS
BEGIN
    DECLARE @val INT;
    WITH fat(f, n) AS
    (
        SELECT CAST (1 as bigint) as f, 0  as n
        UNION ALL
        SELECT CAST (1 as bigint) as f, 1 as n
        UNION ALL
        SELECT f * (n + 1), n +1
        FROM fat
        WHERE n < 20 AND n <> 0
    )
    SELECT @val = f
    FROM fat
    WHERE n = @n
    RETURN @val
END
GO

-- Testando a função
SELECT dbo.fatorial(3);
SELECT dbo.fatorial(4);
SELECT dbo.fatorial(7);

(Abrindo um parênteses importante:

A única resalva que faço é quanto a estes calculos matemáticos intensos em queries. O SQL Server trabalha muito bem com as ditas set based operations. ou seja, operações baseadas em conjuntos. Operações envolvendo manipulação de strings e cálculos matemáticos complexos não são o forte do SQL Server. Como resolver então, de outra maneira?

Desde o SQL Server 2005 (apareceu junto com as CTEs) o SQL Server tem a CLR integrada ao seu core. Isso quer dizer que é possível criar objetos para o SQL Server (User Defined Types – UDT, Stored Procedures, Functions, etc) programando em uma linguagem compatível com a Common Language Runtime e adicionar o assembly gerado ao seu banco SQL Server. Não fiz testes para esse exemplo do fatorial (farei em um futuro breve), mas é provável que o tempo de execução seja menor se o mesmo estivesse sendo executado pela CLR.

Farei um post detalhando melhor e também na Mundo.NET da edição de Dezembro/2009 sairá um artigo meu sobre como programar com SQLCLR usando C#!

Fechando o parênteses muito importante)

Para o próximo exemplo eu preciso fazer algumas considerações.

Com o SQL Server 2008 surgiu o tipo de dados HIERACHYID e que é, na minha visão, o melhor método de se trabalhar com registros que possuem comportamento hierárquico. Por uma série de motivos: o banco é SQL Server 2005 (ou esta com Compability Level 90), ou é uma estrutura legada, e se você deseja tratar hierarquias?

Commom Table Expressions podem fazer, e fazem, esse papel muito bem. Digamos que temos uma estrutura hirárquica de empregados e cada registro faz uma referência a outro registro que seria o seu supervisor dentro de uma empresa. O diretor da empresa não possui ligação com ninguém, uma vez que ele não teria um supervisor.

Para esse exemplo poderia usar a tabela de empregados do AdventureWorks, mas como nem sempre todos a possuem instalada (deveriam, pois há centenas de exemplos que a utilizam), eu mesmo montarei uma pequena “base”.

Segue o modelo de dados e script para criação da estrutura.

-- Cria tabela base
CREATE TABLE Empregado
(
    id INT PRIMARY KEY,
    nome VARCHAR(50) NOT NULL,
    cargo VARCHAR(50) NOT NULL,
    id_supervisor INT NULL
        CONSTRAINT fk_productSales_pid
        FOREIGN KEY REFERENCES Empregado(id)
);

-- Popula a tabela (reparem na sintaxe do insert, 
-- só é permitida no SQL Server 2008 ou superior).
INSERT INTO Empregado VALUES
(1, 'Thiago Zavaschi', 'Diretor', null),
(2, 'Angelina Jolie', 'Gerente de Vendas', 1),
(3, 'Megan Fox', 'Gerente de Marketing', 1),
(4, 'Uma Thurman', 'Vendedor', 2),
(5, 'Jessica Alba', 'Vendedor', 2),
(6, 'Julia Roberts', 'Estagiário', 3);

Ao executar um simples select nesta tabela temos:

id       nome                       cargo                            id_supervisor
-------- -------------------------- -------------------------------- -------------
1        Thiago Zavaschi            Diretor                          NULL
2        Angelina Jolie             Gerente de Vendas                1
3        Megan Fox                  Gerente de Marketing             1
4        Uma Thurman                Vendedor                         2
5        Jessica Alba               Vendedor                         2
6        Julia Roberts              Estagiário                       3

Creio que todos deverão concordar comigo que esta visulização torna praticamente impossível a noção de hierarquia (e olhe que temos poucos registros, imagine termos centenas dos mesmos.

É fácil retornar qual é o nome do supervisor, mas isso de maneira recursiva não seria nada tranquilo. Vou montar uma query que me retorne o nível da pessoa na hierarquia. Ou seja, para o Diretor o valor será 1, para os gerentes será 2, e para o restante será 3. Com a minah CTE retornando isso, a query subsequente, que consome a CTE podera formatar os resultados, filtrar, enfim, tudo baseado no resultado da consulta recursiva já processada, bacana né? Vamos lá!

;WITH hierarquia(id, nome, cargo, id_supervisor, nivel_hierarquia)
AS
(
    SELECT  id, nome, cargo, id_supervisor, 1 as nivel_hierarquia
    FROM Empregado
    WHERE id_supervisor IS NULL
    -- Perceba que aqui no âncora temos como retorno somente o diretor.
    UNION ALL -- Ligação para a recursão
    SELECT e.id, e.nome, e.cargo, e.id_supervisor, nivel_hierarquia + 1
    FROM Empregado e
    INNER JOIN hierarquia h ON h.id= e.id_supervisor
)
SELECT * FROM hierarquia

A saída deste script é (reparem na coluna nivel_herarquia):

id       nome                 cargo                       id_supervisor nivel_hierarquia
-------- -------------------- --------------------------- ------------- ----------------
1        Thiago Zavaschi      Diretor                     NULL          1
2        Angelina Jolie       Gerente de Vendas           1             2
3        Megan Fox            Gerente de Marketing        1             2
6        Julia Roberts        Estagiário                  3             3
4        Uma Thurman          Vendedor                    2             3
5        Jessica Alba         Vendedor                    2             3

“Ok thiago, mas ainda assim não estou conseguindo visualizar, por exemplo, quem é o supervisor, tem como melhorar?”

Tem sim. uma das utilidades das CTEs é: a mesma retornar um result set “complexo de ser calculado” (como o caso da hierarquia) e deixar para o select mais abaixo fazer a formatação/filtragem/ordenação. Isso eixar a estrutura da sua CTE mais genérica e menos “engessada”, fazendo até mesmo com que você possa reaproveitar esse código em outro lugar.

Por exemplo: quero uma listagem com todo o time operacional (maior valor em nivel_hierarquia) da empresa, com seus respectivos supervisores. A query poderia ficar assim:

;WITH hierarquia(id, nome, cargo, id_supervisor, nivel_hierarquia)
AS
(
    SELECT  id, nome, cargo, id_supervisor, 1 as nivel_hierarquia
    FROM Empregado
    WHERE id_supervisor IS NULL
    -- Perceba que aqui no âncora temos como retorno somente o diretor.
    UNION ALL -- Ligação para a recursão
    SELECT e.id, e.nome, e.cargo, e.id_supervisor, nivel_hierarquia + 1
    FROM Empregado e
    INNER JOIN hierarquia h ON h.id= e.id_supervisor
)
SELECT
h.nome + '/' + h.cargo as [Operacional], -- perceba a formatação ocorrendo aqui
s.nome + '/' + s.cargo as [Tático]
FROM hierarquia h JOIN Empregado s ON h.id_supervisor = s.id
WHERE h.nivel_hierarquia =
(SELECT MAX(nivel_hierarquia) FROM hierarquia) -- perceba que o filtro ocorreu aqui

Com o seguinte output:

Operacional                     Tático
------------------------------- ------------------------------------
Julia Roberts/Estagiário        Megan Fox/Gerente de Marketing
Uma Thurman/Vendedor            Angelina Jolie/Gerente de Vendas
Jessica Alba/Vendedor           Angelina Jolie/Gerente de Vendas

Tranquilo né? E assim poderíamos evoluir de “N” formas, cada uma atendendo a uma necessidade específica.

Common table Expressions também podem ser utilizadas de outras formas, para fazer tratamento de datas, carga, e muitas outras funções (ver links no final do post para maiores informações).

O que mais saber sobre Common Table Expressions (CTE) ?

Bom, já vimos bastante coisa sobre o que é uma CTE, como funciona, onde se aplica (com alguns exemplos), mas o que mais devemos saber?

Devemos saber que há restrições sobre oq eu podemos usar ou não em CTEs. Vou listar alguams das que eu considero principais (no Books Online – BOL, há a lista completa, ver link ao final do post).

Bom acredito que a primeira coisa que é importante saber é que NÃO é permitido fazer uma referência recursiva dentro de uma subquery. Vou usar o exemplo anterior para demonstrar:

...
UNION ALL
    SELECT e.id, e.nome, e.cargo, e.id_supervisor,
    (SELECT h.nivel_hierarquia_2 FROM hierarquia) -- ISSO NÃO É PERMITIDO.
    FROM Empregado e
    ...
)
...

O que nos gera um erro similar a:

Msg 465, Level 16, State 1, Line 32
Recursive references are not allowed in subqueries.

O que mais não é permitido na definição do membro recursivo? Segue a lista:

• SELECT DISTINCT
• GROUP BY
• HAVING
• Scalar aggregation
• TOP
• LEFT, RIGHT, OUTER JOIN (obs: o INNER JOIN é permitido)
• A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

Você, leitor, deve ter percebido que eu normalmente iniciei minhas CTE’s com um ponto e vírgula (‘;’), não, não fiquei maluco e nem coloquei por engano.
A questão é que a sintaxe para execução da CTE obriga que o statement anterior termine com ponto e vírgula, então acabo sempre colocando, já que o statement anterior normalmente não possui “;” ao seu término!

Há algumas guidelines no books online e vou apenas trancrever aqui, em inglês mesmo (consultar o link no final para o texto completo – em inglês), omiti sobre o maxrecursion que eu já comentei.

Using CLR Integration in SQL Server 2005 (Se alguém quiser ir se adiantando um pouco no assunto!)
http://msdn.microsoft.com/en-us/library/ms345136%28SQL.90%29.aspx

Finalizando

UFA! Bom pessoal, espero que todos tenham chegado até aqui, e espero também que gostem tanto do artigo quanto eu gostei de fazê-lo!

Utilizem como referência para consultas futuras e qualquer sugestão estou aberto para ouvir!

Comentei que há o tipo de dados HIERARCHYID, presente no SQL Server 2008 e superirores) para manipular registros essencialmente hierárquicos, mas acho que isso não compete a este post, abordo isso mais profundamente numa próxima oportunidade!

Grande abraço!
Thiago Zavaschi

PS: Tive que dividir o artigo em duas partes, pois o Spaces não deixa publicar posts grandes (para a primeira parte veja o post anterior).