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.
# SELECT
renvoie les lignes des tables, vues et fonctions définies par l’utilisateur.
**Note**
La taille maximale d’une instruction SQL est de 16 Mo.
## Syntaxe
```
[ WITH with_subquery [, ...] ]
SELECT
[ TOP number | [ ALL | DISTINCT ]
* | expression [ AS output_name ] [, ...] ]
[ EXCLUDE column_list ]
[ FROM table_reference [, ...] ]
[ WHERE condition ]
[ [ START WITH expression ] CONNECT BY expression ]
[ GROUP BY ALL | expression [, ...] ]
[ HAVING condition ]
[ QUALIFY condition ]
[ { UNION | ALL | INTERSECT | EXCEPT | MINUS } query ]
[ ORDER BY expression [ ASC | DESC ] ]
[ LIMIT { number | ALL } ]
[ OFFSET start ]
```
**Topics**
+ [Syntaxe](#r_SELECT_synopsis-synopsis)
+ [Clause WITH](r_WITH_clause.md)
+ [Liste SELECT](r_SELECT_list.md)
+ [EXCLUDE column\$1list](r_EXCLUDE_list.md)
+ [Clause FROM](r_FROM_clause30.md)
+ [Clause WHERE](r_WHERE_clause.md)
+ [Clause GROUP BY](r_GROUP_BY_clause.md)
+ [Clause HAVING](r_HAVING_clause.md)
+ [Clause QUALIFY](r_QUALIFY_clause.md)
+ [UNION, INTERSECT et EXCEPT](r_UNION.md)
+ [Clause ORDER BY](r_ORDER_BY_clause.md)
+ [Clause CONNECT BY](r_CONNECT_BY_clause.md)
+ [Exemples de sous-requête](r_Subquery_examples.md)
+ [Sous-requêtes corrélées](r_correlated_subqueries.md)
# 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)
# Liste SELECT
**Topics**
+ [Syntaxe](#r_SELECT_list-synopsis)
+ [Parameters](#r_SELECT_list-parameters)
+ [Notes d’utilisation](#r_SELECT_list_usage_notes)
+ [Exemples](#r_SELECT_list-examples)
La liste SELECT nomme les colonnes, fonctions et expressions que la requête doit renvoyer. La liste représente le résultat de la requête.
Pour plus d’informations sur les fonctions SQL, consultez [Référence sur les fonctions SQL](c_SQL_functions.md). Pour plus d’informations sur les expressions, consultez [Expressions conditionnelles](c_conditional_expressions.md).
## Syntaxe
```
SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
```
## Parameters
TOP *nombre*
TOP accepte un nombre entier positif comme argument, qui définit le nombre de lignes retournées au client. Le comportement avec la clause TOP est identique à celui avec la clause LIMIT. Le nombre de lignes qui est renvoyé est fixe, contrairement à l’ensemble de lignes. Pour renvoyer un ensemble de lignes constant, utilisez TOP ou LIMIT avec une clause ORDER BY.
ALL
Mot-clé redondant qui définit le comportement par défaut, si vous ne spécifiez pas DISTINCT. `SELECT ALL *` signifie la même chose que `SELECT *` (sélectionner toutes les lignes de toutes les colonnes et conserver les doublons).
DISTINCT
Option qui élimine les lignes en double du jeu de résultats, en fonction de la correspondance des valeurs dans une ou plusieurs colonnes.
Si votre application autorise les clés étrangères ou primaires non valides, les requêtes peuvent renvoyer des résultats incorrects. Par exemple, une requête SELECT DISTINCT peut renvoyer des lignes dupliquées si la colonne de clé primaire ne contient pas que des valeurs uniques. Pour plus d'informations, consultez [Définition des contraintes de table](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html).
\$1 (astérisque)
renvoie tout le contenu de la table (toutes les colonnes et toutes les lignes).
*expression*
Expression formée d’une ou de plusieurs colonnes qui existent dans les tables référencées par la requête. Une expression peut contenir des fonctions SQL. Par exemple :
```
avg(datediff(day, listtime, saletime))
```
AS *alias\$1colonne*
Nom temporaire de la colonne utilisé dans le jeu de résultats final. Le mot-clé AS est facultatif. Par exemple :
```
avg(datediff(day, listtime, saletime)) as avgwait
```
Si vous ne spécifiez pas un alias pour une expression qui n’est pas un nom de colonne simple, le jeu de résultats applique un nom par défaut à cette colonne.
L’alias est reconnu juste après sa définition dans la liste cible. Vous pouvez utiliser un alias dans d’autres expressions définies après lui dans la même liste cible. L’exemple suivant illustre ce scénario.
```
select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data;
```
La référence latérale à un alias vous évite de devoir répéter l’expression disposant d’un alias lors de la création d’expressions plus complexes dans la même liste cible. Lorsqu’Amazon Redshift analyse ce type de référence, il rajoute simplement dans la ligne les alias précédemment définis. S’il existe une colonne avec le même nom défini dans la clause `FROM` que dans l’expression disposant d’un alias précédente, la colonne dans la clause `FROM` a la priorité. Par exemple, dans la requête précédente, s’il existe une colonne nommée « probability » dans la table raw\$1data, le terme « probability » dans la seconde expression de la liste cible fait référence à cette colonne et non au nom d’alias « probability ».
## Notes d’utilisation
TOP est une extension SQL ; elle fournit une alternative au comportement LIMIT. Vous ne pouvez pas utiliser TOP et LIMIT dans la même requête.
## Exemples
L’exemple suivant renvoie 10 lignes de la table SALES. Bien que la requête utilise la clause TOP, elle renvoie toujours un ensemble imprévisible de lignes, car aucune clause ORDER BY n’est spécifiée.
```
select top 10 *
from sales;
```
La requête suivante est fonctionnellement équivalente, mais utilise une clause LIMIT au lieu d’une clause TOP :
```
select *
from sales
limit 10;
```
L’exemple suivant renvoie les 10 premières lignes de la table SALES en utilisant la clause TOP, classées dans la colonne QTYSOLD par ordre décroissant.
```
select top 10 qtysold, sellerid
from sales
order by qtysold desc, sellerid;
qtysold | sellerid
--------+----------
8 | 518
8 | 520
8 | 574
8 | 718
8 | 868
8 | 2663
8 | 3396
8 | 3726
8 | 5250
8 | 6216
(10 rows)
```
L’exemple suivant renvoie les deux premières valeurs QTYSOLD et SELLERID de la table SALES, classées par la colonne QTYSOLD :
```
select top 2 qtysold, sellerid
from sales
order by qtysold desc, sellerid;
qtysold | sellerid
--------+----------
8 | 518
8 | 520
(2 rows)
```
L’exemple suivant montre la liste des groupes de catégories distincts de la table CATEGORY :
```
select distinct catgroup from category
order by 1;
catgroup
----------
Concerts
Shows
Sports
(3 rows)
--the same query, run without distinct
select catgroup from category
order by 1;
catgroup
----------
Concerts
Concerts
Concerts
Shows
Shows
Shows
Sports
Sports
Sports
Sports
Sports
(11 rows)
```
L’exemple suivant renvoie l’ensemble distinct de numéros de semaine pour décembre 2008. Sans la clause DISTINCT, l’instruction renverrait 31 lignes, soit une pour chaque jour du mois.
```
select distinct week, month, year
from date
where month='DEC' and year=2008
order by 1, 2, 3;
week | month | year
-----+-------+------
49 | DEC | 2008
50 | DEC | 2008
51 | DEC | 2008
52 | DEC | 2008
53 | DEC | 2008
(5 rows)
```
# EXCLUDE column\$1list
EXCLUDE column\$1list nomme les colonnes exclues des résultats de la requête. L’utilisation de l’option EXCLUDE est utile lorsque seul un sous-ensemble de colonnes doit être exclu d’une table *volumineuse*, c’est-à-dire une table contenant de nombreuses colonnes.
**Topics**
+ [Syntaxe](#r_EXCLUDE_list-synopsis)
+ [Parameters](#r_EXCLUDE_list-parameters)
+ [Exemples](#r_EXCLUDE_list-examples)
## Syntaxe
```
EXCLUDE column_list
```
## Parameters
*column\$1list*
Liste séparée par des virgules d’un ou de plusieurs noms de colonnes qui existent dans les tables référencées par la requête. *column\$1list* peut éventuellement être placé entre parenthèses. Seuls les noms de colonnes sont pris en charge dans la liste d’exclusion des noms de colonnes, et non les expressions (telles que `upper(col1)`) ou les astérisques (\$1).
```
column-name, ... | ( column-name, ... )
```
Par exemple :
```
SELECT * EXCLUDE col1, col2 FROM tablea;
```
```
SELECT * EXCLUDE (col1, col2) FROM tablea;
```
## Exemples
Les exemples suivants utilisent la table SALES qui contient des colonnes : salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission et saletime. Pour plus d’informations sur la table SALES, consultez [Exemple de base de données](c_sampledb.md).
L’exemple suivant renvoie des lignes de la table SALES, mais exclut la colonne SALETIME.
```
SELECT * EXCLUDE saletime FROM sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
L’exemple suivant renvoie des lignes de la table SALES, mais exclut les colonnes QTYSOLD et SALETIME.
```
SELECT * EXCLUDE (qtysold, saletime) FROM sales;
salesid | listid | sellerid | buyerid | eventid | dateid | pricepaid | commission
--------+---------+----------+---------+---------+---------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 482 | 72.3
...
```
L’exemple suivant crée une vue qui renvoie des lignes de la table SALES, mais exclut la colonne SALETIME.
```
CREATE VIEW sales_view AS SELECT * EXCLUDE saletime FROM sales;
SELECT * FROM sales_view;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
L’exemple suivant sélectionne uniquement les colonnes qui ne sont pas exclues d’une table temporaire.
```
SELECT * EXCLUDE saletime INTO TEMP temp_sales FROM sales;
SELECT * FROM temp_sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
# Clause FROM
La clause FROM d’une requête répertorie les références de table (tables, vues et sous-requêtes) à partir desquelles les données sont sélectionnées. Si plusieurs références de table sont répertoriées, les tables doivent être jointes, à l’aide de la syntaxe appropriée de la clause FROM ou de la clause WHERE. Si aucun critère de jointure n’est spécifié, le système traite la requête comme jointure croisée (produit cartésien).
**Topics**
+ [Syntaxe](#r_FROM_clause30-synopsis)
+ [Parameters](#r_FROM_clause30-parameters)
+ [Notes d’utilisation](#r_FROM_clause_usage_notes)
+ [Exemples PIVOT et UNPIVOT](r_FROM_clause-pivot-unpivot-examples.md)
+ [Exemples de clause JOIN](r_Join_examples.md)
+ [Exemples de UNNEST](r_FROM_clause-unnest-examples.md)
## Syntaxe
```
FROM table_reference [, ...]
```
où *table\$1reference* est l’une des références suivantes :
```
with_subquery_table_name [ table_alias ]
table_name [ * ] [ table_alias ]
( subquery ) [ table_alias ]
table_reference [ NATURAL ] join_type table_reference
[ ON join_condition | USING ( join_column [, ...] ) ]
table_reference join_type super_expression
[ ON join_condition ]
table_reference PIVOT (
aggregate(expr) [ [ AS ] aggregate_alias ]
FOR column_name IN ( expression [ AS ] in_alias [, ...] )
) [ table_alias ]
table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] (
value_column_name
FOR name_column_name IN ( column_reference [ [ AS ]
in_alias ] [, ...] )
) [ table_alias ]
UNPIVOT expression AS value_alias [ AT attribute_alias ]
( super_expression.attribute_name ) AS value_alias [ AT index_alias ]
UNNEST ( column_reference )
[AS] table_alias ( unnested_column_name )
UNNEST ( column_reference ) WITH OFFSET
[AS] table_alias ( unnested_column_name, [offset_column_name] )
```
*table\$1alias* (facultatif) peut être utilisé pour donner des noms temporaires aux tables et aux références de tables complexes et, si vous le souhaitez, à leurs colonnes également, comme suit :
```
[ AS ] alias [ ( column_alias [, ...] ) ]
```
## Parameters
*with\$1subquery\$1table\$1name*
Table définie par une sous-requête dans la [Clause WITH](r_WITH_clause.md).
*table\$1name*
Nom d’une table ou d’une vue.
*alias*
Nom alternatif temporaire d’une table ou d’une vue. Un alias doit être fourni pour une table dérivée d’une sous-requête. Dans les autres références de table, les alias sont facultatifs. Le mot-clé AS est toujours facultatif. Les alias de table offrent un raccourci pratique pour identifier les tables dans d’autres parties d’une requête, telles que la clause WHERE. Par exemple :
```
select * from sales s, listing l
where s.listid=l.listid
```
*alias\$1colonne*
Nom alternatif temporaire pour une colonne dans une table ou une vue.
*sous-requête*
Une expression de requête qui correspond à une table. La table existe uniquement pendant la durée de la requête et reçoit généralement un nom ou un *alias*. Toutefois, l’alias n’est pas obligatoire. Vous pouvez aussi définir des noms de colonnes pour les tables qui proviennent de sous-requêtes. Il est important de nommer les alias de colonne lorsque vous souhaitez joindre les résultats des sous-requêtes à d’autres tables et lorsque vous voulez sélectionner ou limiter les colonnes ailleurs dans la requête.
Une sous-requête peut contenir une clause ORDER BY, mais cette clause peut n’avoir aucun effet si une clause LIMIT ou OFFSET n’est pas également spécifiée.
NATURAL
Définit une jointure qui utilise automatiquement toutes les paires de colonnes portant le même nom dans les deux tables comme colonnes de jointure. Aucune condition de jointure explicite n’est nécessaire. Par exemple, si les tables CATEGORY et EVENT ont toutes deux des colonnes nommées CATID, une jointure naturelle des tables est une jointure sur leurs colonnes CATID.
Si une jointure NATURAL est spécifiée, mais qu’il n’y a aucune paire de colonnes portant le même nom dans les tables à joindre, la requête se résout par défaut en une jointure croisée.
*join\$1type*
Spécifiez l’un des types de jointure suivants :
+ [INNER] JOIN
+ LEFT [OUTER] JOIN
+ RIGHT [OUTER] JOIN
+ FULL [OUTER] JOIN
+ CROSS JOIN
Les jointures croisées sont des jointures non qualifiées ; elles renvoient le produit cartésien des deux tables.
Les jointures internes et externes sont des jointures qualifiées. Elles sont qualifiés implicitement (en jointures naturelles), avec la syntaxe ON ou USING de la clause FROM, ou avec une condition de clause WHERE.
Une jointure interne renvoie les lignes correspondantes uniquement, en fonction de la condition de jointure ou d’une liste de colonnes de jointure. Une jointure externe renvoie toutes les lignes que la jointure interne équivalente renvoyerait, plus les lignes non correspondantes de la table de « gauche », de la table de « droite » ou des deux tables. La table de gauche est la première table de la liste et la table de droite la deuxième table. Les lignes non correspondantes contiennent des valeurs NULL pour combler les écarts dans les colonnes de sortie.
ON *condition\$1jointure*
Type de spécification de jointure où les colonnes de jointure sont définies comme condition qui suit le mot-clé ON. Par exemple :
```
sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
```
USING ( *colonne\$1jointure* [, ...] )
Type de spécification de jointure où les colonnes de jointure sont affichées entre parenthèses. Si plusieurs colonnes de jointure sont spécifiées, elles sont séparées par des virgules. Le mot-clé USING doit précéder la liste. Par exemple :
```
sales join listing
using (listid,eventid)
```
PIVOT
Fait pivoter la sortie des lignes vers les colonnes, dans le but de représenter les données tabulaires dans un format facile à lire. La sortie est représentée horizontalement sur plusieurs colonnes. PIVOT est similaire à une requête GROUP BY avec une agrégation, utilisant une expression agrégée pour spécifier un format de sortie. Toutefois, contrairement à GROUP BY, les résultats sont renvoyés sous forme de colonnes plutôt que de lignes.
Pour bénéficier d’exemples montrant comment interroger avec PIVOT et UNPIVOT, consultez [Exemples PIVOT et UNPIVOT](r_FROM_clause-pivot-unpivot-examples.md).
UNPIVOT
*Rotation de colonnes vers des lignes avec UNPIVOT* : l’opérateur UNPIVOT transforme les colonnes de résultats, d’une table d’entrée ou de résultats de requête, en lignes, pour faciliter la lecture de la sortie. UNPIVOT combine les données de ses colonnes d’entrée en deux colonnes de résultats : une colonne de noms et une colonne de valeurs. La colonne de noms contient les noms de colonnes provenant de l’entrée, sous forme d’entrées de ligne. La colonne de valeurs contient des valeurs provenant des colonnes d’entrée, telles que les résultats d’une agrégation. Par exemple, le nombre d’éléments dans différentes catégories.
*Dépivotement de l’objet avec UNPIVOT (SUPER)* : vous pouvez effectuer le dépivotement d’un objet, où *expression* est une expression SUPER faisant référence à un autre élément de la clause FROM. Pour plus d’informations, consultez [Dépivotement d’objet](query-super.md#unpivoting). Il contient également des exemples montrant comment interroger des données semi-structurées telles que des données au format JSON.
*super\$1expression*
Expression SUPER valide. Amazon Redshift renvoie une ligne pour chaque valeur de l’attribut spécifié. Pour plus d’informations sur le type de données SUPER, consultez [Type SUPER](r_SUPER_type.md). Pour plus d’informations sur les valeurs SUPER désimbriquées, consultez [Désimbriquer des requêtes](query-super.md#unnest).
*nom\$1attribut*
Nom d’un attribut dans l’expression SUPER.
*index\$1alias*
Alias pour l’index qui indique la position de la valeur dans l’expression SUPER.
UNNEST
Étend une structure imbriquée, généralement un tableau SUPER, en colonnes contenant les éléments désimbriqués. Pour plus d’informations sur la désimbrication des données SUPER, consultez [Interrogation de données semi-structurées](query-super.md). Pour obtenir des exemples, consultez [Exemples de UNNEST](r_FROM_clause-unnest-examples.md).
*unnested\$1column\$1name*
Nom de la colonne qui contient les éléments désimbriqués.
UNNEST ... WITH OFFSET
Ajoute une colonne de décalage à la sortie désimbriquée, le décalage représentant l’indice de base zéro de chaque élément du tableau. Cette variante est utile lorsque vous souhaitez voir la position des éléments dans un tableau. Pour plus d’informations sur la désimbrication des données SUPER, consultez [Interrogation de données semi-structurées](query-super.md). Pour obtenir des exemples, consultez [Exemples de UNNEST](r_FROM_clause-unnest-examples.md).
*offset\$1column\$1name*
Nom personnalisé pour la colonne de décalage qui vous permet de définir explicitement la manière dont la colonne d’index apparaîtra dans la sortie. Ce paramètre est facultatif. Par défaut, le nom de la colonne de décalage est `offset_col`.
## Notes d’utilisation
Les colonnes de jointure doivent avoir des types de données comparables.
Une jointure NATURAL ou USING conserve seulement l’une de chaque paire de colonnes de jointure dans le jeu de résultats intermédiaire.
Une jointure avec la syntaxe ON conserve les deux colonnes de jointure dans son jeu de résultats intermédiaire.
Consultez également [Clause WITH](r_WITH_clause.md).
# Exemples PIVOT et UNPIVOT
PIVOT et UNPIVOT sont des paramètres de la clause FROM qui font pivoter la sortie de la requête des lignes vers les colonnes et des colonnes vers les lignes, respectivement. Ils représentent des résultats de requêtes tabulaires dans un format facile à lire. Les exemples suivants utilisent des données et des requêtes de test pour montrer comment les utiliser.
Pour plus d’informations sur ces paramètres et d’autres, consultez [Clause FROM](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html).
## Exemples PIVOT
Configurez l’exemple de table et de données, puis utilisez-les pour exécuter les requêtes d’exemple suivantes.
```
CREATE TABLE part (
partname varchar,
manufacturer varchar,
quality int,
price decimal(12, 2)
);
INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);
INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);
INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);
```
PIVOT sur `partname` avec une agrégation `AVG` sur `price`.
```
SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);
```
La requête génèrera la sortie suivante.
```
prop | rudder | wing
---------+----------+---------
10.33 | 2.71 | 11.50
```
Dans l’exemple précédent, les résultats sont transformés en colonnes. L’exemple suivant montre une requête `GROUP BY` qui renvoie les prix moyens sous forme de lignes plutôt que de colonnes.
```
SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
```
La requête génèrera la sortie suivante.
```
partname | avg
----------+-------
prop | 10.33
rudder | 2.71
wing | 11.50
```
Un exemple `PIVOT` avec `manufacturer` en tant que colonne implicite.
```
SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
```
La requête génèrera la sortie suivante.
```
manufacturer | 1 | 2 | null
-------------------+----+----+------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
Les colonnes de table d’entrée qui ne sont pas référencées dans la définition de `PIVOT` sont ajoutées implicitement à la table de résultats. C’est le cas pour la colonne `manufacturer` de l’exemple précédent. L’exemple montre également que `NULL` est une valeur valide pour l’opérateur `IN`.
`PIVOT` dans l’exemple ci-dessus renvoie des informations similaires à celles de la requête suivante, qui inclut `GROUP BY`. La différence est que `PIVOT` renvoie la valeur `0` pour la colonne `2` et le fabricant `small parts co`. La requête `GROUP BY` ne contient pas de ligne correspondante. Dans la plupart des cas, `PIVOT` insère `NULL` si une ligne ne contient pas de données d’entrée pour une colonne donnée. Toutefois, l’agrégat de nombre ne renvoie pas `NULL` et `0` est la valeur par défaut.
```
SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;
```
La requête génèrera la sortie suivante.
```
manufacturer | quality | count
---------------------+---------+-------
big parts co | | 1
big parts co | 2 | 1
big parts co | 1 | 1
local parts co | 2 | 1
local parts co | 1 | 1
local parts co | | 1
small parts co | 1 | 1
small parts co | | 2
```
L’opérateur PIVOT accepte les alias facultatifs sur l’expression agrégée et sur chaque valeur pour l’opérateur `IN`. Utilisez des alias pour personnaliser les noms des colonnes. S’il n’y a pas d’alias agrégé, seuls les alias de liste `IN` sont utilisés. Sinon, l’alias agrégé est ajouté au nom de la colonne avec un trait de soulignement pour séparer les noms.
```
SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);
```
La requête génèrera la sortie suivante.
```
manufacturer | high_count | low_count | na_count
-------------------+-------------+-----------+----------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
Configurez les exemples de table et de données suivants, puis utilisez-les pour exécuter les requêtes d’exemple suivantes. Les données représentent les dates de réservation pour un ensemble d’hôtels.
```
CREATE TABLE bookings (
booking_id int,
hotel_code char(8),
booking_date date,
price decimal(12, 2)
);
INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);
INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);
INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);
INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);
INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);
INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);
INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);
INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);
INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);
```
Dans cet exemple de requête, les enregistrements de réservations sont comptabilisés pour donner un total pour chaque semaine. La date de fin de chaque semaine devient un nom de colonne.
```
SELECT * FROM
(SELECT
booking_id,
(date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
hotel_code AS "hotel code"
FROM bookings
) PIVOT (
count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18')
);
```
La requête génèrera la sortie suivante.
```
hotel code | 2023-02-04 | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
FOREST_L | 3 | 2 | 1
DESERT_S | 4 | 3 | 2
OCEAN_WV | 3 | 3 | 3
CITY_BLD | 3 | 1 | 2
```
Amazon Redshift ne prend pas en charge CROSSTAB pour pivoter sur plusieurs colonnes. Mais vous pouvez transformer les données de ligne en colonnes, comme pour un regroupement avec PIVOT, à l’aide d’une requête telle que la suivante. L’exemple précédent utilise les mêmes données de réservation que celles de l’exemple précédent.
```
SELECT
booking_date,
MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END) AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;
```
L’exemple de requête donne lieu à des dates de réservation répertoriées à côté de phrases courtes qui indiquent quels hôtels sont réservés.
```
booking_date | forest_l | desert_s | ocean_wv
---------------+------------------+------------------+--------------------
2023-02-01 | forest is booked | desert is booked | ocean is booked
2023-02-02 | forest is booked | desert is booked | ocean is booked
2023-02-04 | forest is booked | desert is booked | ocean is booked
2023-02-05 | | desert is booked |
2023-02-06 | | desert is booked |
```
Voici des notes d’utilisation pour `PIVOT` :
+ `PIVOT`peut être appliqué aux tables, aux sous-requêtes et aux expressions de table communes (CTEs). `PIVOT`ne peut être appliqué à aucune `JOIN` expression, récursive CTEs ou `UNPIVOT` expression. `PIVOT` De plus, les expressions non imbriquées `SUPER` et les tables imbriquées Redshift Spectrum ne sont pas prises en charge.
+ `PIVOT` prend en charge les fonctions agrégées `COUNT`, `SUM`, `MIN`, `MAX` et `AVG`.
+ L’expression agrégée `PIVOT` doit être un appel d’une fonction agrégée prise en charge. Les expressions complexes en plus de l’agrégat ne sont pas prises en charge. Les arguments agrégés ne peuvent pas contenir de références à d’autres tables que la table d’entrée `PIVOT`. Les références corrélées à une requête parente ne sont pas prises en charge. L’argument agrégé peut contenir des sous-requêtes. Elles peuvent être corrélées en interne ou sur la table d’entrée `PIVOT`.
+ Les valeurs de liste `PIVOT IN` ne peuvent pas être des références de colonnes ou des sous-requêtes. Chaque valeur doit être de type compatible avec la référence de colonne `FOR`.
+ Si les valeurs de liste `IN` n’ont pas d’alias, `PIVOT` génère des noms de colonnes par défaut. Pour des valeurs constantes `IN` telles que « abc » ou 5, le nom de colonne par défaut est la constante elle-même. Pour toute expression complexe, le nom de la colonne est un nom par défaut Amazon Redshift standard tel que `?column?`.
## Exemples UNPIVOT
Configurez les exemples de données et utilisez-les pour exécuter les exemples suivants.
```
CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);
INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);
```
`UNPIVOT` sur les colonnes d’entrée rouges, vertes et bleues.
```
SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
cnt FOR color IN (red, green, blue)
);
```
La requête génèrera la sortie suivante.
```
color | cnt
-------+-----
red | 15
red | 35
red | 10
green | 20
green | 23
blue | 7
blue | 40
```
Par défaut, les valeurs `NULL` de la colonne d’entrée sont ignorées et ne produisent pas de ligne de résultats.
L’exemple suivant montre `UNPIVOT` avec `INCLUDE NULLS`.
```
SELECT *
FROM (
SELECT red, green, blue
FROM count_by_color
) UNPIVOT INCLUDE NULLS (
cnt FOR color IN (red, green, blue)
);
```
En voici le résultat obtenu.
```
color | cnt
-------+-----
red | 15
red | 35
red | 10
green | 20
green |
green | 23
blue | 7
blue | 40
blue |
```
Si le paramètre `INCLUDING NULLS` est défini, les valeurs d’entrée `NULL` génèrent des lignes de résultats.
`The following query shows UNPIVOT` avec `quality` en tant que colonne implicite.
```
SELECT *
FROM count_by_color UNPIVOT (
cnt FOR color IN (red, green, blue)
);
```
La requête génèrera la sortie suivante.
```
quality | color | cnt
---------+-------+-----
high | red | 15
normal | red | 35
low | red | 10
high | green | 20
low | green | 23
high | blue | 7
normal | blue | 40
```
Les colonnes de la table d’entrée qui ne sont pas référencées dans la définition de `UNPIVOT` sont ajoutées implicitement à la table de résultats. Dans cet exemple, c’est le cas pour la colonne `quality`.
L’exemple suivant en est une illustration de `UNPIVOT` avec des alias pour les valeurs dans la liste `IN`.
```
SELECT *
FROM count_by_color UNPIVOT (
cnt FOR color IN (red AS r, green AS g, blue AS b)
);
```
La requête précédente génère le résultat suivant.
```
quality | color | cnt
---------+-------+-----
high | r | 15
normal | r | 35
low | r | 10
high | g | 20
low | g | 23
high | b | 7
normal | b | 40
```
L’opérateur `UNPIVOT` accepte les alias facultatifs sur chaque valeur de liste `IN`. Chaque alias permet de personnaliser les données de chaque colonne `value`.
Voici des notes d’utilisation pour `UNPIVOT`.
+ `UNPIVOT`peut être appliqué aux tables, aux sous-requêtes et aux expressions de table communes (CTEs). `UNPIVOT`ne peut être appliqué à aucune `JOIN` expression, récursive CTEs ou `UNPIVOT` expression. `PIVOT` De plus, les expressions non imbriquées `SUPER` et les tables imbriquées Redshift Spectrum ne sont pas prises en charge.
+ La liste `UNPIVOT IN` doit contenir uniquement des références de colonnes de table d’entrée. Les colonnes de la liste `IN` doivent avoir un type commun avec lequel elles sont toutes compatibles. La colonne de valeurs `UNPIVOT` a ce type commun. La colonne de noms `UNPIVOT` est de type `VARCHAR`.
+ Si une valeur de liste `IN` ne possède pas d’alias, `UNPIVOT` utilise le nom de la colonne comme valeur par défaut.
# Exemples de clause JOIN
Une clause SQL JOIN permet de combiner les données de deux ou plusieurs tables sur la base de champs communs. Les résultats peuvent ou non changer en fonction de la méthode de jointure spécifiée. Pour obtenir plus d’informations sur la syntaxe d’une clause JOIN, consultez [Parameters](r_FROM_clause30.md#r_FROM_clause30-parameters).
Les exemples suivants utilisent les exemples de données `TICKIT`. Pour obtenir plus d’informations sur le schéma de la base de données, consultez [Exemple de base de données](c_sampledb.md). Pour savoir comment charger des exemples de données, consultez [Chargement des données](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html) dans le *Guide de démarrage d’Amazon Redshift*.
La requête suivante est une jointure interne (sans le mot-clé JOIN) entre la table LISTING et la table SALES, où la valeur LISTID de la table LISTING est comprise entre 1 et 5. Cette requête met en correspondance les valeurs de la colonne LISTID dans les tables LISTING (table de gauche) et SALES (table de droite). Les résultats montrent que les valeurs LISTID 1, 4 et 5 correspondent aux critères.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing, sales
where listing.listid = sales.listid
and listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
La requête suivante est une jointure externe gauche. Les jointures externes gauche et droite conservent les valeurs de l’une des tables jointes quand aucune correspondance n’est trouvée dans l’autre table. Les tables gauche et droite sont la première et la deuxième répertoriées dans la syntaxe. Les valeurs NULL sont utilisées pour combler les « écarts » du jeu de résultats. Cette requête fait correspondre les valeurs de la colonne LISTID dans la table LISTING (la table de gauche) et la table SALES (la table de droite). Les résultats montrent que LISTIDs 2 et 3 n'ont donné lieu à aucune vente.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing left outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
La requête suivante est une jointure externe droite. Cette requête fait correspondre les valeurs de la colonne LISTID dans la table LISTING (la table de gauche) et la table SALES (la table de droite). Les résultats montrent que LISTIDs 1, 4 et 5 correspondent aux critères.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing right outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
La requête suivante est une jointure complète. Les jointures complètes conservent les valeurs des tables jointes lorsqu’aucune correspondance n’est trouvée dans l’autre table. Les tables gauche et droite sont la première et la deuxième répertoriées dans la syntaxe. Les valeurs NULL sont utilisées pour combler les « écarts » du jeu de résultats. Cette requête fait correspondre les valeurs de la colonne LISTID dans la table LISTING (la table de gauche) et la table SALES (la table de droite). Les résultats montrent que LISTIDs 2 et 3 n'ont donné lieu à aucune vente.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
La requête suivante est une jointure complète. Cette requête fait correspondre les valeurs de la colonne LISTID dans la table LISTING (la table de gauche) et la table SALES (la table de droite). Seules les lignes qui ne génèrent aucune vente (LISTIDs 2 et 3) apparaissent dans les résultats.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
and (listing.listid IS NULL or sales.listid IS NULL)
group by 1
order by 1;
listid | price | comm
-------+--------+--------
2 | NULL | NULL
3 | NULL | NULL
```
L’exemple suivant est une jointure interne avec la clause ON. Dans ce cas, les lignes NULL ne sont pas renvoyées.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
La requête suivante est une jointure croisée ou cartésienne de la table LISTING et de la table SALES avec un prédicat pour limiter les résultats. Cette requête correspond aux valeurs des colonnes LISTID de la table SALES et de la table LISTING pour LISTIDs 1, 2, 3, 4 et 5 dans les deux tables. Les résultats montrent que 20 lignes correspondent aux critères.
```
select sales.listid as sales_listid, listing.listid as listing_listid
from sales cross join listing
where sales.listid between 1 and 5
and listing.listid between 1 and 5
order by 1,2;
sales_listid | listing_listid
-------------+---------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
5 | 1
5 | 1
5 | 2
5 | 2
5 | 3
5 | 3
5 | 4
5 | 4
5 | 5
5 | 5
```
L’exemple suivant est une jointure naturelle entre deux tables. Dans ce cas, les colonnes listid, sellerid, eventid et dateid présentent des noms et des types de données identiques dans les deux tables et sont donc utilisées comme colonnes de jointure. Les résultats sont limités à seulement cinq lignes.
```
select listid, sellerid, eventid, dateid, numtickets
from listing natural join sales
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+-----------+---------+--------+-----------
113 | 29704 | 4699 | 2075 | 22
115 | 39115 | 3513 | 2062 | 14
116 | 43314 | 8675 | 1910 | 28
118 | 6079 | 1611 | 1862 | 9
163 | 24880 | 8253 | 1888 | 14
```
L’exemple suivant est une jointure entre deux tables avec la clause USING. Dans ce cas, les colonnes listid et eventid sont utilisées comme colonnes de jointure. Les résultats sont limités à seulement cinq lignes.
```
select listid, listing.sellerid, eventid, listing.dateid, numtickets
from listing join sales
using (listid, eventid)
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+----------+---------+--------+-----------
1 | 36861 | 7872 | 1850 | 10
4 | 8117 | 4337 | 1970 | 8
5 | 1616 | 8647 | 1963 | 4
5 | 1616 | 8647 | 1963 | 4
6 | 47402 | 8240 | 2053 | 18
```
La requête suivante est une jointure interne de deux sous-requêtes de la clause FROM. La requête recherche le nombre de billets vendus et invendus pour les différentes catégories d’événements (concerts et spectacles). Les sous-requêtes de la clause FROM sont des sous-requêtes de *table* ; elles peuvent renvoyer plusieurs lignes et colonnes.
```
select catgroup1, sold, unsold
from
(select catgroup, sum(qtysold) as sold
from category c, event e, sales s
where c.catid = e.catid and e.eventid = s.eventid
group by catgroup) as a(catgroup1, sold)
join
(select catgroup, sum(numtickets)-sum(qtysold) as unsold
from category c, event e, sales s, listing l
where c.catid = e.catid and e.eventid = s.eventid
and s.listid = l.listid
group by catgroup) as b(catgroup2, unsold)
on a.catgroup1 = b.catgroup2
order by 1;
catgroup1 | sold | unsold
----------+--------+--------
Concerts | 195444 |1067199
Shows | 149905 | 817736
```
# Exemples de UNNEST
UNNEST est un paramètre de la clause FROM qui étend les données imbriquées en colonnes contenant les éléments désimbriqués des données. Pour plus d’informations sur la désimbrication des données, consultez [Interrogation de données semi-structurées](query-super.md).
L'instruction suivante crée et remplit le `orders` tableau, qui contient une `products` colonne contenant des tableaux de produits. IDs Les exemples de cette section utilisent les exemples de données de cette table.
```
CREATE TABLE orders (
order_id INT,
products SUPER
);
-- Populate table
INSERT INTO orders VALUES
(1001, JSON_PARSE('[
{
"product_id": "P456",
"name": "Monitor",
"price": 299.99,
"quantity": 1,
"specs": {
"size": "27 inch",
"resolution": "4K"
}
}
]
')),
(1002, JSON_PARSE('
[
{
"product_id": "P567",
"name": "USB Cable",
"price": 9.99,
"quantity": 3
},
{
"product_id": "P678",
"name": "Headphones",
"price": 159.99,
"quantity": 1,
"specs": {
"type": "Wireless",
"battery_life": "20 hours"
}
}
]
'));
```
Voici quelques exemples de requêtes de désimbrication avec les exemples de données à l’aide de la syntaxe PartiQL.
## Désimbrication d’un tableau sans colonne OFFSET
La requête suivante désintègre les tableaux SUPER dans la colonne des produits, chaque ligne représentant un article de la commande dans `order_id`.
```
SELECT o.order_id, unnested_products.product
FROM orders o, UNNEST(o.products) AS unnested_products(product);
order_id | product
----------+-----------------------------------------------------------------------------------------------------------------------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3}
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(3 rows)
```
La requête suivante recherche le produit le plus cher de chaque commande.
```
SELECT o.order_id, MAX(unnested_products.product)
FROM orders o, UNNEST(o.products) AS unnested_products(product);
order_id | product
----------+-----------------------------------------------------------------------------------------------------------------------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(2 rows)
```
## Désimbrication d’un tableau avec une colonne OFFSET implicite
La requête suivante utilise le paramètre `UNNEST ... WITH OFFSET` pour afficher la position de base zéro de chaque produit dans son tableau de commandes.
```
SELECT o.order_id, up.product, up.offset_col
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product);
order_id | product | offset_col
----------+-----------------------------------------------------------------------------------------------------------------------------+------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1
(3 rows)
```
Étant donné que l’instruction ne spécifie pas d’alias pour la colonne offset, Amazon Redshift la nomme `offset_col` par défaut.
## Désimbrication d’un tableau avec une colonne OFFSET explicite
La requête suivante utilise également le paramètre `UNNEST ... WITH OFFSET` pour afficher les produits dans leurs tableaux de commandes. La différence entre cette requête et celle de l’exemple précédent réside dans le fait qu’elle nomme explicitement la colonne de décalage avec l’alias `idx`.
```
SELECT o.order_id, up.product, up.idx
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx);
order_id | product | idx
----------+-----------------------------------------------------------------------------------------------------------------------------+-----
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1
(3 rows)
```
# Clause WHERE
La clause WHERE contient les conditions qui joignent les tables ou appliquent les prédicats aux colonnes des tables. Les tables peuvent être à jointure interne en utilisant la syntaxe appropriée dans la clause WHERE ou FROM. Les critères de jointure externe doivent être spécifiés dans la clause FROM.
## Syntaxe
```
[ WHERE condition ]
```
## *condition*
Toute condition avec un résultat Boolean, comme une condition de jointure ou un prédicat sur une colonne de table. Les exemples suivants sont des conditions de jointure valides :
```
sales.listid=listing.listid
sales.listid<>listing.listid
```
Les exemples suivants sont des conditions valides sur les colonnes des tables :
```
catgroup like 'S%'
venueseats between 20000 and 50000
eventname in('Jersey Boys','Spamalot')
year=2008
length(catdesc)>25
date_part(month, caldate)=6
```
Les conditions peuvent être simples ou complexes ; pour les conditions complexes, vous pouvez utiliser des parenthèses afin d’isoler des unités logiques. Dans l’exemple suivant, la condition de jointure est placée entre parenthèses.
```
where (category.catid=event.catid) and category.catid in(6,7,8)
```
## Notes d’utilisation
Vous pouvez utiliser des alias dans la clause WHERE pour référencer les expressions de liste de sélection.
Vous ne pouvez pas limiter les résultats des fonctions d’agrégation dans la clause WHERE ; utilisez à cette fin la clause HAVING.
Les colonnes qui sont limités dans la clause WHERE doivent provenir de références de table de la clause FROM.
## Exemple
La requête suivante utilise une combinaison de différentes restrictions de clause WHERE, y compris une condition de jointure pour les tables SALES et EVENT, un prédicat sur la colonne EVENTNAME et deux prédicats sur la colonne STARTTIME.
```
select eventname, starttime, pricepaid/qtysold as costperticket, qtysold
from sales, event
where sales.eventid = event.eventid
and eventname='Hannah Montana'
and date_part(quarter, starttime) in(1,2)
and date_part(year, starttime) = 2008
order by 3 desc, 4, 2, 1 limit 10;
eventname | starttime | costperticket | qtysold
----------------+---------------------+-------------------+---------
Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2
Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2
Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1
Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4
(10 rows)
```
# Jointures externes Oracle dans la clause WHERE
Pour des raisons de compatibilité Oracle, Amazon Redshift prend en charge l’opérateur de jointure externe Oracle (\$1) dans les conditions de clause WHERE. Cet opérateur doit être utilisé uniquement dans la définition de conditions de jointure externe ; n’essayez pas de l’utiliser dans d’autres contextes. Les autres utilisations de cet opérateur sont automatiquement ignorées dans la plupart des cas.
Une jointure externe renvoie toutes les lignes que la jointure interne équivalente renvoie, plus les lignes non correspondantes d’une ou de deux tables. Dans la clause FROM, vous pouvez spécifier des jointures externes gauches, droites et complètes. Dans la clause WHERE, vous ne pouvez spécifier que des jointures externes gauches et droites.
Pour les tables de jointure externes TABLE1 TABLE2 et pour renvoyer des lignes non correspondantes depuis TABLE1 (une jointure externe gauche), spécifiez-le `TABLE1 LEFT OUTER JOIN TABLE2` dans la clause FROM ou appliquez l'opérateur (\$1) à toutes les colonnes de jointure depuis TABLE2 la clause WHERE. Pour toutes les lignes TABLE1 qui ne contiennent aucune ligne correspondante TABLE2, le résultat de la requête contient des valeurs nulles pour toutes les expressions de liste de sélection contenant des colonnes provenant de TABLE2.
Pour produire le même comportement pour toutes les lignes TABLE2 qui ne contiennent aucune ligne correspondante TABLE1, spécifiez-le `TABLE1 RIGHT OUTER JOIN TABLE2` dans la clause FROM ou appliquez l'opérateur (\$1) à toutes les colonnes jointes depuis TABLE1 la clause WHERE.
## Syntaxe de base
```
[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}
```
La première condition est équivalente à :
```
from table1 left outer join table2
on table1.column1=table2.column1
```
La deuxième condition est équivalente à :
```
from table1 right outer join table2
on table1.column1=table2.column1
```
**Note**
La syntaxe présentée ici couvre le cas simple d’une équijointure sur une paire de colonnes de jointure. Cependant, d’autres types de conditions de comparaison et plusieurs paires de colonnes de jointure sont également valides.
Par exemple, la clause WHERE suivante définit une jointure externe sur deux paires de colonnes. L’opérateur (\$1) doit être associé à la même table dans les deux conditions :
```
where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)
```
## Notes d’utilisation
Si possible, utilisez la syntaxe OUTER JOIN de la clause standard FROM au lieu de l’opérateur (\$1) dans la clause WHERE. Les requêtes qui contiennent l’opérateur (\$1) sont soumises aux règles suivantes :
+ Vous ne pouvez utiliser que l’opérateur (\$1) dans la clause WHERE, et uniquement en référence aux colonnes des tables ou des vues.
+ Vous ne pouvez pas appliquer l’opérateur (\$1) aux expressions. Cependant, une expression peut contenir des colonnes qui utilisent l’opérateur (\$1). Par exemple, la condition de jointure suivante renvoie une erreur de syntaxe :
```
event.eventid*10(+)=category.catid
```
Cependant, la condition de jointure suivante est valide :
```
event.eventid(+)*10=category.catid
```
+ Vous ne pouvez pas utiliser l’opérateur (\$1) dans un bloc de requête qui contient également la syntaxe de jointure de la clause FROM.
+ Si deux tables sont jointes sur plusieurs conditions de jointure, vous devez utiliser l’opérateur (\$1) dans la totalité de ces conditions ou dans aucune d’entre elles. Une jointure avec des styles de syntaxe mixtes est exécutée comme jointure interne, sans avertissement.
+ L’opérateur (\$1) ne génère pas une jointure externe si vous joignez une table de la requête externe à une table qui résulte d’une requête interne.
+ Pour utiliser l’opérateur (\$1) et créer une jointure externe d’une table avec elle-même, vous devez définir les alias de table dans la clause FROM et les référencer dans la condition de jointure :
```
select count(*)
from event a, event b
where a.eventid(+)=b.catid;
count
-------
8798
(1 row)
```
+ Vous ne pouvez pas associer une condition de jointure contenant l’opérateur (\$1) avec une condition OR ou une condition IN. Par exemple :
```
select count(*) from sales, listing
where sales.listid(+)=listing.listid or sales.salesid=0;
ERROR: Outer join operator (+) not allowed in operand of OR or IN.
```
+ Dans une clause WHERE qui crée une jointure externe avec plus de deux tables, l’opérateur (\$1) ne peut être appliqué qu’une seule fois à une table donnée. Dans l’exemple suivant, la table SALES ne peut pas être référencée par l’opérateur (\$1) dans deux jointures successives.
```
select count(*) from sales, listing, event
where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid;
ERROR: A table may be outer joined to at most one other table.
```
+ Si la condition de jointure externe de la clause WHERE compare une colonne à TABLE2 une constante, appliquez l'opérateur (\$1) à la colonne. Si vous n'incluez pas l'opérateur, les lignes jointes à l'extérieur de TABLE1, qui contiennent des valeurs nulles pour la colonne restreinte, sont éliminées. Consultez la section Exemples ci-dessous.
## Exemples
La requête de jointure suivante spécifie une jointure externe gauche des tables SALES et LISTING, sur leurs colonnes LISTID :
```
select count(*)
from sales, listing
where sales.listid = listing.listid(+);
count
--------
172456
(1 row)
```
La requête équivalente suivante produit le même résultat, mais utilise la syntaxe de jointure de la clause FROM :
```
select count(*)
from sales left outer join listing on sales.listid = listing.listid;
count
--------
172456
(1 row)
```
La table SALES ne contient pas d’enregistrements pour toutes les listes de la table LISTING, car certaines listes ne se traduisent pas par des ventes. La requête suivante crée une jointure externe de SALES et de LISTING, et renvoie les lignes de LISTING même lorsque la table SALES ne rapporte aucune vente pour un ID de liste donné. Les colonnes PRICE et COMM, dérivées de la table SALES, contiennent des valeurs null dans le jeu de résultats pour ces lignes sans correspondance.
```
select listing.listid, sum(pricepaid) as price,
sum(commission) as comm
from listing, sales
where sales.listid(+) = listing.listid and listing.listid between 1 and 5
group by 1 order by 1;
listid | price | comm
--------+--------+--------
1 | 728.00 | 109.20
2 | |
3 | |
4 | 76.00 | 11.40
5 | 525.00 | 78.75
(5 rows)
```
Notez que lorsque l’opérateur de jointure de la clause WHERE est utilisé, l’ordre des tables dans la clause FROM n’importe pas.
L’exemple d’une condition de jointure externe plus complexe dans la clause WHERE est celui où la condition se compose d’une comparaison entre deux Colonnes de la table *et* d’une comparaison avec une constante :
```
where category.catid=event.catid(+) and eventid(+)=796;
```
Notez que l’opérateur (\$1) est utilisé à deux emplacements : d’abord dans la comparaison d’égalité entre les tables et ensuite dans la condition de comparaison pour la colonne EVENTID. Le résultat de cette syntaxe est la conservation des lignes de jointure externe lors de l’évaluation de la restriction sur EVENTID. Si vous supprimez l’opérateur (\$1) de la restriction EVENTID, la requête traite cette restriction comme filtre, pas dans le cadre de la condition de jointure externe. A leur tour, les lignes de jointure externe qui contiennent des valeurs null pour EVENTID sont éliminées du jeu de résultats.
Voici une requête complète qui illustre ce comportement :
```
select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid(+)=796;
catname | catgroup | eventid
-----------+----------+---------
Classical | Concerts |
Jazz | Concerts |
MLB | Sports |
MLS | Sports |
Musicals | Shows | 796
NBA | Sports |
NFL | Sports |
NHL | Sports |
Opera | Shows |
Plays | Shows |
Pop | Concerts |
(11 rows)
```
La requête équivalente à l’aide de la syntaxe de la clause FROM est la suivante :
```
select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;
```
Si vous supprimez le deuxième opérateur (\$1) de la version de la clause WHERE de cette requête, elle renvoie uniquement 1 ligne (celle où `eventid=796`).
```
select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid=796;
catname | catgroup | eventid
-----------+----------+---------
Musicals | Shows | 796
(1 row)
```
# Clause GROUP BY
La clause GROUP BY identifie les colonnes de regroupement de la requête. Elle est utilisée pour regrouper les lignes d’une table qui ont les mêmes valeurs dans toutes les colonnes répertoriées. L’ordre dans lequel les colonnes sont répertoriées n’a pas d’importance. Le résultat est de combiner chaque ensemble de lignes ayant des valeurs communes en une seule ligne de groupe qui représente toutes les lignes du groupe. Utilisez GROUP BY pour éliminer la redondance dans la sortie et pour calculer les agrégats qui s’appliquent aux groupes. Les colonnes de regroupement doivent être déclarées lorsque la requête calcule les regroupements avec des fonctions standard telles que SUM, AVG et COUNT. Pour plus d’informations, consultez [Fonctions d’agrégation](c_Aggregate_Functions.md).
## Syntaxe
```
[ GROUP BY expression [, ...] | ALL | aggregation_extension ]
```
où *aggregation\$1extension* est l’une des valeurs suivantes :
```
GROUPING SETS ( () | aggregation_extension [, ...] ) |
ROLLUP ( expr [, ...] ) |
CUBE ( expr [, ...] )
```
## Parameters
*expression*
La liste des colonnes ou des expressions doit correspondre à la liste des expressions non agrégées de la liste de sélection de la requête. Par exemple, imaginons la requête simple suivante.
```
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by listid, eventid
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397 | 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
(5 rows)
```
Dans cette requête, la liste de sélection se compose de deux expressions d’agrégation. La première utilise la fonction SUM et la seconde la fonction COUNT. Les deux autres colonnes, LISTID et EVENTID, doivent être déclarées en tant que colonnes de regroupement.
Les expressions de la clause GROUP BY peuvent également faire référence à la liste de sélection en utilisant des nombres ordinaux. Par exemple, l’exemple précédent peut être abrégé comme suit.
```
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by 1,2
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397 | 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
(5 rows)
```
ALL
ALL indique de regrouper toutes les colonnes spécifiées dans la liste SELECT, à l’exception de celles qui sont agrégées. Par exemple, considérez la requête suivante qui regroupe `col1` et `col2` sans avoir à les spécifier individuellement dans la clause GROUP BY. La colonne `col3` est l’argument de la fonction `SUM` et n’est donc pas groupée.
```
SELECT col1, col2 sum(col3) FROM testtable GROUP BY ALL
```
Si vous EXCLUEZ une colonne de la liste SELECT, la clause GROUP BY ALL ne regroupe pas les résultats en fonction de cette colonne spécifique.
```
SELECT * EXCLUDE col3 FROM testtable GROUP BY ALL
```
* *aggregation\$1extension* *
Vous pouvez utiliser les extensions d’agrégation GROUPING SETS, ROLLUP et CUBE pour effectuer plusieurs opérations GROUP BY dans une seule instruction. Pour plus d’informations sur les extensions d’agrégation et les fonctions associées, consultez [Extensions de regroupement](r_GROUP_BY_aggregation-extensions.md).
## Exemples
Les exemples suivants utilisent la table SALES qui contient des colonnes : salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission et saletime. Pour plus d’informations sur la table SALES, consultez [Exemple de base de données](c_sampledb.md).
L’exemple suivant regroupe `salesid` et `listid` sans avoir à les spécifier individuellement dans la clause GROUP BY. La colonne `qtysold` est l’argument de la fonction `SUM` et n’est donc pas groupée.
```
SELECT salesid, listid, sum(qtysold) FROM sales GROUP BY ALL;
salesid | listid | sum
--------+---------+------
33095 | 36572 | 2
88268 | 100813 | 4
110917 | 127048 | 1
...
```
L’exemple de requête suivant exclut plusieurs colonnes de la liste SELECT. GROUP BY ALL ne regroupe donc que salesid et listid.
```
SELECT * EXCLUDE sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, saletime
FROM sales GROUP BY ALL;
salesid | listid
--------+---------
33095 | 36572
88268 | 100813
110917 | 127048
...
```
# Extensions de regroupement
Amazon Redshift prend en charge les extensions d’agrégation permettant d’effectuer plusieurs opérations GROUP BY dans une seule instruction.
Les exemples d’extensions d’agrégation utilisent la table `orders`, qui contient les données de vente d’une entreprise d’électronique. Pour créer des `orders`, procédez comme suit.
```
CREATE TABLE ORDERS (
ID INT,
PRODUCT CHAR(20),
CATEGORY CHAR(20),
PRE_OWNED CHAR(1),
COST DECIMAL
);
INSERT INTO ORDERS VALUES
(0, 'laptop', 'computers', 'T', 1000),
(1, 'smartphone', 'cellphones', 'T', 800),
(2, 'smartphone', 'cellphones', 'T', 810),
(3, 'laptop', 'computers', 'F', 1050),
(4, 'mouse', 'computers', 'F', 50);
```
## *GROUPING SETS*
Calcule un ou plusieurs jeux de regroupement dans une seule instruction. Un jeu de regroupement est l’ensemble d’une clause GROUP BY unique, un jeu de 0 colonne ou plus avec lequel vous pouvez regrouper le jeu de résultats d’une requête. GROUP BY GROUPING SETS revient à exécuter une requête UNION ALL sur un jeu de résultats groupé par différentes colonnes. Par exemple, GROUP BY GROUPING SETS((a), (b)) est équivalent à GROUP BY a UNION ALL GROUP BY b.
L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés par catégories de produits et type de produits vendus.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);
category | product | total
----------------------+----------------------+-------
computers | | 2100
cellphones | | 1610
| laptop | 2050
| smartphone | 1610
| mouse | 50
(5 rows)
```
## *ROLLUP*
Suppose une hiérarchie dans laquelle les colonnes précédentes sont considérées comme les parents des colonnes suivantes. ROLLUP regroupe les données par colonnes fournies et renvoie des lignes de sous-totaux supplémentaires représentant les totaux à tous les niveaux de colonnes de regroupement, en plus des lignes groupées. Par exemple, vous pouvez utiliser GROUP BY ROLLUP((a), (b)) pour renvoyer un jeu de résultats regroupé d’abord par a, puis par b en supposant que b est une sous-section de a. ROLLUP renvoie également une ligne contenant le jeu des résultats sans regrouper les colonnes.
GROUP BY ROLLUP((a), (b)) équivaut à GROUP BY GROUPING SETS((a,b), (a), ()).
L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés d’abord par catégorie, puis par produit, le produit étant une subdivision de la catégorie.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;
category | product | total
----------------------+----------------------+-------
cellphones | smartphone | 1610
cellphones | | 1610
computers | laptop | 2050
computers | mouse | 50
computers | | 2100
| | 3710
(6 rows)
```
## *CUBE*
Regroupe les données par colonnes fournies et renvoie des lignes de sous-totaux supplémentaires représentant les totaux à tous les niveaux de colonnes de regroupement, en plus des lignes groupées. CUBE renvoie les mêmes lignes que ROLLUP, mais ajoute des lignes de sous-total supplémentaires pour chaque combinaison de colonnes de regroupement non couverte par ROLLUP. Par exemple, vous pouvez utiliserGROUP BY CUBE ((a), (b)) pour renvoyer un jeu de résultats regroupé d’abord par a, puis par b en supposant que b est une sous-section de a, puis par b uniquement. CUBE renvoie également une ligne contenant le jeu des résultats sans regrouper les colonnes.
GROUP BY CUBE((a), (b)) équivaut à GROUP BY GROUPING SETS((a, b), (a), (b), ()).
L’exemple suivant renvoie le coût des produits de la table des commandes, regroupés d’abord par catégorie, puis par produit, le produit étant une subdivision de la catégorie. Contrairement à l’exemple précédent pour ROLLUP, l’instruction renvoie des résultats pour chaque combinaison de colonnes de regroupement.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;
category | product | total
----------------------+----------------------+-------
cellphones | smartphone | 1610
cellphones | | 1610
computers | laptop | 2050
computers | mouse | 50
computers | | 2100
| laptop | 2050
| mouse | 50
| smartphone | 1610
| | 3710
(9 rows)
```
## *Fonctions GROUPING/GROUPING\$1ID*
ROLLUP et CUBE ajoutent des valeurs NULL au jeu de résultats pour indiquer le sous-total des lignes. Par exemple, GROUP BY ROLLUP((a), (b)) renvoie une ou plusieurs lignes dont la valeur est NULL dans la colonne de regroupement b pour indiquer qu’il s’agit de sous-totaux des champs de la colonne de regroupement a. Ces valeurs NULL ne servent qu’à satisfaire le format des tuples renvoyés.
Lorsque vous exécutez des opérations GROUP BY avec ROLLUP et CUBE sur des relations qui stockent elles-mêmes des valeurs NULL, cela peut produire des jeux de résultats dont les lignes semblent avoir des colonnes de regroupement identiques. Pour revenir à l’exemple précédent, si la colonne de regroupement b contient une valeur NULL stockée, GROUP BY ROLLUP((a), (b)) renvoie une ligne avec une valeur NULL dans la colonne de regroupement b qui n’est pas un sous-total.
Pour faire la distinction entre les valeurs NULL créées par ROLLUP et CUBE et les valeurs NULL stockées dans les tables elles-mêmes, vous pouvez utiliser la fonction GROUPING ou son alias GROUPING\$1ID. GROUPING prend un seul jeu de regroupement comme argument et, pour chaque ligne du jeu de résultats, renvoie une valeur de 0 ou 1 bit correspondant à la colonne de regroupement dans cette position, puis convertit cette valeur en nombre entier. Si la valeur de cette position est une valeur NULL créée par une extension d’agrégation, GROUPING renvoie 1. Elle renvoie 0 pour toutes les autres valeurs, y compris les valeurs NULL stockées.
Par exemple, GROUPING(category, product) peut renvoyer les valeurs suivantes pour une ligne donnée, en fonction des valeurs des colonnes de regroupement pour cette ligne. Pour les besoins de cet exemple, toutes les valeurs NULL de la table sont des valeurs NULL créées par une extension d’agrégation.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/fr_fr/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html)
Les fonctions GROUPING apparaissent dans la partie liste SELECT de la requête au format suivant.
```
SELECT ... [GROUPING( expr )...] ...
GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
```
L’exemple suivant est identique à l’exemple précédent pour CUBE, mais avec l’ajout de fonctions GROUPING pour ses jeux de regroupement.
```
SELECT category, product,
GROUPING(category) as grouping0,
GROUPING(product) as grouping1,
GROUPING(category, product) as grouping2,
sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;
category | product | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
cellphones | smartphone | 0 | 0 | 0 | 1610
cellphones | | 0 | 1 | 1 | 1610
computers | laptop | 0 | 0 | 0 | 2050
computers | mouse | 0 | 0 | 0 | 50
computers | | 0 | 1 | 1 | 2100
| laptop | 1 | 0 | 2 | 2050
| mouse | 1 | 0 | 2 | 50
| smartphone | 1 | 0 | 2 | 1610
| | 1 | 1 | 3 | 3710
(9 rows)
```
## *ROLLUP et CUBE partiels*
Vous pouvez exécuter les opérations ROLLUP et CUBE avec une partie seulement des sous-totaux.
La syntaxe des opérations ROLLUP et CUBE partielles est la suivante.
```
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
```
Ici, la clause GROUP BY crée uniquement des lignes de sous-total au niveau de *expr2* et au-delà.
Les exemples suivants illustrent des opérations ROLLUP et CUBE partielles sur la table des commandes, en les regroupant d’abord par produit d’occasion ou non, puis en exécutant ROLLUP et CUBE dans les colonnes de catégorie et de produit.
```
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
F | | | 6 | 1100
T | | | 6 | 2610
(9 rows)
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
F | | laptop | 4 | 1050
F | | mouse | 4 | 50
T | | laptop | 4 | 1000
T | | smartphone | 4 | 1610
F | | | 6 | 1100
T | | | 6 | 2610
(13 rows)
```
Comme la colonne d’occasion n’est pas incluse dans les opérations ROLLUP et CUBE, aucune ligne du total général n’inclut toutes les autres lignes.
## *Concatenated grouping*
Vous pouvez concaténer plusieurs SETS/ROLLUP/CUBE clauses GROUPING pour calculer différents niveaux de sous-totaux. Les regroupements concaténés renvoient le produit cartésien des jeux de regroupement fournis.
La syntaxe pour concaténer les SETS/ROLLUP/CUBE clauses GROUPING est la suivante.
```
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
{ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
```
Examinez l’exemple suivant pour voir comment un petit regroupement concaténé peut produire un jeu de résultats final volumineux.
```
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
| cellphones | smartphone | 1 | 1610
| computers | laptop | 1 | 2050
| computers | mouse | 1 | 50
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
| cellphones | | 3 | 1610
| computers | | 3 | 2100
F | | laptop | 4 | 1050
F | | mouse | 4 | 50
T | | laptop | 4 | 1000
T | | smartphone | 4 | 1610
| | laptop | 5 | 2050
| | mouse | 5 | 50
| | smartphone | 5 | 1610
F | | | 6 | 1100
T | | | 6 | 2610
| | | 7 | 3710
(22 rows)
```
## *Nested grouping*
Vous pouvez utiliser les SETS/ROLLUP/CUBE opérations de regroupement en tant qu'*expr* de vos ENSEMBLES DE GROUPEMENT pour former un regroupement imbriqué. Le sous-regroupement au sein des GROUPING SETS imbriqués est aplati.
La syntaxe pour les regroupements imbriqués est la suivante.
```
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
```
Prenez l’exemple de code suivant.
```
SELECT category, product, pre_owned,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;
category | product | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
cellphones | | | 3 | 1610
computers | | | 3 | 2100
| laptop | F | 4 | 1050
| laptop | T | 4 | 1000
| mouse | F | 4 | 50
| smartphone | T | 4 | 1610
| laptop | | 5 | 2050
| mouse | | 5 | 50
| smartphone | | 5 | 1610
| | F | 6 | 1100
| | T | 6 | 2610
| | | 7 | 3710
| | | 7 | 3710
(13 rows)
```
Notez que, comme ROLLUP(category) et CUBE(product, pre\$1owned) contiennent tous deux le jeu de regroupement (), la ligne représentant le total général est dupliquée.
## *Notes d’utilisation*
+ La clause GROUP BY prend en charge jusqu’à 64 jeux de regroupement. Dans le cas de ROLLUP et CUBE, ou d’une combinaison de GROUPING SETS, ROLLUP et CUBE, cette limitation s’applique au nombre implicite de jeux de regroupement. Par exemple, GROUP BY CUBE((a), (b)) compte comme 4 jeux de regroupement, et non 2.
+ Vous ne pouvez pas utiliser de constantes pour regrouper des colonnes lorsque vous utilisez des extensions d’agrégation.
+ Vous ne pouvez pas créer un ensemble de regroupement qui contient des colonnes en double.
# Clause HAVING
La clause HAVING applique une condition à l’ensemble des résultats groupés intermédiaires que renvoie une requête.
## Syntaxe
```
[ HAVING condition ]
```
Par exemple, vous pouvez limiter les résultats d’une fonction SUM :
```
having sum(pricepaid) >10000
```
La condition HAVING est appliquée après que toutes les conditions de la clause WHERE ont été appliquées et que les opérations GROUP BY sont terminées.
La condition elle-même prend la même forme que celle de toute condition de clause WHERE.
## Notes d’utilisation
+ Toutes les colonnes référencées dans une condition de clause HAVING doivent être une colonne de regroupement ou une colonne qui fait référence au résultat d’une fonction d’agrégation.
+ Dans une clause HAVING, vous ne pouvez pas spécifier :
+ Un nombre ordinal qui fait référence à un élément de la liste de sélection. Seules les clauses GROUP BY et ORDER BY acceptent des nombres ordinaux.
## Exemples
La requête suivante calcule la vente totale de billets pour tous les événements selon leur nom, puis supprime les événements où le total des ventes est inférieur à 800 000 \$1 US. La condition HAVING est appliquée aux résultats de la fonction d’agrégation de la liste de sélection : `sum(pricepaid)`.
```
select eventname, sum(pricepaid)
from sales join event on sales.eventid = event.eventid
group by 1
having sum(pricepaid) > 800000
order by 2 desc, 1;
eventname | sum
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
```
La requête suivante calcule un ensemble de résultats similaire. Dans ce cas, toutefois, la condition HAVING est appliquée à un regroupement qui n’est pas spécifié dans la liste de sélection : `sum(qtysold)`. Les événements qui n’ont pas vendu plus de 2 000 billets disparaissent du résultat final.
```
select eventname, sum(pricepaid)
from sales join event on sales.eventid = event.eventid
group by 1
having sum(qtysold) >2000
order by 2 desc, 1;
eventname | sum
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
Chicago | 790993.00
Spamalot | 714307.00
```
La requête suivante calcule la vente totale de billets pour tous les événements selon leur nom, puis supprime les événements où le total des ventes est inférieur à 800 000 \$1 US. La condition HAVING est appliquée aux résultats de la fonction d’agrégation de la liste de sélection à l’aide de l’alias `pp` pour `sum(pricepaid)`.
```
select eventname, sum(pricepaid) as pp
from sales join event on sales.eventid = event.eventid
group by 1
having pp > 800000
order by 2 desc, 1;
eventname | pp
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
```
# Clause QUALIFY
La clause QUALIFY filtre les résultats d’une fonction de fenêtre précédemment calculée en fonction des conditions de recherche définies par l’utilisateur. Vous pouvez utiliser la clause pour appliquer des conditions de filtrage au résultat d’une fonction de fenêtre sans utiliser de sous-requête.
Elle est similaire à la [clause HAVING](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html), qui applique une condition à d’autres lignes de filtre à partir d’une clause WHERE. La différence entre QUALIFY et HAVING réside dans le fait que les résultats filtrés de la clause QUALIFY peuvent être basés sur le résultat de l’exécution de fonctions de fenêtre sur les données. Vous pouvez utiliser à la fois les clauses QUALIFY et HAVING dans une même requête.
## Syntaxe
```
QUALIFY condition
```
**Note**
Si vous utilisez la clause QUALIFY directement après la clause FROM, le nom de la relation FROM doit comporter un alias spécifié avant la clause QUALIFY.
## Exemples
Les exemples de cette section utilisent les exemples de données ci-dessous.
```
create table store_sales (ss_sold_date date, ss_sold_time time,
ss_item text, ss_sales_price float);
insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0),
('2022-01-01', '11:00:00', 'Product 2', 500.0),
('2022-01-01', '15:00:00', 'Product 3', 20.0),
('2022-01-01', '17:00:00', 'Product 4', 1000.0),
('2022-01-01', '18:00:00', 'Product 5', 30.0),
('2022-01-02', '10:00:00', 'Product 6', 5000.0),
('2022-01-02', '16:00:00', 'Product 7', 5.0);
```
L’exemple suivant montre comment trouver les deux articles les plus chers vendus après midi chaque jour.
```
SELECT *
FROM store_sales ss
WHERE ss_sold_time > time '12:00:00'
QUALIFY row_number()
OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2
ss_sold_date | ss_sold_time | ss_item | ss_sales_price
--------------+--------------+-----------+----------------
2022-01-01 | 17:00:00 | Product 4 | 1000
2022-01-01 | 18:00:00 | Product 5 | 30
2022-01-02 | 16:00:00 | Product 7 | 5
```
Vous pouvez ensuite retrouver le dernier article vendu chaque jour.
```
SELECT *
FROM store_sales ss
QUALIFY last_value(ss_item)
OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) = ss_item;
ss_sold_date | ss_sold_time | ss_item | ss_sales_price
--------------+--------------+-----------+----------------
2022-01-01 | 18:00:00 | Product 5 | 30
2022-01-02 | 16:00:00 | Product 7 | 5
```
L’exemple suivant renvoie les mêmes enregistrements que pour la requête précédente, le dernier article vendu chaque jour, mais n’utilise pas la clause QUALIFY.
```
SELECT * FROM (
SELECT *,
last_value(ss_item)
OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ss_last_item
FROM store_sales ss
)
WHERE ss_last_item = ss_item;
ss_sold_date | ss_sold_time | ss_item | ss_sales_price | ss_last_item
--------------+--------------+-----------+----------------+--------------
2022-01-02 | 16:00:00 | Product 7 | 5 | Product 7
2022-01-01 | 18:00:00 | Product 5 | 30 | Product 5
```
# UNION, INTERSECT et EXCEPT
**Topics**
+ [Syntaxe](#r_UNION-synopsis)
+ [Parameters](#r_UNION-parameters)
+ [Ordre d’évaluation des opérateurs ensemblistes](#r_UNION-order-of-evaluation-for-set-operators)
+ [Notes d’utilisation](#r_UNION-usage-notes)
+ [Exemple de requêtes UNION](c_example_union_query.md)
+ [Exemple de requête UNION ALL](c_example_unionall_query.md)
+ [Exemple de requêtes INTERSECT](c_example_intersect_query.md)
+ [Exemple de requête EXCEPT](c_Example_MINUS_query.md)
Les *opérateurs ensemblistes* UNION, INTERSECT et EXCEPT sont utilisés pour comparer et fusionner les résultats de deux expressions de requête distinctes. Par exemple, si vous voulez savoir quels utilisateurs d’un site web sont à la fois acheteurs et vendeurs, mais que leurs noms d’utilisateur sont stockés dans des colonnes ou tables distinctes, vous pouvez trouver l’*intersection* de ces deux types d’utilisateurs. Si vous voulez savoir quels utilisateurs du site web sont acheteurs mais pas vendeurs, vous pouvez utiliser l’opérateur EXCEPT pour trouver la *différence* entre les deux listes d’utilisateurs. Si vous souhaitez créer une liste de tous les utilisateurs, quel que soit le rôle, vous pouvez utiliser l’opérateur UNION.
## Syntaxe
```
query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
```
## Parameters
*query*
Expression de requête qui correspond, sous la forme de sa liste de sélection, à une deuxième expression de requête qui suit l’opérateur UNION, INTERSECT ou EXCEPT. Les deux expressions doivent comporter le même nombre de colonnes de sortie avec des types de données compatibles ; sinon, les deux jeux de résultats ne peuvent pas être comparés et fusionnés. Les opérations définies n’autorisent pas la conversion implicite entre différentes catégories de types de données ; pour plus d’informations, consultez [Compatibilité et conversion de types](c_Supported_data_types.md#r_Type_conversion).
Vous pouvez créer des requêtes qui contiennent un nombre illimité d’expressions de requête et les lier avec les opérateurs UNION, INTERSECT et EXCEPT dans n’importe quelle combinaison. Par exemple, la structure de requête suivante est valide, en supposant que les tables T1, T2 et T3 contiennent des ensembles de colonnes compatibles :
```
select * from t1
union
select * from t2
except
select * from t3
order by c1;
```
UNION
Opération de définition qui renvoie les lignes de deux expressions de requête, indépendamment de savoir si les lignes proviennent de l’une ou des deux expressions.
INTERSECT
Opération de définition qui renvoie les lignes provenant de deux expressions de requête. Les lignes qui ne sont pas retournées par les deux expressions sont ignorées.
EXCEPT \$1 MINUS
Opération de définition qui renvoie les lignes qui dérivent de l’une de deux expressions de requête. Pour être éligible pour le résultat, lignes doivent exister dans la première table de résultats, pas dans la deuxième. MINUS et EXCEPT sont des synonymes exacts.
ALL
Le mot-clé ALL conserve toutes les lignes en double produites par UNION. Le comportement par défaut lorsque le mot-clé ALL n’est pas utilisé consiste à ignorer ces doublons. INTERSECT ALL, EXCEPT ALL et MINUS ALL ne sont pas pris en charge.
## Ordre d’évaluation des opérateurs ensemblistes
Les opérateurs ensemblistes UNION et EXCEPT sont associatifs à gauche. Si les parenthèses ne sont pas spécifiées pour influer sur l’ordre de priorité, une combinaison de ces opérateurs ensemblistes est évaluée de gauche à droite. Par exemple, dans la requête suivante, l’UNION de T1 et de T2 est évaluée en premier, puis l’opération EXCEPT est effectuée sur le résultat UNION :
```
select * from t1
union
select * from t2
except
select * from t3
order by c1;
```
L’opérateur INTERSECT est prioritaire sur les opérateurs UNION et EXCEPT quand une combinaison d’opérateurs est utilisée dans la même requête. Par exemple, la requête suivante permet d’évaluer l’intersection de T2 et de T3, puis d’unir le résultat à T1 :
```
select * from t1
union
select * from t2
intersect
select * from t3
order by c1;
```
Par l’ajout de parenthèses, vous pouvez appliquer un ordre d’évaluation différent. Dans le cas suivant, le résultat de l’union de T1 et de T2 est croisé avec T3, et la requête est susceptible de produire un résultat différent.
```
(select * from t1
union
select * from t2)
intersect
(select * from t3)
order by c1;
```
## Notes d’utilisation
+ Les noms de colonne retournés dans le résultat d’une opération ensembliste sont les noms de colonne (ou alias) des tables de la première expression de requête. Comme ces noms de colonne sont potentiellement trompeurs, en ce sens que les valeurs de la colonne proviennent de tables de l’un ou de l’autre côté de l’opérateur ensembliste, il se peut que vous vouliez fournir des alias descriptifs pour le jeu de résultats.
+ Une expression de requête qui précède un opérateur ensembliste ne doit pas contenir une clause ORDER BY. Une clause ORDER BY ne produit des résultats triés significatifs que lorsqu’elle est utilisée à la fin d’une requête contenant des opérateurs ensemblistes. Dans ce cas, la clause ORDER BY s’applique aux résultats finaux de toutes les opérations ensemblistes. La requête la plus externe peut également contenir des clauses LIMIT et OFFSET standard.
+ Lorsque les requêtes avec opérateurs ensemblistes renvoient des résultats décimaux, les colonnes de résultats correspondantes sont promues pour renvoyer les mêmes précision et échelle. Par exemple, dans la requête suivante, où T1.REVENUE est une colonne DECIMAL(10,2) et T2.REVENUE une colonne DECIMAL(8,4), le résultat décimal est promu en DECIMAL(12,4) :
```
select t1.revenue union select t2.revenue;
```
L’échelle est `4`, parce que c’est l’échelle maximale des deux colonnes. La précision est `12` parce que T1.REVENUE nécessite 8 chiffres à gauche de la virgule (12-4 = 8). Cette promotion de type garantit que toutes les valeurs de chaque côté de l’UNION conviennent au résultat. Pour les valeurs 64 bits, la précision de résultat maximale est de 19 et l’échelle de résultat maximale de 18. Pour les valeurs 128 bits, la précision de résultat maximale est de 38 et l’échelle de résultat maximale de 37.
Si le type de données qui en résulte dépasse les limites Amazon Redshift de précision et d’échelle, la requête renvoie une erreur.
+ Pour les opérations ensemblistes, deux lignes sont traitées comme identiques si, pour chaque paire correspondante de colonnes, les deux valeurs de données sont *égales* ou *toutes deux NULL*. Par exemple, si les tables T1 et T2 contiennent une colonne et une ligne, et que la ligne a la valeur NULL dans les deux tables, une opération INTERSECT sur ces tables renvoie cette ligne.
# Exemple de requêtes UNION
Dans la requête UNION suivante, les lignes de la table SALES sont fusionnées avec les lignes de la table LISTING. Trois colonnes compatibles sont sélectionnées à partir de chaque table ; dans ce cas, les colonnes correspondantes ont les mêmes noms et types de données.
L’ensemble de résultats final est classé sur la première colonne de la table LISTING et limité aux 5 lignes avec la valeur LISTID la plus élevée.
```
select listid, sellerid, eventid from listing
union select listid, sellerid, eventid from sales
order by listid, sellerid, eventid desc limit 5;
listid | sellerid | eventid
--------+----------+---------
1 | 36861 | 7872
2 | 16002 | 4806
3 | 21461 | 4256
4 | 8117 | 4337
5 | 1616 | 8647
(5 rows)
```
L’exemple suivant montre comment vous pouvez ajouter une valeur littérale à la sortie d’une requête UNION afin que vous puissiez voir quelle expression de requête a généré chaque ligne du jeu de résultats. La requête identifie les lignes de la première expression de requête comme « B » (pour « buyers ») et les lignes de la deuxième expression de requête comme « S » (pour « sellers »).
La requête identifie les acheteurs et les vendeurs pour les transactions de billet égales ou supérieures à 10 000 \$1 US. La seule différence entre les deux expressions de requête de chaque côté de l’opérateur d’UNION est la colonne de jointure de la table SALES.
```
select listid, lastname, firstname, username,
pricepaid as price, 'S' as buyorsell
from sales, users
where sales.sellerid=users.userid
and pricepaid >=10000
union
select listid, lastname, firstname, username, pricepaid,
'B' as buyorsell
from sales, users
where sales.buyerid=users.userid
and pricepaid >=10000
order by 1, 2, 3, 4, 5;
listid | lastname | firstname | username | price | buyorsell
--------+----------+-----------+----------+-----------+-----------
209658 | Lamb | Colette | VOR15LYI | 10000.00 | B
209658 | West | Kato | ELU81XAA | 10000.00 | S
212395 | Greer | Harlan | GXO71KOC | 12624.00 | S
212395 | Perry | Cora | YWR73YNZ | 12624.00 | B
215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S
215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B
(6 rows)
```
L’exemple suivant utilise un opérateur UNION ALL, car les lignes dupliquées, s’il en existe, doivent être conservées dans le résultat. Pour une série d'événements spécifique IDs, la requête renvoie 0 ligne ou plus pour chaque vente associée à chaque événement, et 0 ou 1 ligne pour chaque annonce de cet événement. IDs Les événements sont propres à chaque ligne des tableaux LISTING et EVENT, mais il peut y avoir plusieurs ventes pour la même combinaison d'événement et d'annonce IDs dans le tableau SALES.
La troisième colonne du jeu de résultats identifie la source de la ligne. Si la source est la table SALES, un « Yes » apparaît dans la colonne SALESROW. (SALESROW est un alias de SALES. LISTID.) Si la ligne vient de la table LISTING, un « No » apparaît dans la colonne SALESROW.
Dans ce cas, le jeu de résultats se compose de trois lignes de vente pour affichage 500, événement 7787. En d’autres termes, trois transactions différentes ont eu lieu pour cette combinaison d’affichage et d’événement. Les deux autres listes, 501 et 502, n'ont généré aucune vente. La seule ligne produite par la requête pour ces listes IDs provient donc de la table LISTING (SALESROW = « Non »).
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union all
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
7787 | 500 | Yes
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(6 rows)
```
Si vous exécutez la même requête sans le mot-clé ALL, le résultat ne conserve qu’une seule des transactions de vente.
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(4 rows)
```
# Exemple de requête UNION ALL
L’exemple suivant utilise un opérateur UNION ALL, car les lignes dupliquées, s’il en existe, doivent être conservées dans le résultat. Pour une série d'événements spécifique IDs, la requête renvoie 0 ligne ou plus pour chaque vente associée à chaque événement, et 0 ou 1 ligne pour chaque annonce de cet événement. IDs Les événements sont propres à chaque ligne des tableaux LISTING et EVENT, mais il peut y avoir plusieurs ventes pour la même combinaison d'événement et d'annonce IDs dans le tableau SALES.
La troisième colonne du jeu de résultats identifie la source de la ligne. Si la source est la table SALES, un « Yes » apparaît dans la colonne SALESROW. (SALESROW est un alias de SALES. LISTID.) Si la ligne vient de la table LISTING, un « No » apparaît dans la colonne SALESROW.
Dans ce cas, le jeu de résultats se compose de trois lignes de vente pour affichage 500, événement 7787. En d’autres termes, trois transactions différentes ont eu lieu pour cette combinaison d’affichage et d’événement. Les deux autres listes, 501 et 502, n'ont généré aucune vente. La seule ligne produite par la requête pour ces listes IDs provient donc de la table LISTING (SALESROW = « Non »).
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union all
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
7787 | 500 | Yes
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(6 rows)
```
Si vous exécutez la même requête sans le mot-clé ALL, le résultat ne conserve qu’une seule des transactions de vente.
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(4 rows)
```
# Exemple de requêtes INTERSECT
Comparez l’exemple suivant avec le premier exemple UNION. La seule différence entre les deux exemples est l’opérateur ensembliste qui est utilisé, mais les résultats sont très différents. Seule une des lignes est la même :
```
235494 | 23875 | 8771
```
Il s’agit de la seule ligne du résultat limité de 5 lignes qui a été trouvé dans les deux tables.
```
select listid, sellerid, eventid from listing
intersect
select listid, sellerid, eventid from sales
order by listid desc, sellerid, eventid
limit 5;
listid | sellerid | eventid
--------+----------+---------
235494 | 23875 | 8771
235482 | 1067 | 2667
235479 | 1589 | 7303
235476 | 15550 | 793
235475 | 22306 | 7848
(5 rows)
```
La requête suivante détecte les événements (pour lesquels des billets ont été vendus) qui se sont déroulées dans des lieux de New York et de Los Angeles en mars. La différence entre les deux expressions de requête est la contrainte sur la colonne VENUECITY.
```
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='Los Angeles'
intersect
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='New York City'
order by eventname asc;
eventname
----------------------------
A Streetcar Named Desire
Dirty Dancing
Electra
Running with Annalise
Hairspray
Mary Poppins
November
Oliver!
Return To Forever
Rhinoceros
South Pacific
The 39 Steps
The Bacchae
The Caucasian Chalk Circle
The Country Girl
Wicked
Woyzeck
(16 rows)
```
# Exemple de requête EXCEPT
La table CATEGORY de la base de données TICKIT contient les 11 lignes suivantes :
```
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
5 | Sports | MLS | Major League Soccer
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
9 | Concerts | Pop | All rock and pop music concerts
10 | Concerts | Jazz | All jazz singers and bands
11 | Concerts | Classical | All symphony, concerto, and choir concerts
(11 rows)
```
Supposons qu’une table CATEGORY\$1STAGE (table intermédiaire) contienne une seule ligne supplémentaire :
```
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
5 | Sports | MLS | Major League Soccer
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
9 | Concerts | Pop | All rock and pop music concerts
10 | Concerts | Jazz | All jazz singers and bands
11 | Concerts | Classical | All symphony, concerto, and choir concerts
12 | Concerts | Comedy | All stand up comedy performances
(12 rows)
```
renvoiez la différence entre les deux tables. En d’autres termes, renvoiez les lignes qui sont dans la table CATEGORY\$1STAGE, mais pas dans la table CATEGORY :
```
select * from category_stage
except
select * from category;
catid | catgroup | catname | catdesc
-------+----------+---------+----------------------------------
12 | Concerts | Comedy | All stand up comedy performances
(1 row)
```
La requête équivalente suivante utilise le synonyme MINUS.
```
select * from category_stage
minus
select * from category;
catid | catgroup | catname | catdesc
-------+----------+---------+----------------------------------
12 | Concerts | Comedy | All stand up comedy performances
(1 row)
```
Si vous inversez l’ordre des expressions SELECT, la requête ne renvoie aucune ligne.
# Clause ORDER BY
**Topics**
+ [Syntaxe](#r_ORDER_BY_clause-synopsis)
+ [Parameters](#r_ORDER_BY_clause-parameters)
+ [Notes d’utilisation](#r_ORDER_BY_usage_notes)
+ [Exemples avec ORDER BY](r_Examples_with_ORDER_BY.md)
La clause ORDER BY trie le jeu de résultats d’une requête.
## Syntaxe
```
[ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
```
## Parameters
*expression*
Expression qui définit l’ordre de tri du jeu de résultats de la requête, généralement en spécifiant une ou plusieurs colonnes de la liste de sélection. Les résultats sont retournés en fonction du classement UTF-8 binaire. Vous pouvez aussi spécifier les éléments suivants :
+ Colonnes qui ne sont pas dans la liste de sélection
+ Expressions formées d’une ou de plusieurs colonnes qui existent dans les tables référencées par la requête
+ Nombres ordinaux qui représentent la position des entrées de la liste de sélection (ou position des colonnes de la table s’il n’existe aucune liste de sélection)
+ Alias qui définissent les entrées de la liste de sélection
Lorsque la clause ORDER BY contient plusieurs expressions régulières, le jeu de résultats est trié selon la première expression, puis la deuxième expression est appliquée aux lignes de la première expression ayant des valeurs correspondantes, et ainsi de suite.
ASC \$1 DESC
Option qui définit l’ordre de tri de l’expression, comme suit :
+ ASC : croissant (par exemple, de faible à élevé pour les valeurs numériques et de « A » à « Z » pour les chaînes de caractères). Si aucune option n’est spécifiée, les données sont triées dans l’ordre croissant par défaut.
+ DESC : descendantes (valeurs d’élevées à faibles pour les valeurs numériques ; de « Z » à « A » pour les chaînes).
NULLS FIRST \$1 NULLS LAST
Option qui spécifie si les valeurs NULL doivent être triées en premier, avant les valeurs non null, ou en dernier, après les valeurs non null. Par défaut, les valeurs NULL sont triées et classées en dernier par ordre croissant (ASC) et triées et classées en premier par ordre décroissant (DESC).
LIMIT *nombre* \$1 ALL
Option qui contrôle le nombre de lignes triées renvoyées par la requête. Le nombre LIMIT doit être un nombre entier positif ; la valeur maximale est `2147483647`.
LIMIT 0 ne renvoie aucune ligne. Vous pouvez utiliser cette syntaxe à des fins de test, pour vérifier qu’une requête s’exécute (sans afficher aucune ligne) ou pour renvoyer une liste de colonnes d’une table. Une clause ORDER BY est redondante si vous utilisez LIMIT 0 pour renvoyer une liste de colonnes. La valeur par défaut est LIMIT ALL.
OFFSET *début*
Option qui spécifie d’ignorer le nombre de lignes qui précèdent *début* avant de commencer à renvoyer les lignes. Le nombre OFFSET doit être un nombre entier positif ; la valeur maximale est `2147483647`. Lorsqu’elles sont utilisées avec l’option LIMIT, les lignes OFFSET sont ignorées avant de commencer à compter les lignes LIMIT qui sont retournées. Si l’option LIMIT n’est pas utilisée, le nombre de lignes du jeu de résultats est diminué du nombre de lignes qui sont ignorées. Comme les lignes ignorées par une clause OFFSET continuent de devoir être analysées, il peut être inefficace de choisir une valeur OFFSET élevée.
## Notes d’utilisation
Notez le comportement attendu suivant avec les clauses ORDER BY :
+ Les valeurs NULL sont considérées comme « plus élevés » que toutes les autres valeurs. Avec l’ordre de tri croissant par défaut, les valeurs NULL sont triées à la fin. Pour modifier ce comportement, utilisez l’option NULLS FIRST.
+ Lorsqu’une requête ne contient pas une clause ORDER BY, le système renvoie des jeux de résultats sans classement prévisible des lignes. La même requête exécutée deux fois peut renvoyer le même jeu de résultats dans un ordre différent.
+ Les options LIMIT et OFFSET peuvent être utilisées sans clause ORDER BY ; cependant, pour renvoyer un ensemble cohérent de lignes, utilisez ces options conjointement à ORDER BY.
+ Dans tout système parallèle comme Amazon Redshift, quand ORDER BY ne produit pas un classement unique, l’ordre des lignes est non déterministe. Autrement dit, si l’expression ORDER BY produit des valeurs en double, l’ordre de retour de ces lignes peut varier d’un système à un autre ou d’une exécution d’Amazon Redshift à une autre.
+ Amazon Redshift ne prend pas en charge les littéraux de chaîne dans les clauses ORDER BY.
# Exemples avec ORDER BY
renvoiez les 11 lignes de la table CATEGORY, triées sur la deuxième colonne, CATGROUP. Pour les résultats qui ont la même valeur CATGROUP, classez les valeurs de colonne CATDESC en fonction de la longueur de la chaîne de caractères. Triez ensuite sur les colonnes CATID et CATNAME.
```
select * from category order by 2, length(catdesc), 1, 3;
catid | catgroup | catname | catdesc
------+----------+-----------+----------------------------------------
10 | Concerts | Jazz | All jazz singers and bands
9 | Concerts | Pop | All rock and pop music concerts
11 | Concerts | Classical | All symphony, concerto, and choir conce
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
5 | Sports | MLS | Major League Soccer
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
(11 rows)
```
renvoiez les colonnes sélectionnées de la table SALES, triées selon les valeurs QTYSOLD les plus élevées. Limitez les résultats aux 10 lignes supérieures :
```
select salesid, qtysold, pricepaid, commission, saletime from sales
order by qtysold, pricepaid, commission, salesid, saletime desc
limit 10;
salesid | qtysold | pricepaid | commission | saletime
--------+---------+-----------+------------+---------------------
15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56
61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23
90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09
74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21
130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31
55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53
16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31
489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55
4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33
16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33
(10 rows)
```
renvoiez une liste de colonnes et aucune ligne à l’aide de la syntaxe LIMIT 0 :
```
select * from venue limit 0;
venueid | venuename | venuecity | venuestate | venueseats
---------+-----------+-----------+------------+------------
(0 rows)
```
# Clause CONNECT BY
La clause CONNECT BY spécifie la relation entre les lignes d’une hiérarchie. Vous pouvez utiliser CONNECT BY pour sélectionner des lignes dans un ordre hiérarchique en attachant la table à elle-même et en traitant les données hiérarchiques. Par exemple, vous pouvez l’utiliser pour parcourir de manière récursive un organigramme et répertorier des données.
Les requêtes hiérarchiques sont traitées dans l’ordre suivant :
1. Si la clause FROM comporte une jointure, elle est traitée en premier.
1. La clause CONNECT BY est évaluée.
1. La clause WHERE est évaluée.
## Syntaxe
```
[START WITH start_with_conditions]
CONNECT BY connect_by_conditions
```
**Note**
Bien que START et CONNECT ne soient pas des mots réservés, utilisez des identificateurs délimités (guillemets doubles) ou AS si vous utilisez START et CONNECT comme alias de table dans votre requête, afin d’éviter tout échec lors de l’exécution.
```
SELECT COUNT(*)
FROM Employee "start"
CONNECT BY PRIOR id = manager_id
START WITH name = 'John'
```
```
SELECT COUNT(*)
FROM Employee AS start
CONNECT BY PRIOR id = manager_id
START WITH name = 'John'
```
## Parameters
*start\$1with\$1conditions*
Conditions qui spécifient la ou les lignes racines de la hiérarchie
*connect\$1by\$1conditions*
Conditions qui spécifient la relation entre les lignes parents et les lignes enfants de la hiérarchie. Au moins une condition doit être qualifiée à l’aide de l’opérateur unaire ` ` utilisé pour faire référence à la ligne parent.
```
PRIOR column = expression
-- or
expression > PRIOR column
```
## Opérateurs
Vous pouvez utiliser les opérateurs suivants dans une requête CONNECT BY.
*LEVEL*
Pseudocolonne qui renvoie le niveau de ligne actuel dans la hiérarchie. Renvoie 1 pour la ligne racine, 2 pour l’enfant de la ligne racine, etc.
*PRIOR*
Opérateur unaire qui évalue l’expression pour la ligne parent de la ligne actuelle dans la hiérarchie.
## Exemples
Voici un exemple de requête CONNECT BY qui renvoie le nombre d’employés qui relèvent directement ou indirectement de John, avec 4 niveaux maximum.
```
SELECT id, name, manager_id
FROM employee
WHERE LEVEL < 4
START WITH name = 'John'
CONNECT BY PRIOR id = 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
```
Définition de table pour cet exemple :
```
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
```
Voici les lignes insérées dans la table.
```
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);
```
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)
# Exemples de sous-requête
Les exemples suivants illustrent différentes façons par lesquelles les sous-requêtes conviennent aux requêtes SELECT. Pour obtenir un autre exemple de l’utilisation des sous-requêtes, consultez [Exemples de clause JOIN](r_Join_examples.md).
## Sous-requête SELECT liste
L’exemple suivant contient une sous-requête dans la liste SELECT. Cette sous-requête est *scalaire* : elle renvoie une et une seule colonne et une seule valeur, ce qui est répété dans le résultat pour chaque ligne retournée à partir de la requête externe. La requête compare la valeur Q1SALES que la sous-requête calcule aux valeurs des ventes des deux autres trimestres (2 et 3) en 2008, comme défini par la requête externe.
```
select qtr, sum(pricepaid) as qtrsales,
(select sum(pricepaid)
from sales join date on sales.dateid=date.dateid
where qtr='1' and year=2008) as q1sales
from sales join date on sales.dateid=date.dateid
where qtr in('2','3') and year=2008
group by qtr
order by qtr;
qtr | qtrsales | q1sales
-------+-------------+-------------
2 | 30560050.00 | 24742065.00
3 | 31170237.00 | 24742065.00
(2 rows)
```
## Sous-requête de clause WHERE
L’exemple suivant contient une sous-requête de table dans la clause WHERE. Cette sous-requête produit plusieurs lignes. Dans ce cas, les lignes ne contiennent qu’une seule colonne, mais les sous-requêtes de table peuvent contenir plusieurs colonnes et lignes, tout comme n’importe quelle autre table.
La requête recherche les 10 meilleurs vendeurs en termes de nombre maximal de billets vendus. La liste des 10 meilleurs est limitée par la sous-requête, qui supprime les utilisateurs qui résident dans les villes où il y a des lieux de vente. Cette requête peut être écrite de différentes façons ; par exemple, la sous-requête peut être réécrite comme jointure au sein de la requête principale.
```
select firstname, lastname, city, max(qtysold) as maxsold
from users join sales on users.userid=sales.sellerid
where users.city not in(select venuecity from venue)
group by firstname, lastname, city
order by maxsold desc, city desc
limit 10;
firstname | lastname | city | maxsold
-----------+-----------+----------------+---------
Noah | Guerrero | Worcester | 8
Isadora | Moss | Winooski | 8
Kieran | Harrison | Westminster | 8
Heidi | Davis | Warwick | 8
Sara | Anthony | Waco | 8
Bree | Buck | Valdez | 8
Evangeline | Sampson | Trenton | 8
Kendall | Keith | Stillwater | 8
Bertha | Bishop | Stevens Point | 8
Patricia | Anderson | South Portland | 8
(10 rows)
```
## Sous-requêtes de clause WITH
Consultez [Clause WITH](r_WITH_clause.md).
# Sous-requêtes corrélées
L’exemple suivant contient une *sous-requête corrélée* dans la clause WHERE ; ce genre de sous-requête contient une ou plusieurs corrélations entre ses colonnes et les colonnes générés par la requête externe. Dans ce cas, la corrélation est `where s.listid=l.listid`. Pour chaque ligne que produit la requête externe, la sous-requête est exécutée pour qualifier ou disqualifier la ligne.
```
select salesid, listid, sum(pricepaid) from sales s
where qtysold=
(select max(numtickets) from listing l
where s.listid=l.listid)
group by 1,2
order by 1,2
limit 5;
salesid | listid | sum
--------+--------+----------
27 | 28 | 111.00
81 | 103 | 181.00
142 | 149 | 240.00
146 | 152 | 231.00
194 | 210 | 144.00
(5 rows)
```
## Modèles de sous-requêtes corrélées non pris en charge
Le planificateur de requête utilise une méthode de réécriture de requête appelée décorrélation de sous-requête afin d’optimiser plusieurs modèles de sous-requêtes corrélées en vue de l’exécution dans un environnement MPP. Quelques types de sous-requêtes corrélées suivent des modèles qu’Amazon Redshift ne peut pas décorréler et ne prend pas en charge. Les requêtes qui contiennent les références de corrélation suivantes génèrent des erreurs :
+ Les références de corrélation qui ignorent un bloc de requête, également appelées « références de corrélation de niveau non hiérarchique ». Par exemple, dans la requête suivante, le bloc contenant la référence de corrélation et le bloc ignoré sont connectés par un prédicat NOT EXISTS :
```
select event.eventname from event
where not exists
(select * from listing
where not exists
(select * from sales where event.eventid=sales.eventid));
```
Le bloc ignoré dans ce cas est la sous-requête sur la table LISTING. La référence de corrélation correspond aux tables EVENT et SALES.
+ Références de corrélation à partir d’une sous-requête qui fait partie d’une clause ON dans une requête externe :
```
select * from category
left join event
on category.catid=event.catid and eventid =
(select max(eventid) from sales where sales.eventid=event.eventid);
```
La clause ON contient une référence de corrélation depuis SALES dans la sous-requête jusqu’à EVENT dans la requête externe.
+ Références de corrélation sensibles à null à une table système Amazon Redshift. Par exemple :
```
select attrelid
from stv_locks sl, pg_attribute
where sl.table_id=pg_attribute.attrelid and 1 not in
(select 1 from pg_opclass where sl.lock_owner = opcowner);
```
+ Références de corrélation à partir d’une sous-requête contenant une fonction de fenêtrage.
```
select listid, qtysold
from sales s
where qtysold not in
(select sum(numtickets) over() from listing l where s.listid=l.listid);
```
+ Références d’une colonne GROUP BY aux résultats d’une sous-requête corrélée. Par exemple :
```
select listing.listid,
(select count (sales.listid) from sales where sales.listid=listing.listid) as list
from listing
group by list, listing.listid;
```
+ Références de corrélation à partir d’une sous-requête avec fonction d’agrégation et d’une clause GROUP BY, connectée à la requête externe par un prédicat IN. (Cette restriction ne s’applique pas aux fonctions d’agrégation MIN et MAX.) Par exemple :
```
select * from listing where listid in
(select sum(qtysold)
from sales
where numtickets>4
group by salesid);
```