Amazon Redshift ne prendra plus en charge la création de nouveaux Python à UDFs partir du patch 198. UDFs Le Python existant continuera de fonctionner jusqu'au 30 juin 2026. Pour plus d’informations, consultez le [ billet de blog ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra. # 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\$1table\$1expressions*. 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\$1table\$1expression* 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\$1table\$1expression* : ``` CTE_table_name (column_name [, ...] ) AS ( recursive_query ) ``` ## Parameters RECURSIVE Mot-clé qui identifie la requête comme étant une CTE récursive. Ce mot-clé est requis si l’expression *common\$1table\$1expression* 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 si la clause WITH contient plusieurs CTEs récursifs. En général, une CTE récursive est une sous-requête UNION ALL avec deux parties. *common\$1table\$1expression* Définit une table temporaire que vous pouvez référencer dans [Clause FROM](r_FROM_clause30.md) et qui n’est utilisée que pendant l’exécution de la requête à laquelle elle appartient. *CTE\$1table\$1name* 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](r_FROM_clause30.md). *column\$1name* 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\$1name* est facultatif. Pour une CTE récursive, *column\$1name* est obligatoire. *query* Toute requête SELECT qu’Amazon Redshift prend en charge. Consultez [SELECT](r_SELECT_synopsis.md). *recursive\$1query* 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\$1table\$1name*. 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\$1table\$1name* dans sa clause FROM. C’est ce qu’on appelle le recursive member. *recursive\$1query* contient une condition WHERE pour mettre fin à la *recursive\$1query*. ## 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](#r_WITH_clause-recursive-cte-example). 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](#r_WITH_clause-recursive-cte-example). 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\$1query* 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\$1SALES et TOP\$1VENUES. 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\$1VENUES. ``` 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. ![\[Organigramme du service de John.\]](http://docs.aws.amazon.com/fr_fr/redshift/latest/dg/images/org-chart.png)