Cláusula WITH

PDFRSS

Uma cláusula WITH é uma cláusula opcional que precede a lista SELECT em uma consulta. A cláusula WITH define um ou mais common_table_expressions. Cada expressão de tabela comum (CTE) define uma tabela temporária, que é semelhante à definição de visualização. Você pode fazer referência a essas tabelas temporárias na cláusula FROM. Eles são usados apenas enquanto a consulta a que pertencem é executada. Cada CTE na cláusula WITH especifica um nome de tabela, uma lista opcional de nomes de coluna e uma expressão de consulta que é avaliada como uma tabela (uma instrução SELECT). Quando você faz referência ao nome da tabela temporária na cláusula FROM da mesma expressão de consulta que a define, o CTE é recursivo.

Subconsultas da cláusula WITH são uma forma eficiente de definir tabelas que podem ser usadas ao longo da execução de uma consulta. Em todos os casos, os mesmos resultados podem ser obtidos usando subconsultas no corpo principal da instrução SELECT, mas pode ser mais simples fazer leituras ou gravações de subconsultas da cláusula WITH. Sempre que possível, subconsultas da cláusula WITH por várias vezes referidas são aperfeiçoadas como subexpressões comuns, ou seja, é possível avaliar uma subconsulta WITH uma vez e reutilizar seus resultados. (Observe que subexpressões comuns não estão limitadas àquelas definidas na cláusula WITH.)

Sintaxe

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

Onde common_table_expression pode ser não recursivo ou recursivo. Segue-se a forma não recursiva:

CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

Segue-se a forma recursiva de common_table_expression:

CTE_table_name (column_name [, ...] ) AS ( recursive_query )

Parâmetros

RECURSIVE

Palavra-chave que identifica a consulta como um CTE recursivo. Esta palavra-chave é necessária se qualquer common_table_expression definido na cláusula WITH for recursivo. Você só pode especificar a palavra-chave RECURSIVE uma vez, imediatamente após a palavra-chave WITH, mesmo quando a cláusula WITH contém várias CTEs recursivas. Em geral, um CTE recursivo é uma subconsulta UNION ALL com duas partes.

common_table_expression

Define uma tabela temporária que você pode fazer referência no Cláusula FROM e é usado somente durante a execução da consulta a qual pertence.

CTE_table_name

Um nome exclusivo para uma tabela temporária que define os resultados da subconsulta de cláusula WITH. Você não pode usar nomes duplicados em uma única cláusula WITH. Cada subconsulta deve ter um nome de tabela que pode mencionado em Cláusula FROM.

column_name

Uma lista de nomes de colunas de saída para a subconsulta da cláusula WITH, separados por vírgulas. O número de nomes de coluna especificados deve ser igual ou menor que o número de colunas definido pela subconsulta. Para um CTE que não é recursivo, a cláusula column_name é opcional. Para um CTE recursivo, a lista column_name é necessária.

query

Qualquer consulta SELECT compatível com o Amazon Redshift. Consulte SELECT.

recursive_query

Uma consulta UNION ALL que consiste em duas subconsultas SELECT:

• A primeira subconsulta SELECT não tem uma referência recursiva para o mesmo CTE_table_name. Ele retorna um conjunto de resultados que é a semente inicial da recursão. Esta parte é chamada de membro inicial ou membro semente.

• A segunda subconsulta SELECT faz referência ao mesmo CTE_table_name em sua cláusula FROM. Isso é chamado de membro recursivo. A recursive_query contém uma condição WHERE para finalizar a recursive_query.

Observações de uso

Você pode usar a cláusula WITH nas seguintes instruções SQL:

• SELECT

• SELECT INTO

• CREATE TABLE AS

• CREATE VIEW

• DECLARE

• EXPLAIN

• INSERT INTO...SELECT

• PREPARE

• UPDATE (em uma subconsulta cláusula WHERE não é possível definir um CTE recursivo na subconsulta. O CTE recursivo deve preceder a cláusula UPDATE.)

• DELETE

Se a cláusula FROM de uma consulta que contém a cláusula WITH não fizer referência a qualquer das tabelas definidas pela cláusula WITH, a cláusula WITH será ignorada e a consulta será executada como normal.

