Cláusula WITH

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).

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  common_table_expression [, common_table_expression , ...] ]

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

CTE_table_name AS ( query )

Parâmetros

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.

query

Qualquer consulta SELECT que AWS Clean Rooms ofereça suporte. Consulte SELECT.

Observações de uso

Você pode usar uma cláusula WITH na seguinte instrução SQL:

• SELECT, WITH, UNION, INTERSECT e EXCEPT

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

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