Syntaxe Paramètres Notes d’utilisation CTE récursives Exemples Exemple : CTE récursive

Clause WITH

Une clause WITH est une clause facultative qui précède la liste SELECT d’une requête. La clause WITH définit une ou plusieurs expressions common_table_expressions. Chaque expression de table commune (CTE) définit une table temporaire, qui est similaire à la définition d’une vue. Vous pouvez référencer ces tables temporaires dans la clause FROM. Elles ne sont utilisées que pendant l’exécution de la requête à laquelle elles appartiennent. Chaque CTE de la clause WITH spécifie un nom de table, une liste facultative de noms de colonne et une expression de requête correspondant à une table (instruction SELECT). Lorsque vous référencez le nom de la table temporaire dans la clause FROM de la même expression de requête qui la définit, la CTE est récursive.

Les sous-requêtes de clause WITH sont un moyen efficace de définir les tables qui peuvent être utilisées tout au long de l’exécution d’une même requête. Dans tous les cas, les mêmes résultats peuvent être obtenus à l’aide de sous-requêtes dans le corps principal de l’instruction SELECT, mais les sous-requêtes de clause WITH peuvent être plus simples à lire et à écrire. Chaque fois que possible, les sous-requêtes de clause WITH qui sont référencées plusieurs fois sont optimisées en tant que sous-expressions courantes ; autrement dit, il peut être possible d’évaluer une sous-requête WITH une fois et de réutiliser ses résultats. (Notez que les sous-expressions courantes ne sont pas limitées à celles définies dans la clause WITH).

Syntaxe


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

où common_table_expression peut être récursive ou non-récursive. Voici la forme non-récursive :


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

Voici la forme récursive de common_table_expression :


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

Paramètres

RECURSIVE

Mot-clé qui identifie la requête comme étant une CTE récursive. Ce mot-clé est requis si l’expression common_table_expression définie dans la clause WITH est récursive. Vous ne pouvez spécifier le mot-clé RECURSIVE qu’une seule fois, immédiatement après le mot-clé WITH, même lorsque la clause WITH contient plusieurs CTE récursives. En général, une CTE récursive est une sous-requête UNION ALL avec deux parties.

common_table_expression

Définit une table temporaire que vous pouvez référencer dans Clause FROM et qui n’est utilisée que pendant l’exécution de la requête à laquelle elle appartient.

CTE_table_name

Nom unique d’une table temporaire qui définit les résultats d’une sous-requête de clause WITH. Vous ne pouvez pas utiliser de noms en double au sein d’une clause WITH. Chaque sous-requête doit avoir un nom de table qui peut être référencé dans la Clause FROM.

column_name

Liste des noms de colonne de sortie pour la sous-requête de clause WITH, séparés par des virgules. Le nombre de noms de colonne spécifié doit être égal ou inférieur au nombre de colonnes défini par la sous-requête. Pour une CTE non récursive, column_name est facultatif. Pour une CTE récursive, column_name est obligatoire.

query

Toute requête SELECT qu’Amazon Redshift prend en charge. Consultez SELECT.

recursive_query

Requête UNION ALL qui se compose de deux sous-requêtes SELECT :

La première sous-requête SELECT n’a pas de référence récursive à la même table CTE_table_name. Elle renvoie un ensemble de résultats qui est la base initiale de la récursivité. Cette partie est appelée initial member ou seed member.
La deuxième sous-requête SELECT fait référence à la même table CTE_table_name dans sa clause FROM. C’est ce qu’on appelle le recursive member. recursive_query contient une condition WHERE pour mettre fin à la recursive_query.

Notes d’utilisation

Vous pouvez utiliser une clause WITH dans les instructions SQL suivantes :

SELECT
SELECT INTO
CREATE TABLE AS
CREATE VIEW
DECLARE
EXPLAIN
INSERT INTO...SELECT
PREPARE
UPDATE (dans une sous-requête de clause WHERE. Vous ne pouvez pas définir une CTE récursive dans la sous-requête. La CTE récursive doit précéder la clause UPDATE.)
DELETE

Si la clause FROM d’une requête qui contient une clause WITH ne fait pas référence à l’une des tables définies par la clause WITH, la clause WITH est ignorée et la requête s’exécute normalement.