Uma tabela definida por uma subconsulta de cláusula WITH somente pode ser referida no escopo da consulta SELECT iniciada pela cláusula WITH. Por exemplo, você pode fazer referência a essa tabela na cláusula FROM da subconsulta na lista SELECT, na cláusula WHERE ou na cláusula HAVING. Você não pode usar a cláusula WITH em uma subconsulta e fazer referência à sua tabela na cláusula FROM da consulta principal ou de outra subconsulta. Este padrão de consulta resulta em uma mensagem de erro do formulário relation table_name doesn't exist para a tabela da cláusula WITH.

Você não pode especificar outra cláusula WITH em uma subconsulta de cláusula WITH.

Você não pode fazer referência antecipada a tabelas definidas por subconsultas da cláusula WITH. Por exemplo, a consulta a seguir retorna um erro devido à referência antecipada para a tabela W2 na definição da tabela W1:

with w1 as (select * from w2), w2 as (select * from w1)
select * from sales;
ERROR:  relation "w2" does not exist

A subconsulta de cláusula WITH pode não consistir em uma instrução SELECT INTO. No entanto, você pode usar uma cláusula WITH em uma instrução SELECT INTO.

Expressões de tabela comuns recursivas

Uma expressão de tabela comum (CTE) recursiva é um CTE que faz referência a si próprio. Um CTE recursivo é útil na consulta de dados hierárquicos, como organogramas que mostram relações de relatório entre funcionários e gerentes. Consulte Exemplo: CTE recursivo.

Outro uso comum é uma lista de materiais multinível, quando um produto consiste em muitos componentes e cada componente também consiste em outros componentes ou submontagens.

Certifique-se de limitar a profundidade da recursão incluindo uma cláusula WHERE na segunda subconsulta SELECT da consulta recursiva. Para ver um exemplo, consulte Exemplo: CTE recursivo. Caso contrário, um erro pode ocorrer semelhante ao seguinte:

• Recursive CTE out of working buffers.

• Exceeded recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.

nota

max_recursion_rows é um parâmetro que define o número máximo de linhas que um CTE recursivo pode retornar para evitar loops de recursão infinita. Não recomendamos alterar esse parâmetro para um valor maior do que o padrão. Isso impede que problemas de recursão infinita em suas consultas ocupem espaço excessivo em seu cluster.

Você pode especificar uma ordem de classificação e limitar o resultado do CTE recursivo. Você pode incluir opções de grupo por e distintas no resultado final do CTE recursivo.

Você não pode especificar outra cláusula WITH em uma subconsulta de cláusula WITH. A recursive_query não pode incluir uma cláusula de ordem por ou limite.

Exemplos

O exemplo a seguir mostra o caso mais simples possível de uma consulta que contém uma cláusula WITH. A consulta WITH com o nome VENUECOPY seleciona todas as linhas da tabela VENUE. Por sua vez, a consulta principal seleciona todas as linhas de VENUECOPY. A tabela VENUECOPY existe somente durante a consulta.

with venuecopy as (select * from venue)
select * from venuecopy order by 1 limit 10;

 venueid |         venuename          |    venuecity    | venuestate | venueseats
---------+----------------------------+-----------------+------------+------------
1 | Toyota Park                | Bridgeview      | IL         |          0
2 | Columbus Crew Stadium      | Columbus        | OH         |          0
3 | RFK Stadium                | Washington      | DC         |          0
4 | CommunityAmerica Ballpark  | Kansas City     | KS         |          0
5 | Gillette Stadium           | Foxborough      | MA         |      68756
6 | New York Giants Stadium    | East Rutherford | NJ         |      80242
7 | BMO Field                  | Toronto         | ON         |          0
8 | The Home Depot Center      | Carson          | CA         |          0
9 | Dick's Sporting Goods Park | Commerce City   | CO         |          0
v     10 | Pizza Hut Park             | Frisco          | TX         |          0
(10 rows)

O exemplo a seguir mostra uma cláusula WITH que produz duas tabelas, chamadas VENUE_SALES e TOP_VENUES. A segunda tabela de consulta WITH seleciona a partir da primeira. Por sua vez, a cláusula WHERE do bloco principal de consulta contém um subconsulta que restringe a tabela TOP_VENUES.

with venue_sales as
(select venuename, venuecity, sum(pricepaid) as venuename_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
group by venuename, venuecity),

top_venues as
(select venuename
from venue_sales
where venuename_sales > 800000)

