Cláusula WITH

Una cláusula WITH es una cláusula opcional que precede a la lista SELECT en una consulta. La cláusula WITH define una o más common_table_expressions. Cada expresión común de tabla (CTE) define una tabla temporal, que es similar a la definición de una vista. Puede referenciar estas tablas temporales en la cláusula FROM. Solo se utilizan mientras se ejecuta la consulta a la que pertenecen. Cada CTE de la cláusula WITH especifica un nombre de tabla, una lista opcional de nombres de columnas y una expresión de consulta que toma el valor de una tabla (una instrucción SELECT).

Las subconsultas de la cláusula WITH son una manera eficiente de definir tablas que puede utilizarse al ejecutar una única consulta. En todos los casos, se pueden obtener los mismos resultados al utilizar subconsultas en el cuerpo principal de la instrucción SELECT, pero las subconsultas de la cláusula WITH pueden resultar más sencillas de escribir y leer. Cuando es posible, las subconsultas de la cláusula WITH a las que se hace referencia varias veces se optimizan como subexpresiones comunes; es decir, puede ser posible evaluar una subconsulta WITH una vez y reutilizar sus resultados (tenga en cuenta que las subexpresiones comunes no se limitan a aquellas definidas en la cláusula WITH).

Sintaxis

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

donde common_table_expression puede ser no recursiva. A continuación se presenta la forma no recursiva:

CTE_table_name AS ( query )

Parámetros

common_table_expression

Define una tabla temporal a la que se puede referenciar en Cláusula FROM y se utiliza solo durante la ejecución de la consulta a la que pertenece.

CTE_table_name

Un nombre único para una tabla temporal que define los resultados de una subconsulta de la cláusula WITH. No se pueden usar nombres duplicados dentro de una cláusula WITH. Cada subconsulta debe tener un nombre de tabla al que se pueda hacer referencia en la Cláusula FROM.

consulta

Cualquier consulta SELECT que AWS Clean Rooms sea compatible. Consulte SELECT.

Notas de uso

Puede usar una cláusula WITH en las siguientes instrucciones SQL:

• SELECT, WITH, UNION, INTERSECT y EXCEPT

Si la cláusula FROM de una consulta que contiene una cláusula WITH no referencia ninguna de las tablas definidas por la cláusula WITH, se ignora la cláusula WITH y la consulta se ejecuta como siempre.

Se puede hacer referencia a una tabla definida por una subconsulta de la cláusula WITH solo en el alcance de la consulta SELECT que inicia la cláusula WITH. Por ejemplo, se puede hacer referencia a dicha tabla en la cláusula FROM de una subconsulta en la lista SELECT, la cláusula WHERE o la cláusula HAVING. No se puede usar una cláusula WITH en una subconsulta y hacer referencia a su tabla en la cláusula FROM de una consulta principal o de otra subconsulta. Este patrón de consulta provoca un mensaje de error relation table_name doesn't exist para la tabla de la cláusula WITH.

No se puede especificar otra cláusula WITH dentro de una subconsulta de la cláusula WITH.

No se pueden realizar referencias futuras a tablas definidas por las subconsultas de la cláusula WITH. Por ejemplo, la siguiente consulta devuelve un error debido a la referencia futura a la tabla W2 en la definición de la tabla W1:

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

Ejemplos

En el siguiente ejemplo, se muestra el caso posible más simple de una consulta que contiene una cláusula WITH. La consulta WITH denominada VENUECOPY selecciona todas las filas de la tabla VENUE. La consulta principal, a su vez, selecciona todas las filas de VENUECOPY. La tabla VENUECOPY existe solo durante esta 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)

En el siguiente ejemplo, se muestra una cláusula WITH que produce dos tablas, denominadas VENUE_SALES y TOP_VENUES. La segunda tabla de la consulta WITH selecciona desde la primera. A su vez, la cláusula WHERE del bloque de la consulta principal contiene una subconsulta que limita la tabla 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)

En los siguientes dos ejemplos se muestran las reglas para el alcance de las referencias de la tabla en función de las subconsultas de la cláusula WITH. La primera consulta se ejecuta, pero en la segunda se produce un error inesperado. La primera consulta tiene una subconsulta de la cláusula WITH dentro de la lista SELECT de la consulta principal. Se hace referencia a la tabla definida por la cláusula WITH (HOLIDAYS) en la cláusula FROM de la subconsulta de la 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)

La segunda consulta falla porque intenta hacer referencia a la tabla HOLIDAYS en la consulta principal, así como en la subconsulta de la lista SELECT. Las referencias de la consulta principal están fuera de alcance.

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