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 en 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). Cuando se referencia el nombre de la tabla temporal en la cláusula FROM de la misma expresión de consulta que lo define, la CTE es recursiva.
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 [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
Donde common_table_expression puede ser recursiva o no recursiva. A continuación, se presenta la forma no recursiva:
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )
A continuación, se presenta la forma recursiva de common_table_expression:
CTE_table_name (column_name [, ...] ) AS ( recursive_query )
Parámetros
- RECURSIVE
-
Se trata de la palabra clave que identifica la consulta como una CTE recursiva. Esta palabra clave es necesaria si alguna common_table_expression definida en la cláusula WITH es recursiva. Solo puede especificar la palabra clave RECURSIVE una vez, inmediatamente después de la palabra clave WITH, incluso si la cláusula WITH contiene varias CTE recursivas. En general, una CTE recursiva es una subconsulta UNION ALL con dos partes.
- 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.
- column_name
-
Se trata de una lista de nombres de columnas de salida para la subconsulta de la cláusula WITH, separados por comas. La cantidad de nombres de columnas especificada debe ser igual o menor a la cantidad de columnas definidas por la subconsulta. Para una CTE que no es recursiva, column_name es opcional. Para una CTE recursiva, la lista de column_name es obligatoria.
- consulta
-
Se trata de cualquier consulta SELECT compatible con Amazon Redshift. Consulte SELECT.
- recursive_query
-
Una consulta UNION ALL que consta de dos subconsultas SELECT:
La primera subconsulta SELECT no tiene una referencia recursiva al mismo CTE_table_name. Devuelve un conjunto de resultados que es la inicialización de la recursión. Esta parte se denomina miembro inicial o miembro de inicialización.
La segunda subconsulta SELECT referencia el mismo CTE_table_name en su cláusula FROM. Esto se conoce como el miembro recursivo. La recursive_query contiene una condición WHERE para finalizar la recursive_query.
Notas de uso
Puede usar una cláusula WITH en las siguientes instrucciones SQL:
-
SELECT
-
SELECT INTO
-
CREATE TABLE AS
-
CREATE VIEW
-
DECLARE
-
EXPLAIN
-
INSERT INTO...SELECT
-
PREPARE
-
UPDATE (dentro de una subconsulta de la cláusula WHERE. No puede definir una CTE recursiva en la subconsulta. La CTE recursiva debe ubicarse antes de la cláusula UPDATE).
-
DELETE
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
Una subconsulta de la cláusula WITH no puede constar de una instrucción SELECT INTO. No obstante, puede usar una cláusula WITH en una instrucción SELECT INTO.
Expresiones de tabla comunes recursivas
Una expresión común de tabla (CTE) recursiva es una CTE que se referencia a sí misma. Una CTE recursiva es útil para consultar datos jerárquicos, como gráficos de la organización que muestran las relaciones de informe entre los empleados y los administradores. Consulte Ejemplo: CTE recursiva.
Otro uso común es una factura de materiales multinivel, en la que un producto consta de muchos componentes, y cada componente, a su vez, consta de otros componentes o subconjuntos.
Asegúrese de limitar la profundidad de la recursividad al incluir una cláusula WHERE en la segunda subconsulta SELECT de la consulta recursiva. Para ver un ejemplo, consulte Ejemplo: CTE recursiva. De lo contrario, puede producirse un error similar a los siguientes:
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 es un parámetro que establece el número máximo de filas que puede devolver una CTE recursiva para evitar bucles de recursión infinitos. No recomendamos cambiarlo por un valor superior al predeterminado. De este modo, se evita que los problemas de recursión infinita en las consultas ocupen un espacio excesivo en el clúster.
Puede especificar un criterio de ordenación y un límite en el resultado de la CTE recursiva. Puede incluir las opciones GROUP BY y DISTINCT en el resultado final de la CTE recursiva.
No se puede especificar una cláusula WITH RECURSIVE dentro de una subconsulta. El miembro de recursive_query no puede incluir una cláusula de ordenación o de límite.
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
Ejemplo: CTE recursiva
A continuación se muestra un ejemplo de CTE recursivo que devuelve los empleados que dependen directa o indirectamente de John. La consulta recursiva contiene una cláusula WHERE para limitar la profundidad de la recursividad a menos de 4 niveles.
--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 continuación, se muestra el resultado de la 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 continuación, se muestra un gráfico de la organización del departamento de John.