select venuename, venuecity, venuestate,
sum(qtysold) as venue_qty,
sum(pricepaid) as venue_sales
from sales, venue, event
where venue.venueid=event.venueid and event.eventid=sales.eventid
and venuename in(select venuename from top_venues)
group by venuename, venuecity, venuestate
order by venuename;

        venuename       |   venuecity   | venuestate | venue_qty | venue_sales
------------------------+---------------+------------+-----------+-------------
August Wilson Theatre   | New York City | NY         |      3187 |  1032156.00
Biltmore Theatre        | New York City | NY         |      2629 |   828981.00
Charles Playhouse       | Boston        | MA         |      2502 |   857031.00
Ethel Barrymore Theatre | New York City | NY         |      2828 |   891172.00
Eugene O'Neill Theatre  | New York City | NY         |      2488 |   828950.00
Greek Theatre           | Los Angeles   | CA         |      2445 |   838918.00
Helen Hayes Theatre     | New York City | NY         |      2948 |   978765.00
Hilton Theatre          | New York City | NY         |      2999 |   885686.00
Imperial Theatre        | New York City | NY         |      2702 |   877993.00
Lunt-Fontanne Theatre   | New York City | NY         |      3326 |  1115182.00
Majestic Theatre        | New York City | NY         |      2549 |   894275.00
Nederlander Theatre     | New York City | NY         |      2934 |   936312.00
Pasadena Playhouse      | Pasadena      | CA         |      2739 |   820435.00
Winter Garden Theatre   | New York City | NY         |      2838 |   939257.00
(14 rows)

Os dois exemplos a seguir demonstram as regras para o escopo de referências de tabela com base subconsultas da cláusula WITH. A primeira consulta é executada, mas a segunda falha com um erro esperado. A primeira consulta tem a subconsulta de cláusula WITH na lista SELECT da consulta principal. A tabela definida pela cláusula WITH (HOLIDAYS) é referida na cláusula FROM da subconsulta na lista SELECT:

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join date on sales.dateid=date.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

caldate   | daysales | dec25sales
-----------+----------+------------
2008-12-25 | 70402.00 |   70402.00
2008-12-31 | 12678.00 |   70402.00
(2 rows)

A segunda consulta falha porque tenta fazer referência à tabela HOLIDAYS na consulta principal, assim como na subconsulta da lista SELECT. As referências principais da consulta estão fora do escopo.

select caldate, sum(pricepaid) as daysales,
(with holidays as (select * from date where holiday ='t')
select sum(pricepaid)
from sales join holidays on sales.dateid=holidays.dateid
where caldate='2008-12-25') as dec25sales
from sales join holidays on sales.dateid=holidays.dateid
where caldate in('2008-12-25','2008-12-31')
group by caldate
order by caldate;

ERROR:  relation "holidays" does not exist

Exemplo: CTE recursivo

Veja a seguir um exemplo de um CTE recursivo que retorna os funcionários que respondem direta ou indiretamente a John. A consulta recursiva contém uma cláusula WHERE para limitar a profundidade da recursão a menos de 4 níveis.

--create and populate the sample table
  create table employee (
  id int,
  name varchar (20),
  manager_id int
  );
  
  insert into employee(id, name, manager_id)  values
(100, 'Carlos', null),
(101, 'John', 100),
(102, 'Jorge', 101),
(103, 'Kwaku', 101),
(110, 'Liu', 101),
(106, 'Mateo', 102),
(110, 'Nikki', 103),
(104, 'Paulo', 103),
(105, 'Richard', 103),
(120, 'Saanvi', 104),
(200, 'Shirley', 104),
(201, 'Sofía', 102),
(205, 'Zhang', 104);
  
--run the recursive query
  with recursive john_org(id, name, manager_id, level) as
( select id, name, manager_id, 1 as level
  from employee
  where name = 'John'
  union all
  select e.id, e.name, e.manager_id, level + 1 as next_level
  from employee e, john_org j
  where e.manager_id = j.id and level < 4
  )
 select distinct id, name, manager_id from john_org order by manager_id;

A seguir é o resultado da consulta.

    id        name      manager_id
  ------+-----------+--------------
   101    John           100
   102    Jorge          101
   103    Kwaku          101
   110    Liu            101
   201    Sofía          102
   106    Mateo          102
   110    Nikki          103
   104    Paulo          103
   105    Richard        103
   120    Saanvi         104
   200    Shirley        104
   205    Zhang          104

A seguir está um organograma para o departamento de John.