Banco de Dados Introducao

Capítulo 3

1- Expressão de Tabela Comum - Common Table Expression - CTE

Essa seção é baseada no tutorial: An Introduction to MySQL CTE

O MySQL introduziu a expressão de tabela comum (CTE-Common Table Expression) a partir da versão 8.0. Então para executar os exemplos apresentados nessa seção devemos ter o MySQL 8.0+ .

Uma CTE (uma expressão de tabela comum) é um conjunto de resultados temporários que damos um nome e que passa a existir dentro do escopo de execução de uma única instrução SQL, por exemplo, SELECT, INSERT, UPDATE ou DELETE.

Uma CTE não é armazenada como um objeto e dura apenas durante a execução de uma consulta.

É semelhante a uma Tabela Derivada.

Ao contrário de uma tabela derivada, uma CTE pode ser autorreferenciada (uma CTE recursiva) ou pode ser referenciada várias vezes na mesma consulta. Além disso, um CTE oferece melhor legibilidade e desempenho em comparação com uma tabela derivada.

Sintaxe da MySQL CTE

A estrutura de um CTE inclui o nome, uma lista de colunas opcionais e uma consulta que define o CTE. Depois que a CTE é definida, podemos usá-la como uma visualização em uma instrução SELECT, INSERT, UPDATE, DELETE ou CREATE VIEW. A sintaxe básica de um CTE é:

Sintaxe de uma CTE

                            
WITH cte_name (column_list) AS (
    query
)
consulta com a cte 
(por exemplo:

 SELECT * FROM cte_name;)

Nos exemplos que se seguem, usamos o banco de dados classicmodels dado no tutorial sobre CTE no qual baseamos essa seção. Veja mais clicando aqui. Ou fazendo o dowload do banco aqui.

Esse banco possui a tabela customers que possui os campos mostrados na figura abaixo.

Exemplo 1

                            
WITH customers_in_usa AS (
    SELECT 
        customerName, state
    FROM
        customers
    WHERE
        country = 'USA'
) SELECT 
    customerName
 FROM
    customers_in_usa
 WHERE
    state = 'CA'
 ORDER BY customerName;



---------------------------------------
mysql> WITH customers_in_usa AS (
    ->     SELECT 
    ->         customerName, state
    ->     FROM
    ->         customers
    ->     WHERE
    ->         country = 'USA'
    -> ) SELECT 
    ->     customerName
    ->  FROM
    ->     customers_in_usa
    ->  WHERE
    ->     state = 'CA'
    ->  ORDER BY customerName;
+------------------------------+
| customerName                 |
+------------------------------+
| Boards & Toys Co.            |
| Collectable Mini Designs Co. |
| Corporate Gift Ideas Co.     |
| Men 'R' US Retailers, Ltd.   |
| Mini Gifts Distributors Ltd. |
| Mini Wheels Co.              |
| Signal Collectibles Ltd.     |
| Technics Stores Inc.         |
| The Sharp Gifts Warehouse    |
| Toys4GrownUps.com            |
| West Coast Collectables Co.  |
+------------------------------+
11 rows in set (0.02 sec)

Neste exemplo, o nome do CTE é "customers_in_usa", a consulta que define o CTE retorna duas colunas customerName e state. Portanto, o CTE customers_in_usa retorna todos os clientes localizados nos EUA.

Depois de definir o CTE customers_in_usa, nós o referenciamos na instrução SELECT para selecionar apenas clientes localizados na Califórnia.

Veja o seguinte exemplo:

Exemplo 2

                            
WITH topsales2003 AS (
    SELECT 
        salesRepEmployeeNumber employeeNumber,
        SUM(quantityOrdered * priceEach) sales
    FROM
        orders
            INNER JOIN
        orderdetails USING (orderNumber)
            INNER JOIN
        customers USING (customerNumber)
    WHERE
        YEAR(shippedDate) = 2003
            AND status = 'Shipped'
    GROUP BY salesRepEmployeeNumber
    ORDER BY sales DESC
    LIMIT 5
)
SELECT 
    employeeNumber, 
    firstName, 
    lastName, 
    sales
FROM
    employees
        JOIN
    topsales2003 USING (employeeNumber);

    -----------------------------------------

    mysql> WITH topsales2003 AS (
    ->     SELECT 
    ->         salesRepEmployeeNumber employeeNumber,
    ->         SUM(quantityOrdered * priceEach) sales
    ->     FROM
    ->         orders
    ->             INNER JOIN
    ->         orderdetails USING (orderNumber)
    ->             INNER JOIN
    ->         customers USING (customerNumber)
    ->     WHERE
    ->         YEAR(shippedDate) = 2003
    ->             AND status = 'Shipped'
    ->     GROUP BY salesRepEmployeeNumber
    ->     ORDER BY sales DESC
    ->     LIMIT 5
    -> )
    -> SELECT 
    ->     employeeNumber, 
    ->     firstName, 
    ->     lastName, 
    ->     sales
    -> FROM
    ->     employees
    ->         JOIN
    ->     topsales2003 USING (employeeNumber);
