Como trabalhar com CTEs (expressões de tabela comuns)

Consulte também:

CONNECT BY , WITH

Neste tópico:

O que é uma CTE?

Uma CTE (expressão de tabela comum) é uma subconsulta nomeada definida em uma cláusula WITH. Você pode pensar na CTE como uma exibição temporária para uso na instrução que define a CTE. A CTE define o nome da exibição temporária, uma lista opcional de nomes de colunas e uma expressão de consulta (ou seja, uma instrução SELECT). O resultado da expressão da consulta é efetivamente uma tabela. Cada coluna dessa tabela corresponde a uma coluna na lista (opcional) de nomes de colunas.

O seguinte código é um exemplo de uma consulta que utiliza uma CTE:

WITH
    my_cte (cte_col_1, cte_col_2) AS (
        SELECT col_1, col_2
            FROM ...
    )
SELECT ... FROM my_cte;
Copy

No exemplo acima, a CTE começa na linha que contém my_cte (cte_col_1, cte_col_2) AS ( e termina na linha que contém ).

Evite escolher nomes de CTE que correspondam ao seguinte:

  • Nomes de funções SQL

  • Tabelas, exibições ou exibições materializadas. Se uma consulta define uma CTE com um determinado nome, a CTE tem precedência sobre tabelas, etc.

A CTE pode ser recursiva ou não recursiva. Uma CTE recursiva é uma CTE que se refere a si mesma. Uma CTE recursiva pode unir uma tabela a si mesma tantas vezes quanto necessário para processar dados hierárquicos na tabela.

CTEs aumentam a modularidade e simplificam a manutenção.

Dados hierárquicos e CTEs recursivas

CTEs recursivas permitem processar dados hierárquicos, tais como um detalhamento de peças (componente, subcomponentes) ou uma hierarquia de gerenciamento (gerente, funcionários). Para obter mais informações sobre dados hierárquicos e outras formas de consulta de dados hierárquicos, consulte Consulta de dados hierárquicos.

Uma CTE recursiva permite que você entre em todos os níveis de uma hierarquia sem saber com antecedência quantos níveis existem.

Visão geral da sintaxe da CTE recursiva

Esta seção fornece uma visão geral da sintaxe e como a sintaxe se relaciona com a forma como a recursividade funciona:

WITH [ RECURSIVE ] <cte_name> AS
(
  <anchor_clause> UNION ALL <recursive_clause>
)
SELECT ... FROM ...;
Copy
Onde:
anchor_clause

seleciona uma linha inicial ou um conjunto de linhas que representam o topo da hierarquia. Por exemplo, se você estiver tentando exibir todos os funcionários de uma empresa, a cláusula de âncora selecionaria o presidente da empresa.

A cláusula de âncora é uma instrução SELECT e pode conter qualquer constructo SQL com suporte. A cláusula de âncora não pode fazer referência ao cte_name.

recursive_clause

seleciona a próxima camada da hierarquia com base na camada anterior. Na primeira iteração, a camada anterior é o resultado estabelecido a partir da cláusula de âncora. Nas iterações subsequentes, a camada anterior é a mais recente iteração concluída.

A recursive_clause é uma instrução SELECT; no entanto, a instrução é restrita a projeções, junções e filtros. Além disso, o seguinte não é permitido na instrução:

  • Funções agregadas ou de janela.

  • GROUP BY, ORDER BY, LIMIT ou DISTINCT.

A cláusula recursiva pode fazer referência ao cte_name como uma tabela ou exibição regular.

Para uma descrição mais detalhada da sintaxe, consulte WITH.

Logicamente, a CTE recursiva é avaliada da seguinte forma:

  1. A anchor_clause é avaliada e seu resultado é gravado tanto para o conjunto de resultados finais quanto para uma tabela de trabalho. O cte_name é efetivamente um alias para a tabela de trabalho; em outras palavras, uma consulta referenciando o cte_name lê a partir da tabela de trabalho.

  2. Enquanto a tabela de trabalho não estiver vazia:

    1. A recursive_clause é avaliada, usando o conteúdo atual da tabela de trabalho onde quer que cte_name seja referenciado.

    2. O resultado de recursive_clause é gravado tanto para o conjunto de resultados finais como para uma tabela temporária.

    3. A tabela de trabalho é substituída pelo conteúdo da tabela temporária.

