Une fonction de fenêtrage effectue un calcul sur un jeu d'enregistrements liés d'une certaine façon à l'enregistrement courant. On peut les rapprocher des calculs réalisables par une fonction d'agrégat. Cependant, les fonctions de fenêtrage n'entraînent pas le regroupement des enregistrements traités en un seul, comme le ferait l'appel à une fonction d'agrégation standard. À la place, chaque enregistrement garde son identité propre. En coulisse, la fonction de fenêtrage est capable d'accéder à d'autres enregistrements que l'enregistrement courant du résultat de la requête.
Voici un exemple permettant de comparer le salaire d'un employé avec le salaire moyen de sa division :
SELECT nomdep, noemp, salaire, avg(salaire) OVER (PARTITION BY nomdep) FROM salaireemp;
nomdep | noemp | salaire | avg -----------+-------+---------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 ventes | 3 | 4800 | 4866.6666666666666667 ventes | 1 | 5000 | 4866.6666666666666667 ventes | 4 | 4800 | 4866.6666666666666667 (10 rows)
Les trois premières colonnes viennent directement de la table
salaireemp
, et il y a une ligne de sortie pour
chaque ligne de la table. La quatrième colonne représente une moyenne
calculée sur tous les enregistrements de la table qui ont la même valeur de
nomdep
que la ligne courante. (Il s'agit
effectivement de la même fonction que la fonction d'agrégat classique
avg
, mais la clause OVER
entraîne
son exécution en tant que fonction de fenêtrage et son calcul sur la
fenêtre.)
Un appel à une fonction de fenêtrage contient toujours une clause
OVER
qui suit immédiatement le nom et les arguments de
la fonction. C'est ce qui permet de la distinguer syntaxiquement d'une
fonction simple ou d'une fonction d'agrégat. La clause
OVER
détermine précisément comment les lignes de la
requête sont éclatées pour être traitées par la fonction de fenêtrage. La
clause PARTITION BY
contenue dans
OVER
divise les enregistrements en
groupes, ou partitions, qui partagent les mêmes valeurs pour la (les)
expression(s) contenue(s) dans la clause PARTITION BY
.
Pour chaque enregistrement, la fonction de fenêtrage est calculée sur les
enregistrements qui se retrouvent dans la même partition que
l'enregistrement courant.
Vous pouvez aussi contrôler l'ordre dans lequel les lignes sont traitées
par les fonctions de fenêtrage en utilisant la clause ORDER
BY
à l'intérieur de la clause OVER
(la
partition traitée par le ORDER BY
n'a de plus pas besoin
de correspondre à l'ordre dans lequel les lignes seront affichées). Voici
un exemple :
SELECT nomdep, noemp, salaire, rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC) FROM salaireemp;
nomdep | noemp | salaire| rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 ventes | 1 | 5000 | 1 ventes | 4 | 4800 | 2 ventes | 3 | 4800 | 2 (10 rows)
On remarque que la fonction rank
produit un rang
numérique pour chaque valeur ORDER BY
distincte dans la
partition de la ligne courante, en utilisant l'ordre défini par la clause
ORDER BY
. rank
n'a pas besoin de
paramètre explicite, puisque son comportement est entièrement déterminé par
la clause OVER
.
Les lignes prises en compte par une fonction de fenêtrage sont celles de la
table virtuelle produite par la clause FROM
de la
requête filtrée par ses clauses WHERE
, GROUP
BY
et HAVING
, s'il y en a. Par exemple, une
ligne rejetée parce qu'elle ne satisfait pas à la condition
WHERE
n'est vue par aucune fonction de fenêtrage. Une
requête peut contenir plusieurs de ces fonctions de fenêtrage qui découpent
les données de façons différentes, par le biais de clauses
OVER
différentes, mais elles travaillent toutes sur le
même jeu d'enregistrements, défini par cette table virtuelle.
ORDER BY
peut être omis lorsque l'ordre des
enregistrements est sans importance. Il est aussi possible d'omettre
PARTITION BY
, auquel cas il n'y a qu'une seule
partition, contenant tous les enregistrements.
Il y a un autre concept important associé aux fonctions de fenêtrage :
pour chaque enregistrement, il existe un jeu d'enregistrements dans sa
partition appelé son window frame (cadre de fenêtre).
Certaines fonctions de fenêtrage travaillent uniquement
sur les enregistrements du window frame,
plutôt que sur l'ensemble de la partition. Par défaut, si on a précisé une
clause ORDER BY
, la window
frame contient tous les enregistrements du début de la
partition jusqu'à l'enregistrement courant, ainsi que tous les
enregistrements suivants qui sont égaux à l'enregistrement courant au sens
de la clause ORDER BY
. Quand ORDER BY
est omis, la window frame par défaut
contient tous les enregistrements de la partition.
[4]
Voici un exemple utilisant sum
:
SELECT salaire, sum(salaire) OVER () FROM salaireemp;
salaire| sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows)
Dans l'exemple ci-dessus, puisqu'il n'y a pas d'ORDER BY
dans la clause OVER
, la window
frame est égale à la partition ; en d'autres termes,
chaque somme est calculée sur toute la table, ce qui fait qu'on a le même
résultat pour chaque ligne du résultat. Mais si on ajoute une clause
ORDER BY
, on a un résultat très différent :
SELECT salaire, sum(salaire) OVER (ORDER BY salaire) FROM salaireemp;
salaire| sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows)
Ici, sum est calculé à partir du premier salaire (c'est-à-dire le plus bas) jusqu'au salaire courant, en incluant tous les doublons du salaire courant (remarquez les valeurs pour les salaires identiques).
Les fonctions window ne sont autorisées que dans la liste
SELECT
et la clause ORDER BY
de la
requête. Elles sont interdites ailleurs, comme dans les clauses
GROUP BY
,HAVING
et
WHERE
. La raison en est qu'elles sont exécutées après le
traitement de ces clauses. Par ailleurs, les fonctions de fenêtrage
s'exécutent après les fonctions d'agrégat classiques. Cela signifie qu'il
est permis d'inclure une fonction d'agrégat dans les arguments d'une
fonction de fenêtrage, mais pas l'inverse.
S'il y a besoin de filtrer ou de grouper les enregistrements après le calcul des fonctions de fenêtrage, une sous-requête peut être utilisée. Par exemple :
SELECT nomdep, noemp, salaire, date_embauche FROM (SELECT nomdep, noemp, salaire, date_embauche, rank() OVER (PARTITION BY nomdep ORDER BY salaire DESC, noemp) AS pos FROM salaireemp ) AS ss WHERE pos < 3;
La requête ci-dessus n'affiche que les enregistrements de la requête interne ayant un rang inférieur à 3.
Quand une requête met en jeu plusieurs fonctions de fenêtrage, il est
possible d'écrire chacune avec une clause OVER
différente, mais cela entraîne des duplications de code et augmente les
risques d'erreurs si on souhaite le même comportement pour plusieurs
fonctions de fenêtrage. À la place, chaque comportement de fenêtrage peut
être associé à un nom dans une clause WINDOW
et ensuite
être référencé dans OVER
. Par exemple :
SELECT sum(salaire) OVER w, avg(salaire) OVER w FROM salaireemp WINDOW w AS (PARTITION BY nomdep ORDER BY salaire DESC);
Plus de détails sur les fonctions de fenêtrage sont disponibles dans la Section 4.2.8, la Section 9.21, la Section 7.2.5 et la page de référence SELECT.
[4] Il existe des options pour définir la window frame autrement, mais ce tutoriel ne les présente pas. Voir la Section 4.2.8 pour les détails.