Récapitulatif de la syntaxe de la fonction de fenêtrage Ordonnancement unique des données pour les fonctions de fenêtrage Fonctions prises en charge Exemple de tableau contenant des exemples de fonctions de fenêtrage

Fonctions de fenêtrage

En utilisant les fonctions de fenêtrage, vous pouvez créer des requêtes d’analyse commerciale plus efficacement. Les fonctions de fenêtrage fonctionnent sur une partition ou « fenêtre » d’un ensemble de résultats et renvoient une valeur pour chaque ligne de cette fenêtre. En revanche, les fonctions non fenêtrées effectuent leurs calculs sur chaque ligne du jeu de résultats. Contrairement aux fonctions de groupe qui regroupent les lignes de résultats, les fonctions de fenêtrage conservent toutes les lignes de l’expression de table.

Les valeurs renvoyées sont calculées en utilisant les valeurs des ensembles de lignes de cette fenêtre. Pour chaque ligne de la table, la fenêtre définit un ensemble de lignes qui est utilisé pour calculer des attributs supplémentaires. Une fenêtre est définie à l'aide d'une spécification de fenêtre (la OVER clause) et repose sur trois concepts principaux :

Partitionnement de fenêtres, qui forme des groupes de lignes (PARTITIONclause)
Ordre des fenêtres, qui définit un ordre ou une séquence de lignes au sein de chaque partition (clause ORDER BY)
Cadres de fenêtre, définis par rapport à chaque ligne afin de restreindre davantage l'ensemble de lignes (ROWSspécification)

Les fonctions de fenêtre sont le dernier ensemble d'opérations effectuées dans une requête, à l'exception de la clause ORDER BY finale. Toutes les jointures et toutes les clausesWHERE, GROUP BY et toutes les HAVING clauses sont terminées avant que les fonctions de fenêtre ne soient traitées. Par conséquent, les fonctions de fenêtre ne peuvent apparaître que dans la liste de sélection ou dans la clause ORDER BY. Vous pouvez utiliser plusieurs fonctions de fenêtrage dans une seule requête avec différentes clauses de cadre. Vous pouvez également utiliser les fonctions de fenêtre dans d'autres expressions scalaires, telles queCASE.

Les fonctions de fenêtre ne peuvent pas être imbriquées. Par exemple, une fonction d'agrégation SUM peut apparaître dans une fonction de fenêtreSUM, mais une fonction de fenêtre ne SUM peut pas apparaître dans une autre fonction de fenêtreSUM. Ce qui suit n'est pas pris en charge car une fonction de fenêtre est imbriquée dans une autre fonction de fenêtre.


SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

Récapitulatif de la syntaxe de la fonction de fenêtrage

Les fonctions de fenêtre suivent la syntaxe standard suivante.


function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )

Ici, function est l’une des fonctions décrites dans cette section.

L’expr_list se présente comme suit.


expression | column_name [, expr_list ]

L’order_list se présente comme suit.


expression | column_name [ ASC | DESC ] 
[ NULLS FIRST | NULLS LAST ]
[, order_list ]

La frame_clause se présente comme suit.


ROWS
{ UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } |

{ BETWEEN
{ UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW}
AND
{ UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}

Arguments

fonction

La fonction de fenêtrage. Pour plus d’informations, consultez les descriptions de chaque fonction.

OVER

La clause qui définit la spécification du fenêtrage. La OVER clause est obligatoire pour les fonctions de fenêtre et différencie les fonctions de fenêtre des autres SQL fonctions.

PARTITIONPAR expr_list

(Facultatif) La clause PARTITION BY subdivise le jeu de résultats en partitions, un peu comme la clause GROUP BY. Si une clause de partition est présente, la fonction est calculée pour les lignes de chaque partition. Si aucune clause de partition n’est spécifiée, une seule partition contient la totalité de la table et la fonction est calculée pour cette table complète.

Les fonctions de classement DENSE _ RANKNTILE,RANK, et ROW _ NUMBER nécessitent une comparaison globale de toutes les lignes du jeu de résultats. Lorsqu'une clause PARTITION BY est utilisée, l'optimiseur de requêtes peut exécuter chaque agrégation en parallèle en répartissant la charge de travail sur plusieurs tranches en fonction des partitions. Si la clause PARTITION BY n'est pas présente, l'étape d'agrégation doit être exécutée en série sur une seule tranche, ce qui peut avoir un impact négatif significatif sur les performances, en particulier pour les clusters de grande taille.

Amazon Redshift ne prend pas en charge les littéraux de chaîne dans PARTITION les clauses BY.

ORDERPAR order_list

(Facultatif) La fonction de fenêtre est appliquée aux lignes de chaque partition triées conformément à la spécification d'ordre dans ORDER BY. Cette clause ORDER BY est distincte et totalement indépendante des clauses ORDER BY de la frame_clause. La clause ORDER BY peut être utilisée sans la clause PARTITION BY.

Pour les fonctions de classement, la clause ORDER BY identifie les mesures des valeurs de classement. Pour les fonctions d’agrégation, les lignes partitionnées doivent être ordonnées avant que la fonction d’agrégation soit calculée pour chaque cadre. Pour en savoir plus sur les types de fonction de fenêtrage, consultez Fonctions de fenêtrage.

Les identificateurs de colonnes ou les expressions qui correspondent aux identificateurs de colonnes sont requis dans la liste d’ordre. Ni les constantes, ni les expressions constantes ne peuvent être utilisées pour remplacer les noms de colonnes.

NULLSles valeurs sont traitées comme leur propre groupe, triées et classées selon l'NULLSLASToption NULLS FIRST ou. Par défaut, NULL les valeurs sont triées et classées en dernier dans l'ASCordre, puis triées et classées en premier dans l'DESCordre.

Amazon Redshift ne prend pas en charge les littéraux de chaîne dans ORDER les clauses BY.

Si la clause ORDER BY est omise, l'ordre des lignes n'est pas déterministe.

Note

Dans tout système parallèle tel qu'Amazon Redshift, lorsqu'une clause ORDER BY ne produit pas un ordre unique et total des données, l'ordre des lignes n'est pas déterministe. En d'autres termes, si l'expression ORDER BY produit des valeurs dupliquées (ordre partiel), l'ordre de retour de ces lignes peut varier d'une exécution d'Amazon Redshift à l'autre. De leur côté, les fonctions de fenêtrage peuvent renvoyer des résultats inattendus ou incohérents. Pour plus d'informations, consultez Ordonnancement unique des données pour les fonctions de fenêtrage.

column_name

Nom d’une colonne à partitionner ou à ordonner.

ASC | DESC

Option qui définit l’ordre de tri de l’expression, comme suit :

ASC: croissant (par exemple, du plus bas au plus haut 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: décroissant (de haut en bas pour les valeurs numériques ; de « Z » à « A » pour les chaînes).

NULLS FIRST | NULLS LAST

Option qui indique si elle NULLS doit être ordonnée en premier, avant les valeurs non nulles, ou en dernier, après les valeurs non nulles. Par défaut, NULLS sont triés et classés en dernier dans l'ASCordre, et triés et classés en premier dans l'DESCordre.

frame_clause

Pour les fonctions d'agrégation, la clause frame affine davantage l'ensemble de lignes dans la fenêtre d'une fonction lors de l'utilisation de ORDER BY. Elle vous permet d’inclure ou d’exclure des ensembles de lignes dans le résultat ordonné. La clause frame comprend le ROWS mot-clé et les spécificateurs associés.

La clause frame ne s’applique pas aux fonctions de classement. De plus, la clause frame n'est pas obligatoire lorsqu'aucune clause ORDER BY n'est utilisée dans la OVER clause pour une fonction d'agrégation. Si une clause ORDER BY est utilisée pour une fonction d'agrégation, une clause de cadre explicite est requise.

Lorsqu'aucune clause ORDER BY n'est spécifiée, le cadre implicite est illimité, équivalent à. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ROWS

Cette clause définit le cadre de fenêtrage en spécifiant un décalage physique de la ligne actuelle.

Cette clause spécifie les lignes de la fenêtre ou de la partition actuelle auxquelles la valeur de la ligne actuelle doit être associée. Elle utilise des arguments qui spécifient la position de la ligne, qui peut être avant ou après la ligne actuelle. Le point de référence de tous les cadres de fenêtrage est la ligne actuelle. Chaque ligne devient la ligne actuelle à son tour à mesure que le cadre de fenêtrage avance dans la partition.

Le cadre peut être un simple ensemble de lignes allant jusqu’à et incluant la ligne actuelle.


{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}

Ou il peut s’agir d’un ensemble de lignes situées entre les deux limites.


BETWEEN
{ UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }
AND
{ UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }

UNBOUNDEDPRECEDINGindique que la fenêtre commence sur la première ligne de la partition ; le décalage PRECEDING indique que la fenêtre commence sur un nombre de lignes équivalent à la valeur du décalage avant la ligne en cours. UNBOUNDEDPRECEDINGest la valeur par défaut.

CURRENTROWindique que la fenêtre commence ou se termine à la ligne en cours.

UNBOUNDEDFOLLOWINGindique que la fenêtre se termine à la dernière ligne de la partition ; le décalage FOLLOWING indique que la fenêtre termine un nombre de lignes équivalent à la valeur du décalage après la ligne en cours.

offset identifie un nombre physique de lignes avant ou après la ligne actuelle. Dans ce cas, offset doit être une constante ayant une valeur numérique positive. Par exemple, 5 FOLLOWING termine le cadre cinq lignes après la ligne actuelle.

Lorsque BETWEEN ce n'est pas spécifié, le cadre est implicitement délimité par la ligne en cours. Par exemple, ROWS 5 PRECEDING est égal à ROWS BETWEEN 5 PRECEDING AND CURRENT ROW. En outre, ROWS UNBOUNDED FOLLOWING est égal à ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.

Note

Vous ne pouvez pas spécifier un cadre dans lequel la limite de début est supérieure à la limite de fin. Par exemple, vous ne pouvez pas spécifier l’un des cadres suivants.


between 5 following and 5 preceding
between current row and 2 preceding
between 3 following and current row

Ordonnancement unique des données pour les fonctions de fenêtrage

Si une clause ORDER BY pour une fonction de fenêtre ne produit pas un ordre unique et total des données, l'ordre des lignes n'est pas déterministe. Si l'expression ORDER BY produit des valeurs dupliquées (ordre partiel), l'ordre de retour de ces lignes peut varier au cours de plusieurs exécutions. Dans ce cas, les fonctions de fenêtrage peuvent également renvoyer des résultats inattendus ou incohérents.

Par exemple, la requête suivante renvoie des résultats différents sur plusieurs exécutions. Ces différents résultats sont dus au order by dateid fait que cela ne produit pas un ordre unique des données pour la fonction de SUM fenêtre.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |   1730.00 |     1730.00
1827 |    708.00 |     2438.00
1827 |    234.00 |     2672.00
...

select dateid, pricepaid,
sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid |   sumpaid
--------+-----------+-------------
1827 |    234.00 |      234.00
1827 |    472.00 |      706.00
1827 |    347.00 |     1053.00
...

Dans ce cas, l'ajout d'une deuxième colonne ORDER BY à la fonction de fenêtre peut résoudre le problème.


select dateid, pricepaid,
sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid
from sales
group by dateid, pricepaid;

dateid | pricepaid | sumpaid
--------+-----------+---------
1827 |    234.00 |  234.00
1827 |    337.00 |  571.00
1827 |    347.00 |  918.00
...

Fonctions prises en charge

Amazon Redshift prend en charge deux types de fonctions de fenêtrage : par regroupement et par rang.

Vous trouverez ci-dessous les fonctions d’agrégation prises en charge :

AVGfonction de fenêtre
COUNTfonction de fenêtre
CUME_ fonction DIST de fenêtre
DENSE_ fonction RANK de fenêtre
FIRST_ fonction VALUE de fenêtre
LAGfonction de fenêtre
LAST_ fonction VALUE de fenêtre
LEADfonction de fenêtre
LISTAGGfonction de fenêtre
MAXfonction de fenêtre
MEDIANfonction de fenêtre
MINfonction de fenêtre
NTH_ fonction VALUE de fenêtre
PERCENTILE_ fonction CONT de fenêtre
PERCENTILE_ fonction DISC de fenêtre
RATIOfonction de fenêtre _TO_ REPORT
STDDEVfonctions SAMP de POP fenêtre STDDEV _ et _(STDDEV_ SAMP et STDDEV sont des synonymes)
SUMfonction de fenêtre
VARfonctions SAMP de POP fenêtre VAR _ et _(VAR_ SAMP et VARIANCE sont des synonymes)

Vous trouverez ci-dessous les fonctions de classement prises en charge :

Exemple de tableau contenant des exemples de fonctions de fenêtrage

Vous trouverez des exemples de fonctions de fenêtrage spécifiques avec la description de chaque fonction. Certains exemples utilisent une table nomméeWINSALES, qui contient 11 lignes, comme indiqué ci-dessous.

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
30001	8/2/2003	3	B	10	10
10001	12/24/2003	1	C	10	10
10005	12/24/2003	1	A	30
40001	1/9/2004	4	A	40
10006	1/18/2004	1	C	10
20001	2/12/2004	2	B	20	20
40005	2/12/2004	4	A	10	10
20002	2/16/2004	2	C	20	20
30003	4/18/2004	3	B	15
30004	4/18/2004	3	B	20
30007	9/7/2004	3	C	30

Le script suivant crée et remplit le WINSALES tableau d'exemple.


CREATE TABLE winsales(
  salesid int,
  dateid date,
  sellerid int,
  buyerid char(10),
  qty int,
  qty_shipped int);

INSERT INTO winsales VALUES
  (30001, '8/2/2003', 3, 'b', 10, 10),
  (10001, '12/24/2003', 1, 'c', 10, 10),
  (10005, '12/24/2003', 1, 'a', 30, null),	
  (40001, '1/9/2004', 4, 'a', 40, null),	
  (10006, '1/18/2004', 1, 'c', 10, null),	
  (20001, '2/12/2004', 2, 'b', 20, 20),
  (40005, '2/12/2004', 4, 'a', 10, 10),
  (20002, '2/16/2004', 2, 'c', 20, 20),
  (30003, '4/18/2004', 3, 'b', 15, null),
  (30004, '4/18/2004', 3, 'b', 20, null),	
  (30007, '9/7/2004', 3, 'c', 30, null);

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

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

Conventions de rédaction

TO_VARBYTE

AVG