Une table définie par une sous-requête de clause WITH peut être référencée uniquement dans la portée de la requête SELECT que commence la clause WITH. Par exemple, vous pouvez faire référence à une telle table dans la clause FROM d’une sous-requête de la liste SELECT, la clause WHERE ou la clause HAVING. Vous ne pouvez pas utiliser une clause WITH dans une sous-requête et faire référence à sa table dans la clause FROM de la requête principale ou d’une autre sous-requête. Ce modèle de requête entraîne un message d’erreur sous la forme relation table_name doesn't exist pour la table de la clause WITH.

Vous ne pouvez pas spécifier une autre clause WITH à l’intérieur d’une sous-requête de clause WITH.

Vous ne pouvez pas effectuer de références futures aux tables définies par des sous-requêtes de clause WITH. Par exemple, la requête suivante renvoie une erreur en raison de la référence future à la table W2 dans la définition de table W1 :


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

Une sous-requête de clause WITH peut ne pas comporter d’instruction SELECT INTO ; cependant, vous pouvez utiliser une clause WITH dans une instruction SELECT INTO.

Expressions récursives de table commune

Une expression de table commune (CTE) récursive est une CTE qui se réfère à elle-même. Une CTE récursive est utile pour interroger des données hiérarchiques, telles que des organigrammes qui montrent les rapports hiérarchiques entre les employés et les responsables. Consultez Exemple : CTE récursive.

Une autre utilisation courante est une nomenclature à plusieurs niveaux, lorsqu’un produit est constitué de nombreux composants et que chaque composant lui-même est également constitué d’autres composants ou sous-ensembles.

Veillez à limiter la profondeur de récursivité en incluant une clause WHERE dans la deuxième sous-requête SELECT de la requête récursive. Pour obtenir un exemple, consultez Exemple : CTE récursive. Sinon, une erreur similaire à ce qui suit peut se produire :

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.

Note

max_recursion_rows est un paramètre définissant le nombre maximal de lignes qu’une CTE récursive peut renvoyer afin d’éviter les boucles de récursion infinies. Nous vous recommandons de ne pas modifier cette valeur pour qu’elle soit supérieure à la valeur par défaut. Cela permet d’éviter que les problèmes de récursion infinie dans vos requêtes n’occupent trop d’espace dans votre cluster.

Vous pouvez spécifier un ordre de tri et une limite sur le résultat de la CTE récursive. Vous pouvez inclure des options group by et distinct sur le résultat final de la CTE récursive.

Vous ne pouvez pas spécifier une autre clause WITH RECURSIVE dans une sous-requête. Le membre recursive_query ne peut pas inclure de clause order by ou limit.

Exemples

L’exemple suivant illustre le cas le plus simple possible d’une requête contenant une clause WITH. La requête WITH nommée VENUECOPY sélectionne toutes les lignes de la table VENUE. La requête principale, à son tour, sélectionne toutes les lignes de VENUECOPY. La table VENUECOPY existe uniquement pendant la durée de cette requête.


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)

L’exemple suivant montre une clause WITH qui produit deux tables, nommées VENUE_SALES et TOP_VENUES. La deuxième table de requête WITH effectue la sélection à partir de la première. A son tour, la clause WHERE du bloc de requête principal contient une sous-requête qui restreint la table 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)

Les deux exemples suivants illustrent les règles sur la portée des références de table dans les sous-requêtes de clause WITH. La première requête s’exécute, mais la deuxième échoue avec une erreur prévue. La première requête a une sous-requête de clause WITH à l’intérieur de la liste SELECT de la requête principale. La table définie par la clause WITH (HOLIDAYS) est référencée dans la clause FROM de la sous-requête de la liste 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 deuxième requête échoue, car elle tente de faire référence à la table HOLIDAYS de la requête principale, ainsi que dans la sous-requête de liste SELECT. Les références de requête principale sont hors de portée.


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

Exemple : CTE récursive

Voici un exemple de CTE récursive qui renvoie les employés qui relèvent directement ou indirectement de John. La requête récursive contient une clause WHERE pour limiter la profondeur de récursivité à moins de 4 niveaux.


--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;

Voici le résultat de la requête.


    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

Voici un organigramme du service de John.

Avertissement JavaScript est désactivé ou n'est pas disponible dans votre navigateur.

Pour que vous puissiez utiliser la documentation AWS, Javascript doit être activé. Vous trouverez des instructions sur les pages d'aide de votre navigateur.

Conventions de rédaction

SELECT

Liste SELECT