Com efeito, a saída da iteração anterior é armazenada em uma tabela de trabalho chamada cte_name, e essa tabela é então uma das entradas para a próxima iteração. A tabela de trabalho contém apenas o resultado da mais recente iteração. Os resultados acumulados de todas as iterações até o momento são armazenados em outro lugar.

Após a iteração final, os resultados acumulados estão disponíveis para a instrução principal SELECT, referenciando cte_name.

Considerações sobre CTE recursiva

Potencial para loops infinitos

A construção de uma CTE recursiva de forma incorreta pode causar um loop infinito. Nesses casos, a consulta continua a ser executada até que tenha sucesso, até que o tempo limite ocorra (por exemplo, ultrapassa o número de segundos especificado pelo parâmetro STATEMENT_TIMEOUT_IN_SECONDS) ou até que você cancele a consulta.

Para obter mais informações sobre como podem ocorrer loops infinitos e para diretrizes sobre como evitar este problema, consulte Solução de problemas de uma CTE recursiva.

Hierarquias não contíguas

Este tópico descreveu hierarquias e como as relações pai-filho podem ser usadas por CTEs recursivas. Em todos os exemplos deste tópico, as hierarquias são contíguas.

Para obter mais informações sobre hierarquias não contíguas, consulte Consulta de dados hierárquicos.

Exemplos

Esta seção inclui tanto exemplos de CTEs não recursivas como recursivas para contrastar os dois tipos.

CTE não recursiva, com dois níveis e autojunção

Este exemplo utiliza uma tabela de funcionários e gerentes:

CREATE OR REPLACE TABLE employees (title VARCHAR, employee_ID INTEGER, manager_ID INTEGER);
Copy
INSERT INTO employees (title, employee_ID, manager_ID) VALUES
    ('President', 1, NULL),  -- The President has no manager.
        ('Vice President Engineering', 10, 1),
            ('Programmer', 100, 10),
            ('QA Engineer', 101, 10),
        ('Vice President HR', 20, 1),
            ('Health Insurance Analyst', 200, 20);
Copy

Uma autojunção de dois níveis desta tabela de funcionários parece com:

SELECT
     emps.title,
     emps.employee_ID,
     mgrs.employee_ID AS MANAGER_ID, 
     mgrs.title AS "MANAGER TITLE"
  FROM employees AS emps LEFT OUTER JOIN employees AS mgrs
    ON emps.manager_ID = mgrs.employee_ID
  ORDER BY mgrs.employee_ID NULLS FIRST, emps.employee_ID;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MANAGER TITLE              |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

A consulta acima mostra todos os funcionários. Os funcionários de cada gerente aparecem perto de seu gerente no relatório. Entretanto, o relatório não mostra visualmente a hierarquia. Sem examinar cuidadosamente os dados, você não sabe quantos níveis existem na organização, e precisa ler cada linha para ver quais funcionários estão associados a um gerente específico.

Uma CTE recursiva pode exibir estes dados hierárquicos como uma árvore lateral, como mostrado na próxima seção.

CTE recursiva com saída recuada

Abaixo estão dois exemplos de utilização de uma CTE recursiva:

  • O primeiro usa recuos para mostrar os diferentes níveis da hierarquia. Para simplificar este exemplo, o código não produz as linhas em uma ordem específica.

  • O segundo exemplo usa recuos e mostra os funcionários de cada gerente imediatamente abaixo de seu gerente.

Saída não ordenada

Aqui está o primeiro exemplo.

 1)    WITH RECURSIVE managers
 2)          (indent, employee_ID, manager_ID, employee_title)
 3)        AS
 4)          (
 5)
 6)            SELECT '' AS indent, employee_ID, manager_ID, title AS employee_title
 7)              FROM employees
 8)              WHERE title = 'President'
 9)
10)            UNION ALL
11)
12)           SELECT indent || '--- ',
13)               employees.employee_ID, employees.manager_ID, employees.title
14)             FROM employees JOIN managers
15)               ON employees.manager_ID = managers.employee_ID
16)         )
17)
18)     SELECT indent || employee_title AS Title, employee_ID, manager_ID
19)       FROM managers
20)     ;
Copy

A consulta inclui as seguintes seções:

  • A linha 2 contém os nomes das colunas para a “exibição” (CTE).

  • As linhas 4 - 16 contêm a CTE.

  • As linhas 6 - 8 contêm a cláusula de âncora da CTE.

  • As linhas 12 - 15 contêm a cláusula recursiva da CTE.

  • As linhas 18 - 19 contêm o SELECT principal que usa a CTE como uma exibição. Esta SELECT faz referência a:

    • O nome da CTE (managers), definido na linha 1.

    • As colunas da CTE (indent, employee_id, etc.) definidas na linha 2.

