WITH¶

Uso geral¶

• Uma cláusula WITH pode se referir recursivamente a si mesma, e a outros CTEs que aparecem anteriormente na mesma cláusula. Por exemplo, cte_name2 pode se referir a cte_name1 e a si mesmo, enquanto cte_name1 pode se referir a si mesmo, mas não a cte_name2.

• Você pode misturar cláusulas recursivas e não recursivas (iterativas e não iterativas) CTE na cláusula WITH. As cláusulas CTE devem ser ordenadas de forma que, se um CTE precisar fazer referência a outro CTE, o CTE a ser referenciado deve ser definido mais cedo na instrução (por exemplo, o segundo CTE pode fazer referência ao primeiro CTE, mas não o contrário).

  Os CTEs não precisam ser listados em ordem com base no fato de serem recursivos ou não. Por exemplo, um CTE não recursivo pode ser listado imediatamente após a palavra-chave RECURSIVE, e um CTE recursivo pode vir após esse CTE não recursivo.

  Dentro de um CTE recursivo, ou a cláusula de âncora ou a cláusula recursiva (ou ambas) podem se referir a outro(s) CTE(s).

• Para CTEs recursivos, o cte_column_list é obrigatório.

• Para CTEs não recursivos, o cte_column_list é opcional.

• Certifique-se de usar UNION ALL, não UNION, em um CTE recursivo.

• A palavra-chave RECURSIVE é opcional.

  • CTEs pode ser recursivo, com RECURSIVE especificado ou não.

  • Você pode usar a palavra-chave RECURSIVE mesmo que nenhum CTEs seja recursivo.

  • Se RECURSIVE for usado, deve ser usado apenas uma vez, mesmo que mais de um CTE seja recursivo.

  Embora as instruções SQL funcionem corretamente com ou sem a palavra-chave RECURSIVE, o uso correto da palavra-chave torna o código mais fácil de entender e manter. O Snowflake recomenda o uso da palavra-chave RECURSIVE se um ou mais CTEs forem recursivos, e o Snowflake recomenda fortemente a omissão da palavra-chave se nenhum dos CTEs for recursivo.

WITH cte AS (
  SELECT ..., 1 as level ...

  UNION ALL

  SELECT ..., cte.level + 1 as level
   FROM cte ...
   WHERE ... level < 10
) ...

Limitações¶

• A implementação do Snowflake de CTEs recursivos não oferece suporte às seguintes palavras-chave que alguns outros sistemas suportam:

  • SEARCH DEPTH FIRST BY ...

  • CYCLE ... SET ...

Cláusula de âncora¶

A cláusula de âncora em um CTE recursivo é uma instrução SELECT.

A cláusula de âncora é executada uma vey durante a execução da instrução na qual está inserida; ela é executada antes da cláusula recursiva e gera o primeiro conjunto de linhas a partir do CTErecursivo. Estas linhas não só estão incluídas na saída da consulta, mas também referenciadas pela cláusula recursiva.

A cláusula de âncora pode conter qualquer construção SQL permitida em uma cláusula SELECT. No entanto, a cláusula de âncora não pode fazer referência a cte_name1; somente a cláusula recursiva pode fazer referência a cte_name1.

Embora a cláusula de âncora geralmente faça a seleção da mesma tabela que a cláusula recursiva, isto não é necessário. A cláusula de âncora pode fazer a seleção de qualquer fonte de dados do tipo tabela, incluindo outra tabela, uma exibição, um UDTF ou um valor constante.

A cláusula de âncora seleciona um único “nível” da hierarquia, tipicamente o nível superior, ou o nível mais alto de interesse. Por exemplo, se a consulta se destina a mostrar a “explosão de peças” de um carro, a cláusula de âncora retorna o componente de nível mais alto, que é o próprio carro.

A saída da cláusula de âncora representa uma camada da hierarquia, e esta camada é armazenada como o conteúdo da “exibição” que é acessada na primeira iteração da cláusula recursiva.