+----------------+-----------+-----------+-----------+
| employeeNumber | firstName | lastName  | sales     |
+----------------+-----------+-----------+-----------+
|           1165 | Leslie    | Jennings  | 413219.85 |
|           1370 | Gerard    | Hernandez | 295246.44 |
|           1401 | Pamela    | Castillo  | 289982.88 |
|           1621 | Mami      | Nishi     | 267249.40 |
|           1501 | Larry     | Bott      | 261536.95 |
+----------------+-----------+-----------+-----------+
5 rows in set (0.01 sec)

Neste exemplo, o CTE retorna os 5 principais representantes de vendas em 2003. Depois disso, nos referimos ao CTE topsales2003 para obter informações adicionais sobre o representante de vendas, incluindo nome e sobrenome.

No próximo exemplo, temos duas CTEs na mesma consulta. A primeira CTE (salesrep) obtém os funcionários cujos cargos são o representante de vendas. A segunda CTE (customer_salesrep) faz referência à primeira CTE na cláusula INNER JOIN para obter o representante de vendas e os clientes de quem cada representante de vendas é responsável. Depois de obter a segunda CTE, consultamos os dados dessa CTE usando uma instrução SELECT simples com a cláusula ORDER BY.

Exemplo 3

                            
WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;


-------------------------------------------------------
mysql> WITH salesrep AS (
    ->     SELECT 
    ->         employeeNumber,
    ->         CONCAT(firstName, ' ', lastName) AS salesrepName
    ->     FROM
    ->         employees
    ->     WHERE
    ->         jobTitle = 'Sales Rep'
    -> ),
    -> customer_salesrep AS (
    ->     SELECT 
    ->         customerName, salesrepName
    ->     FROM
    ->         customers
    ->             INNER JOIN
    ->         salesrep ON employeeNumber = salesrepEmployeeNumber
    -> )
    -> SELECT 
    ->     *
    -> FROM
    ->     customer_salesrep
    -> ORDER BY customerName LIMIT 5;
+-------------------------+------------------+
| customerName            | salesrepName     |
+-------------------------+------------------+
| Alpha Cognac            | Gerard Hernandez |
| American Souvenirs Inc  | Foon Yue Tseng   |
| Amica Models & Co.      | Pamela Castillo  |
| Anna's Decorations, Ltd | Andy Fixter      |
| Atelier graphique       | Gerard Hernandez |
+-------------------------+------------------+
5 rows in set (0.00 sec)

(No exemplo no mysql limitei a saída às 5 primeiras).

<4>Exemplo 4

Repita a consulta do Exemplo 3, agora sem limitar a saída a 5 linhas.

Solução: a consulta é a mesma, bastando tirar o "LIMIT 5"

Exemplo 4

                            