A CTE contém duas instruções SELECT:

  • A instrução SELECT na cláusula de âncora é executada uma vez e fornece o conjunto de linhas a partir do primeiro nível (superior) da hierarquia.

  • A SELECT na cláusula recursiva pode fazer referência à CTE. Você pode pensar na consulta como iteração, com cada iteração expandindo os resultados da consulta das iterações anteriores.

No exemplo do gerente/funcionário, a cláusula de âncora emite a primeira linha, que descreve o presidente da empresa.

Na próxima iteração da cláusula recursiva, a cláusula recursiva encontra todas as linhas cujo gerente é o presidente da empresa (ou seja, encontra todos os vice-presidentes). A terceira iteração encontra todos os funcionários cujo gerente é um dos vice-presidentes. A iteração continua até que haja uma iteração na qual todas as linhas recuperadas são linhas de funcionários de nível folha que não gerenciam ninguém. A instrução faz mais uma iteração, procurando (mas não encontrando) quaisquer funcionários cujos gerentes sejam funcionários de nível folha. A iteração produz 0 linhas e para.

Ao longo destas iterações, a cláusula UNION ALL acumula os resultados. Os resultados de cada iteração são adicionados aos resultados das iterações anteriores. Após a conclusão da última iteração, as linhas acumuladas (como qualquer linha produzida em uma cláusula WITH) são disponibilizadas para a cláusula SELECT principal da consulta. A SELECT principal pode então consultar essas linhas.

Este exemplo particular de consulta usa recuos para mostrar a natureza hierárquica dos dados. Se você olhar para a saída, verá que quanto mais baixo o nível do funcionário, mais os dados desse funcionário são recuados.

O recuo é controlado pela coluna chamada indent. O recuo começa com 0 caracteres (uma cadeia de caracteres vazia na cláusula de âncora), e aumenta em 4 caracteres (---) para cada iteração (ou seja, para cada nível da hierarquia).

Não surpreendentemente, é muito importante construir as junções corretamente e selecionar as colunas corretas na cláusula recursiva. As colunas em SELECT da cláusula recursiva devem corresponder corretamente às colunas da cláusula de âncora. Lembre-se de que a consulta começa com o presidente, depois seleciona os vice-presidentes e depois seleciona as pessoas que se reportam diretamente aos vice-presidentes, etc. Cada iteração procura funcionários cujo campo manager_id corresponda a um dos valores managers.employee_id produzidos na iteração anterior.

Expressado de outra forma, a ID do funcionário na “exibição” de gerentes é a ID de gerente para o próximo nível de funcionários. As IDs dos funcionários devem progredir para baixo através da hierarquia (presidente, vice-presidente, gerente sênior, gerente júnior, etc.) durante cada iteração. Se as IDs de funcionário não progredirem, então a consulta pode fazer loop infinito (se o mesmo manager_ID continuar aparecendo na coluna managers.employee_ID em diferentes iterações), ou pular um nível, ou falhar de outras maneiras.

Saída ordenada

O exemplo anterior não tinha cláusula ORDER BY, portanto, mesmo que o registro de cada funcionário esteja recuado corretamente, cada funcionário não necessariamente aparecia diretamente abaixo de seu gerente. O exemplo abaixo gera saída com recuo correto, e com os funcionários de cada gerente diretamente abaixo de seu próprio gerente.

A cláusula ORDER BY da consulta usa uma coluna adicional, chamada sort_key. A chave de ordenação se acumula à medida que a cláusula recursiva itera; você pode pensar na chave de ordenação como uma sequência que contém toda a cadeia de comando acima de você (seu gerente, o gerente de seu gerente, etc.). A pessoa mais graduada nessa cadeia de comando (o presidente) está no início da cadeia de caracteres da chave de ordenação. Embora você normalmente não mostre a chave de ordenação, a consulta abaixo inclui a chave de ordenação na saída, para que seja mais fácil entender a saída.

Cada iteração deve aumentar o comprimento da chave de ordenação na mesma quantidade (mesmo número de caracteres), assim a consulta usa uma UDF (função definida pelo usuário) chamada skey, com a seguinte definição, para gerar segmentos de comprimento consistente da chave de ordenação:

CREATE OR REPLACE FUNCTION skey(ID VARCHAR)
  RETURNS VARCHAR
  AS
  $$
    SUBSTRING('0000' || ID::VARCHAR, -4) || ' '
  $$
  ;
Copy

Aqui está um exemplo de saída da função SKEY:

SELECT skey(12);
+----------+
| SKEY(12) |
|----------|
| 0012     |
+----------+
Copy

Aqui está a versão final da consulta. Isto coloca os funcionários de cada gerente imediatamente abaixo desse gerente e insere recuos com base no “nível” do funcionário:

WITH RECURSIVE managers 
      -- Column list of the "view"
      (indent, employee_ID, manager_ID, employee_title, sort_key) 
    AS 
      -- Common Table Expression
      (
        -- Anchor Clause
        SELECT '' AS indent, 
            employee_ID, manager_ID, title AS employee_title, skey(employee_ID)
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT indent || '--- ',
            employees.employee_ID, employees.manager_ID, employees.title, 
            sort_key || skey(employees.employee_ID)
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT 
         indent || employee_title AS Title, employee_ID, 
         manager_ID, 
         sort_key
    FROM managers
    ORDER BY sort_key
  ;
+----------------------------------+-------------+------------+-----------------+
| TITLE                            | EMPLOYEE_ID | MANAGER_ID | SORT_KEY        |
|----------------------------------+-------------+------------+-----------------|
| President                        |           1 |       NULL | 0001            |
| --- Vice President Engineering   |          10 |          1 | 0001 0010       |
| --- --- Programmer               |         100 |         10 | 0001 0010 0100  |
| --- --- QA Engineer              |         101 |         10 | 0001 0010 0101  |
| --- Vice President HR            |          20 |          1 | 0001 0020       |
| --- --- Health Insurance Analyst |         200 |         20 | 0001 0020 0200  |
+----------------------------------+-------------+------------+-----------------+
Copy

A consulta seguinte mostra como fazer referência a um campo do nível anterior (superior) na hierarquia; preste especial atenção à coluna mgr_title:

WITH RECURSIVE managers 
      -- Column names for the "view"/CTE
      (employee_ID, manager_ID, employee_title, mgr_title) 
    AS
      -- Common Table Expression
      (

        -- Anchor Clause
        SELECT employee_ID, manager_ID, title AS employee_title, NULL AS mgr_title
          FROM employees
          WHERE title = 'President'

        UNION ALL

        -- Recursive Clause
        SELECT 
            employees.employee_ID, employees.manager_ID, employees.title, managers.employee_title AS mgr_title
          FROM employees JOIN managers 
            ON employees.manager_ID = managers.employee_ID
      )

  -- This is the "main select".
  SELECT employee_title AS Title, employee_ID, manager_ID, mgr_title
    FROM managers
    ORDER BY manager_id NULLS FIRST, employee_ID
  ;
+----------------------------+-------------+------------+----------------------------+
| TITLE                      | EMPLOYEE_ID | MANAGER_ID | MGR_TITLE                  |
|----------------------------+-------------+------------+----------------------------|
| President                  |           1 |       NULL | NULL                       |
| Vice President Engineering |          10 |          1 | President                  |
| Vice President HR          |          20 |          1 | President                  |
| Programmer                 |         100 |         10 | Vice President Engineering |
| QA Engineer                |         101 |         10 | Vice President Engineering |
| Health Insurance Analyst   |         200 |         20 | Vice President HR          |
+----------------------------+-------------+------------+----------------------------+
Copy

Detalhamento de peças

As hierarquias de gerentes/empregados não são o único tipo de hierarquias de profundidade variável que você pode armazenar em uma única tabela e processar com uma CTE recursiva. Outro exemplo comum de dados hierárquicos é um “detalhamento de peças”, no qual cada componente pode ser listado com seus subcomponentes, cada um dos quais pode ser listado com seus subcomponentes.

Por exemplo, suponha que sua tabela contenha dados hierárquicos, tais como os componentes de um carro. Seu carro provavelmente contém componentes tais como um motor, rodas, etc. Muitos desses componentes contêm subcomponentes (por exemplo, um motor pode conter uma bomba de combustível). A bomba de combustível pode conter um motor, tubulação, etc. Você poderia listar todos os componentes e seus subcomponentes usando um CTE recursiva.

Para um exemplo de uma consulta que produz um detalhamento de peças, consulte WITH.

Solução de problemas de uma CTE recursiva

Uma consulta CTE recursiva é executada até ter sucesso ou atingir o tempo limite

Este problema pode ser causado por dois cenários diferentes:

  • Sua hierarquia de dados pode ter um ciclo.

  • Você pode ter criado um loop infinito.

Causa 1: Hierarquia de dados cíclica

Se sua hierarquia de dados contém um ciclo (ou seja, não é uma árvore verdadeira), há múltiplas soluções possíveis:

Solução 1.1:

Se os dados não devem conter um ciclo, corrija os dados.

Solução 1.2:

Limite a consulta de alguma forma (por exemplo, limitar o número de linhas de saída). Por exemplo:

WITH RECURSIVE t(n) AS
    (
    SELECT 1
    UNION ALL
    SELECT N + 1 FROM t
   )
 SELECT n FROM t LIMIT 10;
Copy
Solução 1.3:

Não use uma consulta que contenha uma CTE recursiva, que espera dados hierárquicos.

Causa 2: Loop infinito

Um loop infinito pode acontecer se a cláusula de projeção na recursive_clause produzir um valor do “pai” (a iteração anterior) ao invés do “filho” (a iteração atual) e então a próxima iteração usar esse valor em uma junção quando deveria usar o valor da iteração atual na junção.

O seguinte pseudocódigo mostra um exemplo aproximado disso:

CREATE TABLE employees (employee_ID INT, manager_ID INT, ...);
INSERT INTO employees (employee_ID, manager_ID) VALUES
        (1, NULL),
        (2, 1);

WITH cte_name (employee_ID, manager_ID, ...) AS
  (
     -- Anchor Clause
     SELECT employee_ID, manager_ID FROM table1
     UNION ALL
     SELECT manager_ID, employee_ID   -- <<< WRONG
         FROM table1 JOIN cte_name
           ON table1.manager_ID = cte_name.employee_ID
  )
SELECT ...
Copy

Neste exemplo, a cláusula recursiva passa seu valor pai (manager_id) na coluna que deve ter o valor atual/filho (employee_id). O pai aparecerá como o valor “atual” na próxima iteração, e será passado novamente como o valor “atual” para a geração seguinte, de modo que a consulta nunca avança através dos níveis; ela continua processando o mesmo nível a cada vez.

Etapa 1:

Suponha que a cláusula de âncora selecione os valores employee_id = 1 e manager_id = NULL.

CTE:

employee_ID  manager_ID
-----------  ---------
      1         NULL
Copy
Etapa 2:

Durante a primeira iteração da cláusula recursiva, employee_id = 2 e manager_id = 1 em table1.

CTE:

employee_ID  manager_ID
-----------  ----------
       1         NULL
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Resultado da junção na cláusula recursiva:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                NULL
 ...
Copy

Projeção:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Entretanto, como as colunas employee_id e manager_id são invertidas na projeção, a saída real da consulta (e portanto o conteúdo da CTE no início da próxima iteração) é:

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy
Etapa 3:

Durante a segunda iteração da cláusula recursiva:

CTE:

employee_ID  manager_ID
-----------  ----------
       1         2
Copy

table1:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Resultado da junção em cláusula recursiva:

table1.employee_ID  table1.manager_ID  cte.employee_ID  cte.manager_ID
-----------------   -----------------  ---------------  --------------
 ...
       2                   1                 1                2
 ...
Copy

Projeção:

employee_ID  manager_ID
-----------  ----------
 ...
       2         1
 ...
Copy

Resultado da consulta (conteúdo da CTE no início da próxima iteração):

employee_ID  manager_ID
-----------  ----------
 ...
       1         2        -- Because manager and employee IDs reversed
 ...
Copy

Como você pode ver, no final da segunda iteração, a linha na CTE é a mesma que era no início da iteração:

  • employee_id é 1.

  • manager_id é 2.

Assim, o resultado da junção durante a próxima iteração será o mesmo que o resultado da junção durante a iteração atual, e a consulta faz um loop infinito.

Se você criou um loop infinito:

Solução 2:

Certifique-se de que a cláusula recursiva passe as variáveis corretas na ordem correta.

Certifique-se também de que a condição JOIN na cláusula recursiva esteja correta. Em um caso típico, o pai da linha “atual” deve ser unido ao valor filho/atual da linha pai.