Cláusula recursiva¶

A cláusula recursiva é uma instrução SELECT. Este SELECT é restrito a projeções, filtros e junções (junções internas e externas nas quais a referência recursiva está no lado preservado da junção externa). A cláusula recursiva não pode conter:

• Funções agregadas ou de janela,

• GROUP BY, ORDER BY, LIMIT ou DISTINCT.

A cláusula recursiva pode (e geralmente faz) referência ao cte_name1 como se o CTE fosse uma tabela ou exibição.

A cláusula recursiva geralmente inclui um JOIN que une a tabela que foi usada na cláusula de âncora ao CTE. Entretanto, o JOIN pode juntar-se a mais de uma tabela ou fonte de dados em forma de tabela (exibição etc.).

A primeira iteração da cláusula recursiva começa com os dados da cláusula de ancoragem. Esses dados são então unidos à(s) outra(s) tabela(s) na cláusula FROM da cláusula recursiva.

Cada iteração subsequente começa com os dados da iteração anterior.

Você pode pensar na cláusula CTE ou “exibição” como se estivesse mantendo o conteúdo da iteração anterior, de modo que esse conteúdo esteja disponível para ser unido. Observe que durante qualquer iteração, o CTE contém apenas o conteúdo da iteração anterior, não os resultados acumulados de todas as iterações anteriores. Os resultados acumulados (inclusive a partir da cláusula de ancoragem) são armazenados em um local separado.

Listas de colunas em um CTE recursivo¶

Há três listas de colunas em um CTE recursivo:

• cte_column_list

• anchor_column_list (na cláusula de ancoragem)

• recursive_column_list (na cláusula recursiva)

Um CTE recursivo pode conter outras listas de colunas (por exemplo, em uma subconsulta), mas estas três listas de colunas devem obrigatoriamente estar presentes.

Estas três listas de colunas devem corresponder umas às outras.

Em pseudocódigo, isto é semelhante a:

WITH RECURSIVE cte_name (X, Y) AS
(
  SELECT related_to_X, related_to_Y FROM table1
  UNION ALL
  SELECT also_related_to_X, also_related_to_Y
    FROM table1 JOIN cte_name ON <join_condition>
)
SELECT ... FROM ...

As colunas X e related_to_X devem ser correspondentes; a cláusula de âncora gera o “conteúdo” inicial da “exibição” que o CTE representa, portanto cada coluna da cláusula de âncora (por exemplo, coluna related_to_x) deve gerar a saída que pertencerá à coluna correspondente do CTE (por exemplo, coluna X).

As colunas also_related_to_X e X devem ser correspondentes; em cada iteração da cláusula recursiva, a saída dessa cláusula torna-se o novo conteúdo do CTE/exibição para a próxima iteração.

Além disso, as colunas related_to_X e also_related_to_X devem corresponder porque estão cada uma de um lado do operador UNION ALL, e as colunas de cada lado de um operador UNION ALL devem corresponder.

Exemplos não recursivos¶

Esta seção fornece exemplos de consultas e saída. Para manter os exemplos curtos, o código omite as instruções para criar e carregar as tabelas.

Este primeiro exemplo usa uma simples cláusula WITH para extrair um subconjunto de dados, neste caso os álbuns de música que foram lançados em 1976. Para este pequeno banco de dados, a saída da consulta são os álbuns “Amigos” e “Look Into The Future”, ambos do ano 1976:

with
  albums_1976 as (select * from music_albums where album_year = 1976)
select album_name from albums_1976 order by album_name;
+----------------------+
| ALBUM_NAME           |
|----------------------|
| Amigos               |
| Look Into The Future |
+----------------------+

Copy

Este próximo exemplo usa uma cláusula WITH com uma cláusula WITH anterior; o CTE chamado journey_album_info_1976 usa o CTE chamado album_info_1976. A saída é o álbum “Look Into The Future”, com o nome da banda:

with
   album_info_1976 as (select m.album_ID, m.album_name, b.band_name
      from music_albums as m inner join music_bands as b
      where m.band_id = b.band_id and album_year = 1976),
   Journey_album_info_1976 as (select *
      from album_info_1976 
      where band_name = 'Journey')
select album_name, band_name 
   from Journey_album_info_1976;
+----------------------+-----------+
| ALBUM_NAME           | BAND_NAME |
|----------------------+-----------|
| Look Into The Future | Journey   |
+----------------------+-----------+

Copy

Este exemplo lista os músicos que tocaram nos álbuns do Santana e do Journey. Este exemplo não utiliza a cláusula WITH. Para esta consulta (e as próximas consultas, todas sendo as formas equivalentes de executar a mesma consulta), a saída são IDs e os nomes dos músicos que tocaram nos álbuns do Santana e nos álbuns do Journey:

select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Santana'
intersect
select distinct musicians.musician_id, musician_name
 from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
 where musicians.musician_ID = musicians_and_albums.musician_ID
   and musicians_and_albums.album_ID = music_albums.album_ID
   and music_albums.band_ID = music_bands.band_ID
   and music_bands.band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Como você pode ver, a consulta anterior contém um código duplicado. Os próximos exemplos mostram como simplificar esta consulta usando uma ou mais exibições explícitas, e depois como simplificá-la usando CTEs.

Esta consulta mostra como usar as exibições para reduzir a duplicação e complexidade do exemplo anterior (como no exemplo anterior, isto não usa uma cláusula WITH):

create or replace view view_musicians_in_bands AS
  select distinct musicians.musician_id, musician_name, band_name
    from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
    where musicians.musician_ID = musicians_and_albums.musician_ID
      and musicians_and_albums.album_ID = music_albums.album_ID
      and music_albums.band_ID = music_bands.band_ID;

Copy

Com esta exibição, você pode reescrever a consulta original como:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Este exemplo usa uma cláusula WITH para fazer o equivalente ao que a consulta anterior fez:

with
  musicians_in_bands as (
     select distinct musicians.musician_id, musician_name, band_name
      from musicians inner join musicians_and_albums inner join music_albums inner join music_bands
      where musicians.musician_ID = musicians_and_albums.musician_ID
        and musicians_and_albums.album_ID = music_albums.album_ID
        and music_albums.band_ID = music_bands.band_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Estas instruções criam exibições mais granulares (este exemplo não usa uma cláusula WITH):

Listar os álbuns por uma determinada banda:

create or replace view view_album_IDs_by_bands AS
 select album_ID, music_bands.band_id, band_name
  from music_albums inner join music_bands
  where music_albums.band_id = music_bands.band_ID;

Copy

Listar os músicos que tocaram nos álbuns:

create or replace view view_musicians_in_bands AS
 select distinct musicians.musician_id, musician_name, band_name
  from musicians inner join musicians_and_albums inner join view_album_IDs_by_bands
  where musicians.musician_ID = musicians_and_albums.musician_ID
    and musicians_and_albums.album_ID = view_album_IDS_by_bands.album_ID;

Copy

Agora use essas exibições para consultar os músicos que tocaram em ambos os álbuns do Santana e do Journey:

select musician_id, musician_name from view_musicians_in_bands where band_name = 'Santana'
intersect
select musician_id, musician_name from view_musicians_in_bands where band_name = 'Journey'
order by musician_ID;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Estas instruções criam exibições implícitas e mais granulares (este exemplo usa uma cláusula WITH):

with
  album_IDs_by_bands as (select album_ID, music_bands.band_id, band_name
                          from music_albums inner join music_bands
                          where music_albums.band_id = music_bands.band_ID),
  musicians_in_bands as (select distinct musicians.musician_id, musician_name, band_name
                          from musicians inner join musicians_and_albums inner join album_IDs_by_bands
                          where musicians.musician_ID = musicians_and_albums.musician_ID
                            and musicians_and_albums.album_ID = album_IDS_by_bands.album_ID)
select musician_ID, musician_name from musicians_in_bands where band_name = 'Santana'
intersect
select musician_ID, musician_name from musicians_in_bands where band_name = 'Journey'
order by musician_ID
  ;
+-------------+---------------+
| MUSICIAN_ID | MUSICIAN_NAME |
|-------------+---------------|
|         305 | Gregg Rolie   |
|         306 | Neal Schon    |
+-------------+---------------+

Copy

Exemplos recursivos¶

Este é um exemplo básico da utilização de um CTE recursivo para gerar uma sequência de Fibonacci:

WITH RECURSIVE current_f (current_val, previous_val) AS
    (
    SELECT 0, 1
    UNION ALL 
    SELECT current_val + previous_val, current_val FROM current_f
      WHERE current_val + previous_val < 100
    )
  SELECT current_val FROM current_f ORDER BY current_val;
+-------------+
| CURRENT_VAL |
|-------------|
|           0 |
|           1 |
|           1 |
|           2 |
|           3 |
|           5 |
|           8 |
|          13 |
|          21 |
|          34 |
|          55 |
|          89 |
+-------------+

Copy

Este exemplo é uma consulta com um CTE recursivo que mostra uma “explosão de peças” para um automóvel:

-- The components of a car.
CREATE TABLE components (
    description VARCHAR,
    component_ID INTEGER,
    quantity INTEGER,
    parent_component_ID INTEGER
    );

INSERT INTO components (description, quantity, component_ID, parent_component_ID) VALUES
    ('car', 1, 1, 0),
       ('wheel', 4, 11, 1),
          ('tire', 1, 111, 11),
          ('#112 bolt', 5, 112, 11),
          ('brake', 1, 113, 11),
             ('brake pad', 1, 1131, 113),
       ('engine', 1, 12, 1),
          ('piston', 4, 121, 12),
          ('cylinder block', 1, 122, 12),
          ('#112 bolt', 16, 112, 12)   -- Can use same type of bolt in multiple places
    ;

Copy

WITH RECURSIVE current_layer (indent, layer_ID, parent_component_ID, component_id, description, sort_key) AS (
  SELECT 
      '...', 
      1, 
      parent_component_ID, 
      component_id, 
      description, 
      '0001'
    FROM components WHERE component_id = 1
  UNION ALL
  SELECT indent || '...',
      layer_ID + 1,
      components.parent_component_ID,
      components.component_id, 
      components.description,
      sort_key || SUBSTRING('000' || components.component_ID, -4)
    FROM current_layer JOIN components 
      ON (components.parent_component_id = current_layer.component_id)
  )
SELECT
  -- The indentation gives us a sort of "side-ways tree" view, with
  -- sub-components indented under their respective components.
  indent || description AS description, 
  component_id,
  parent_component_ID
  -- The layer_ID and sort_key are useful for debugging, but not
  -- needed in the report.
--  , layer_ID, sort_key
  FROM current_layer
  ORDER BY sort_key;
+-------------------------+--------------+---------------------+
| DESCRIPTION             | COMPONENT_ID | PARENT_COMPONENT_ID |
|-------------------------+--------------+---------------------|
| ...car                  |            1 |                   0 |
| ......wheel             |           11 |                   1 |
| .........tire           |          111 |                  11 |
| .........#112 bolt      |          112 |                  11 |
| .........brake          |          113 |                  11 |
| ............brake pad   |         1131 |                 113 |
| ......engine            |           12 |                   1 |
| .........#112 bolt      |          112 |                  12 |
| .........piston         |          121 |                  12 |
| .........cylinder block |          122 |                  12 |
+-------------------------+--------------+---------------------+

Copy

Para obter mais exemplos, consulte Como trabalhar com CTEs (expressões de tabela comuns).