WITH salesrep AS (
    SELECT 
        employeeNumber,
        CONCAT(firstName, ' ', lastName) AS salesrepName
    FROM
        employees
    WHERE
        jobTitle = 'Sales Rep'
),
customer_salesrep AS (
    SELECT 
        customerName, salesrepName
    FROM
        customers
            INNER JOIN
        salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT 
    *
FROM
    customer_salesrep
ORDER BY customerName;

Consultas Recursivas

Uma expressão de tabela comum recursiva (recursive common table expression, RCTE) recursiva é uma CTE (common table expression) que possui uma subconsulta que se refere à CTE. Abaixo é mostrada a sintaxe de uma CTE recursiva:

    

WITH RECURSIVE cte_name AS (
    initial_query  -- anchor member
    UNION ALL
    recursive_query -- recursive member that references to the CTE name
)
SELECT * FROM cte_name;

Uma CTE recursiva possui três partes:

Uma consulta inicial que forma o conjunto de resultados base da estrutura CTE. A parte inicial da consulta é chamada de membro âncora.
Uma parte de consulta recursiva é uma consulta que faz referência ao nome CTE, portanto, é chamada de membro recursivo. O membro recursivo é unido ao membro âncora pelo operador UNION ALL ou UNION DISTINCT.
Uma condição de finalização que garante que a recursão pare quando o membro recursivo não retornar nenhuma linha.

A ordem de execução de um CTE recursivo é a seguinte:

Primeiro, separe os membros em dois: membros âncora e recursivos.
Em seguida, execute o membro âncora para formar o conjunto de resultados base (Ro) e use este conjunto de resultados base para a próxima iteração.
Em seguida, execute o membro recursivo com o resultado Ri definido como uma entrada e faça Ri + 1 como uma saída.
Depois repita a terceira etapa até que o membro recursivo retorne um conjunto de resultados vazio, ou seja, a condição de término seja atendida.
Finalmente, combine os conjuntos de resultados de R0 a Rn usando o operador UNION ALL.

Importante: Restrições de membros recursivos

O membro recursivo não deve c

Funções agregadas, por exemplo, MAX, MIN, SUM, AVG, COUNT, etc.
Cláusula GROUP BY
Cláusula ORDER BY
Cláusula LIMIT
A opção DISTINCT

A restrição acima não se aplica ao membro âncora. Além disso, a proibição de DISTINCT se aplica somente quando você usa o operador UNION. Caso você use o operador UNION DISTINCT, o DISTINCT é permitido.

Além disso, o membro recursivo só pode fazer referência ao nome CTE uma vez e em sua cláusula FROM.

Exemplo 5: Uma CTE recursiva simples

Exemplo 5: Uma CTE recursiva bem simples

                            
WITH RECURSIVE cte_count (n) 
AS (
      SELECT 1
      UNION ALL
      SELECT n + 1 
      FROM cte_count 
      WHERE n < 3
    )
SELECT n 
FROM cte_count;

Nesse exemplo a query

    
        SELECT 1

é o "membro âncora" e retorna 1 como o "resultado base" (valor inicial da recursão).

O "membro recursivo" é dado por:

    
    SELECT n + 1 
    FROM cte_count 
    WHERE n < 3

Repare que ele referencia o nome da CTE (que no caso é cte_count).

A expressão n < 3 é a condição de parada: uma vez n atingindo o valor 3 o "membro recursivo" retorna VAZIO e a recursão é interrompida.

A figura seguinte (adaptada de https://www.mysqltutorial.org/mysql-recursive-cte/ ilustra os elementos de uma CTE recursiva.

A seguir mostramos o resultado dessa query recursiva simples (mudamos o nome para exemp5 mas pode ser qualquer nome válido).

Exemplo 5, continuação

                            
mysql> WITH RECURSIVE exemp5 (n) 
    ->  AS (       
    -> SELECT 1
    -> UNION ALL
    ->  SELECT n + 1 
    ->   FROM exemp5
    ->   WHERE n < 3
    -> ) 
    -> SELECT n FROM exemp5;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

As etapas de execução da CTE recursiva são as seguintes:

Primeiro, separe a âncora e os membros recursivos.
Em seguida, o membro âncora forma a linha inicial (SELECT 1), portanto a primeira iteração produz 1 + 1 = 2 com n = 1.
A segunda iteração opera na saída da primeira iteração (2) e produz 2 + 1 = 3 com n = 2.
Depois disso, antes da terceira operação (n = 3), a condição de término (n < 3) é atendida, portanto, a consulta é interrompida.
Finalmente, combine todos os conjuntos de resultados 1, 2 e 3 usando o operador UNION ALL

Algums exemplos de estruturas recursivas

A seguir apresentamos alguns exemplos, extraídos de https://dev.mysql.com/doc/refman/8.0/en/with.html

Exemplo 6

                            

mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, 'abc' AS str
    ->   UNION ALL
    ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    -> )
    -> SELECT * FROM cte;
ERROR 1406 (22001): Data too long for column 'str' at row 1

fazendo a correção... 


mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
    ->   UNION ALL
    ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    -> )
    -> SELECT * FROM cte;
+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+
3 rows in set (0.00 sec)

Exemplo 7

                            

mysql> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.03 sec)

Exemplo 8

                            
mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, 1 AS p, -1 AS q
    ->   UNION ALL
    ->   SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+
5 rows in set (0.00 sec)

Exercício 1

Execute os exemplos apresentados nessa seção.

Exercício 2

Usando o banco de dados livraria crie um CTE que tenha duas colunas: nome do livro e o assunto (descrição do assunto). Liste o conteúdo da CTE

Veja uma possível solução:


mysql> with LivroAssunto AS 
(
     select titulo,assunto.descricao as assunto 
     from livro inner join assunto
      on assunto = sigla 
) 
select * from LivroAssunto;
+-----------------------------------+----------------+
| titulo                            | assunto        |
+-----------------------------------+----------------+
| Banco de Dados para Web           | Banco de Dados |
| Programando em Linguagem C        | Programação    |
| Programando em Linguagem C++      | Programação    |
| Banco de Dados na Bioinformática  | Banco de Dados |
| Redes de Computadores             | Redes          |
| teste                             | Programação    |
+-----------------------------------+----------------+
6 rows in set (0.00 sec)

voltar ao início da página

Página Anterior Próxima Página